PolarDB：COPY

簡介

COPY在 PolarDB表和標準檔案系統檔案之間移動資料。COPY TO把一個表的內容複寫 到一個檔案，而COPY FROM 則從一個檔案複製資料到一個表（把資料追加到表中原有資料）。COPY TO也能複製一個 SELECT查詢的結果。

如果指定了一個列列表，COPY TO將只把指定列的資料複製到檔案。對於COPY FROM，檔案中的每個欄位將按順序插入到指定列中。COPY FROM命令的列列表中沒有指定的表列則會採納其預設值。

帶一個檔案名稱的COPY指示 PolarDB伺服器直接從一個檔案讀取或者寫入到一個檔案。該檔案必須是 PolarDB使用者（運行伺服器的使用者識別碼） 可訪問的並且應該以伺服器的視角來指定其名稱。當指定了 PROGRAM時，伺服器執行給定的命令並且從該程式的標準輸出讀取或者寫入到該程式的標準輸入。該程式必須以伺服器的視角指定，並且必須是PolarDB使用者可執行檔。在指定 STDIN或者STDOUT時，資料會通過用戶端和伺服器之間的串連傳輸。

文法

    COPY table_name [ ( column_name [, ...] ) ]
        FROM { 'filename' | PROGRAM 'command' | STDIN }
        [ [ WITH ] ( option [, ...] ) ]
        [ WHERE condition ]

    COPY { table_name [ ( column_name [, ...] ) ] | ( query ) }
        TO { 'filename' | PROGRAM 'command' | STDOUT }
        [ [ WITH ] ( option [, ...] ) ]

    其中 option 可以是下列之一：

        FORMAT format_name
        FREEZE [ boolean ]
        DELIMITER 'delimiter_character'
        NULL 'null_string'
        HEADER [ boolean ]
        QUOTE 'quote_character'
        ESCAPE 'escape_character'
        FORCE_QUOTE { ( column_name [, ...] ) | * }
        FORCE_NOT_NULL ( column_name [, ...] )
        FORCE_NULL ( column_name [, ...] )
        ENCODING 'encoding_name'

參數

table_name一個現有表的名稱（可以是模式限定的）。

column_name可選的要被複製的列列表。如果沒有指定列列表，則該表的所有列除了產生的列都會被複製。

query其結果要被複製的 SELECT、 VALUES、 INSERT、UPDATE 或者 DELETE 命令。注意查詢周圍的圓括弧是必要的。

對於INSERT、UPDATE以及 DELETE查詢，必須提供一個 RETURNING 子句並且目標關係不能具有會擴充成多條語句的條件規則、 ALSO規則或者INSTEAD規則。

filename輸入或者輸出檔案的路徑名。一個輸入檔案的名稱可以是一個絕對或相對路徑， 但一個輸出檔案的名稱必須是絕對路徑。Windows 使用者可能需要使用一個 E''字串並且雙寫路徑名稱中使用的任何反斜線。

PROGRAM一個要執行的命令。在COPY FROM中，輸入將從該命令的標準輸出讀取，而在COPY TO中，輸出會寫入到該命令的標準輸入。

STDIN指定輸入來自用戶端應用。

STDOUT指定輸出會去到用戶端應用。

boolean指定選中的選項是應該被關閉還是開啟。可以寫TRUE、 ON或1來啟用選項，寫 FALSE、OFF或0禁用它。 boolean值也可以被省略， 那樣會假定為TRUE。

FORMAT選擇要讀取或者寫入的資料格式： text、 csv（逗號分隔值）或者binary。 預設是text。

FREEZE提取複寫已經完成了行凍結的資料，就好像在運行 VACUUM FREEZE命令之後複製。這是為了初始資料載入的效能而設計的。只有被載入表已經在當前子事務中被建立或截斷、該事務中沒有遊標開啟並且該事務沒有持有更舊的快照時， 行才會被凍結。目前無法在分區表上執行COPY FREEZE。

DELIMITER指定分隔檔案每行中各列的字元。文字格式設定中預設是一個定位字元， 而CSV格式中預設是一個逗號。這必須是一個單一的單位元組字元。使用binary格式時不允許這個選項。

NULL指定表示一個空值的字串。文字格式設定中預設是 \N（反斜線-N），CSV格式中預設是一個未加引用的空串。在你不想區分空值和空串的情況下，即使在文字格式設定中你也可能更喜歡空串。使用binary格式時不允許這個選項。

在使用COPY FROM時，任何匹配這個串的資料項目將被儲存為空白值，因此你應該確定你使用的是和 COPY TO時相同的串。

HEADER指定檔案包含標題列，其中有每一列的名稱。在輸出時，第一行包含來自表的列名。在輸入時，第一行會被忽略。只有使用 CSV格式時才允許這個選項。

QUOTE指定一個資料值被引用時使用的引用字元。預設是雙引號。 這必須是一個單一的單位元組字元。只有使用 CSV格式時才允許這個選項。

ESCAPE指定應該出現在一個匹配QUOTE值的資料字元之前的字元。預設和QUOTE值一樣（這樣如果引用字元出現在資料中，它會被雙寫）。這必須是一個單一的單位元組字元。 只有使用CSV格式時才允許這個選項。

FORCE_QUOTE強制必須對每個指定列中的所有非NULL值使用引用。 NULL輸出不會被引用。如果指定了， 所有列的非NULL值都將被引用。只有在 COPY TO中使用CSV格式時才允許這個選項。

FORCE_NOT_NULL不要把指定列的值與空值串匹配。在空值串就是空串的預設情況下， 這意味著空串將被讀作長度為零的字串而不是空值（即使它們沒有被引用）。只有在COPY FROM中使用 CSV格式時才允許這個選項。

FORCE_NULL將指定列的值與空值串匹配（即使它已經被加上引號），並且在找到匹配時將該值設定為NULL。在空值串就是空串的預設情況下，這會把一個被引用的空串轉換為 NULL。 只有在COPY FROM中使用 CSV格式時才允許這個選項。

ENCODING指定檔案被以 encoding_name編碼。如果省略這個選項，將使用當前的用戶端編碼。詳見下文的註解。

WHERE子句是可選的，其一般形式是：

WHERE condition

其中 condition是計算結果為boolean類型的任意運算式。任何不滿足此條件的行都不會插入到表中。在用實際的行值替換任何變數引用時，如果該行返回 true，則該行滿足條件。

目前，在WHERE運算式中不允許使用子查詢，並且值的計算不會看到COPY本身所做的任何更改（當運算式包含對VOLATILE函數的調用時，這一點很重要）。

輸出

在成功完成時，一個COPY命令會返回一個如下行為的命令標籤。

COPY count

count是被複製的行數。

說明

COPY TO只能被用於純粹的表，不能用於視圖。 不過你可以寫COPY (SELECT * FROM viewname ) TO ...來拷貝一個視圖的當前內容。

COPY FROM可以被用於普通表、外部表格、分區表或者具有INSTEAD OF INSERT觸發器的視圖。

COPY只處理提到的表，它不會從子表複製資料或者複製資料到子表中。例如 COPY table TO 會顯示與SELECT * FROM ONLY table相同的資料。而COPY (SELECT * FROM table ) TO ... 可以被用來轉儲一個繼承層次中的所有資料。

你必須擁有被COPY TO讀取的表上的選擇特權， 以及被COPY FROM插入的表上的插入特權。 擁有在命令中列出的列上的特權就足夠了。

如果對錶啟用了行級安全性，相關的SELECT策略將應用於COPY table TO語句。當前，有行級安全性的表不支援COPY FROM。不過可以使用等效的INSERT語句。

COPY命令中提到的檔案會被伺服器（而不是用戶端應用）直接讀取或寫入。因此它們必須位於資料庫伺服器（不是用戶端）的機器上或者是資料庫伺服器可以訪問的。它們必須是 PolarDB使用者（運行伺服器的使用者識別碼）可訪問的並且是可讀或者可寫的。類似地，用PROGRAM 指定的命令也會由伺服器（不是用戶端應用）直接執行，它也必須是 PolarDB使用者可以執行的。 只允許資料庫超級使用者或者授予了預設角色pg_read_server_files、pg_write_server_files及pg_execute_server_program之一的使用者COPY一個檔案或者命令， 因為它允許讀取或者寫入伺服器有特權訪問的任何檔案或者運行伺服器有特權訪問的程式。

不要把COPY和 psql指令 \copy 弄混。\copy會調用 COPY FROM STDIN或者COPY TO STDOUT，然後讀取/儲存一個 psql用戶端可訪問的檔案中的資料。 因此，在使用\copy時，檔案的可訪問性和訪問權利取決於用戶端而不是伺服器。

我們推薦在COPY中使用的檔案名稱總是指定為一個絕對路徑。在COPY TO的情況下伺服器會強制這一點，但是對於 COPY FROM你可以選擇從一個用相對路徑指定的檔案中讀取。該路徑將根據伺服器處理序（而不是用戶端） 的工作目錄（通常是集簇的資料目錄）解釋。

用PROGRAM執行一個命令可能會受到作業系統的存取控制機制（如 SELinux）的限制。

COPY FROM將調用目標表上的任何觸發器和檢查約束。但是它不會調用規則。

對於識別欄位，COPY FROM命令將總是寫上輸入資料中提供的列值，這和INSERT的選項OVERRIDING SYSTEM VALUE的行為一樣。

COPY輸入和輸出受到 DateStyle的影響。為了確保到其他可能使用非預設DateStyle設定的 PolarDB安裝的可移植性，在使用 COPY TO前應該把 DateStyle設定為ISO。避免轉儲把 IntervalStyle設定為 sql_standard的資料也是一個好主意，因為負的區間值可能會被具有不同IntervalStyle設定的伺服器解釋錯誤。

即使資料會被伺服器直接從一個檔案讀取或者寫入一個檔案而不通過用戶端，輸入資料也會被根據ENCODING選項或者當前用戶端編碼解釋，並且輸出資料會被根據ENCODING或者當前用戶端編碼進行編碼。

COPY會在第一個錯誤處停止操作。這在 COPY TO的情況下不會導致問題，但是在COPY FROM中目標表將已經收到了一些行。這些行將不會變得可見或者可訪問，但是它們仍然佔據磁碟空間。 如果在一次大型的複製操作中出現錯誤，這可能浪費相當可觀的磁碟空間。 你可能希望調用VACUUM來恢複被浪費的空間。

FORCE_NULL和FORCE_NOT_NULL可以被同時用在同一列上。這會導致把已被引用的空值串轉換為空白值並且把未引用的空值串轉換為空白串。

檔案格式

在使用text格式時，讀取或寫入的是一個文字檔， 其中每一行就是表中的一行。一行中的列被定界字元分隔。列值本身是由輸出函數產生的或者是可被輸入函數接受的屬於每個屬性資料類型的字串。在為空白值的列的位置使用指定的空值串。如果輸入檔案的任何行包含比預期更多或者更少的列， COPY FROM將會拋出一個錯誤。

資料的結束可以表示為一個只包含反斜線-點號（\.）的單一行。從一個檔案讀取時，資料結束標記並不是必要的，因為檔案結束符就已經足夠用了。只有使用 3.0 用戶端協議之前的用戶端應用複製資料時才需要它。

反斜線字元（\）可以被用在 COPY資料中來引用被用作行或者列定界符的字元。特別地，如果下列字元作為一個列值的一部分出現，它們 必須被前置一個反斜線：反斜線本身、新行、斷行符號以及當前的定界符字元。

COPY TO會不加任何反斜線返回指定的空值串。 相反，COPY FROM會在移除反斜線之前把輸入與空值串相匹配。因此，一個空值串（例如\N）不會與實際的資料值\N（它會被表示為\\N）搞混。

COPY FROM識別下列特殊的反斜線序列：

當前，COPY TO不會發出一個十進位或十六進位位反斜線序列，但是它確實把上面列出的其他序列用於那些控制字元。

任何上述表格中沒有提到的其他反斜線字元將被當作表示其本身。不過，增加不必要的反斜線，因為那可能意外地產生一個匹配資料結束標記（ \.）或者空值串（預設是\N）的字串。這些字串將在完成任何其他反斜線處理之前被識別。

強烈建議產生COPY資料的應用把資料新行和斷行符號分別轉換為\n和\r序列。當前可以把一個資料斷行符號表示為一個反斜線和斷行符號，把一個資料新行表示為一個反斜線和新行。不過，未來的發行可能不會接受這些表示。如果在不同的機器之間（例如從 Unix 到 Windows） 傳輸COPY檔案，它們也很容易受到破壞。

COPY TO將用一個 Unix 風格的新行（ “\n”）終止每一行。運行在 Microsoft Windows 上的伺服器則會輸出斷行符號/新行（“\r\n”），不過只對 COPY到一個伺服器檔案這樣做。為了做到跨平台一致， COPY TO STDOUT總是發送“\n”而不管伺服器平台是什麼。COPY FROM能夠處理以新行、斷行符號或者斷行符號/新行結尾的行。為了減少由作為資料的未加反斜線的新行或者斷行符號帶來的風險，如果輸出中的行結束並不完全相似， COPY FROM將會抱怨。

CSV 格式

這種格式選項被用於匯入和匯出很多其他程式（例如試算表）使用的逗號分隔值（CSV）檔案格式。不同於 PolarDB標準文字格式設定使用的轉義規則，它產生並且識別一般的 CSV 轉義機制。

每個記錄中的值用DELIMITER字元分隔。如果值包含定界符字元、QUOTE字元、NULL字串、 一個斷行符號或者換行字元，那麼整個值會被加上QUOTE字元作為首碼或者尾碼，並且在該值內QUOTE字元或者 ESCAPE字元的任何一次出現之前放上逸出字元。在輸出指定列中非NULL值時，還可以使用 FORCE_QUOTE來強制加上引用。

CSV格式沒有標準方式來區分NULL值和Null 字元串。 PolarDB的COPY用引用來處理這種區分工作。NULL被按照NULL參數字串輸出並且不會被引用，而匹配NULL參數字串的非NULL 值會被加上引用。例如，使用預設設定時，NULL被寫作一個未被引用的Null 字元串，而一個Null 字元串資料值會被寫成帶雙引號（""）。 值的讀取遵循類似的規則。你可以用FORCE_NOT_NULL來防止對指定列的NULL輸入比較。你還可以使用 FORCE_NULL把帶引用的空值字串資料值轉換成NULL。

因為反斜線在CSV格式中不是一種特殊字元，資料結束標記 \.也可以作為一個資料值出現。為了避免任何解釋誤會，在一行上作為孤項出現的\.資料值輸出時會自動被引用，並且輸入時如果被引用，則不會被解釋為資料結束標記。如果正在載入一個由另一個應用建立的檔案並且其中具有一個未被引用的列且可能具有 \.值，你可能需要在輸入檔案中引用該值。

CSV格式中，所有字元都是有意義的。一個被空白或者其他非 DELIMITER字元圍繞的引用值將包括那些字元。在匯入來自用空白填充CSV行到固定長度的系統的資料時，這可能會導致錯誤。如果出現這種情況，在匯入資料到 PolarDB之前，你可能需要預先處理該 CSV檔案以移除拖尾的空白。

CSV 格式將識別並且產生帶有包含嵌入的斷行符號和換行的引用值的 CSV 檔案。因此檔案並不限於文字格式設定檔案的每個錶行一行的形式。

很多程式會產生奇怪的甚至偶爾是不合常理的 CSV 檔案，因此該檔案格式更像是一種習慣而不是標準。因此你可能會碰到一些無法使用這種機制匯入的檔案，並且COPY也可能產生其他程式無法處理的檔案。

二進位格式

binary格式選項導致所有資料被以二進位格式而不是文字格式設定儲存/讀取。它比文本和CSV格式要快一些，但是二進位格式檔案在不同的機器架構和 PolarDB版本之間的可移植性要差些。還有，二進位格式與資料格式非常相關。例如不能從一個smallint列中輸出位元據並且把它讀入到一個 integer列中，雖然這樣做在文字格式設定中是可行的。

binary檔案格式由一個檔案頭、零個或者更多個包含行資料的元組以及一個檔案尾構成。頭部和資料都以網路位元組序表示。

檔案頭

檔案頭由 15 位元組的固定域構成，後面跟著一個變長的頭部擴充區。 固定域有：

簽名 11-位元組的序列PGCOPY\n\377\r\n\0 — 零位元組是簽名的一個必要的部分（該簽名是為了能容易地發現檔案被無法正確處理8位字元編碼的傳輸所破壞。這個簽名將被行尾翻譯過濾器、刪除零位元組、刪除高位或者奇偶修改等改變）。

標誌域 32-位整數位元遮罩，用以表示該檔案格式的重要方面。位被編號為從0（LSB）到31（MSB）。 這個域以網路位元組序存放（最高有效位在前），所有該檔案格式中使用的整數域都是這樣。16-31 位被保留用來表示嚴重的檔案格式問題， 讀取者如果在這個範圍內發現預期之外的被設定位，它應該中止。0-15 位被保留用來表示向後相容的格式問題，讀取者應該簡單地略過這個範圍內任何預期之外的被設定位。當前只定義了一個標誌位，其他位必須為零：

位 16 如果為 1，表示資料中包含 OID；如果為 0，則不包含。PolarDB不再支援 Oid 系統列，但是格式仍然包含該指示符。

頭部擴充區長度 32-為整數，表示頭部剩餘部分的以位元組計的長度，不包括其本身。 當前，這個長度為零，並且其後就緊跟著第一個元組。未來對該格式的更改可能會允許在頭部中表示額外的資料。如果讀取者不知道要對頭部擴充區資料做什麼，可以安靜地跳過它。

頭部擴充地區被預期包含一個能自我解釋的塊的序列。 該標誌域並不想告訴讀取者擴充資料是什麼。詳細的頭部擴充內容的設計留給後來的發行去做。

這種設計允許向後相容的頭部增加（增加頭部擴充塊或者設定低位標誌位）以及非向後相容的更改（設定高位標誌位來表示這類更改並且在需要時向擴充地區中增加支援資料）。

元組

每一個元組由一個表示元組中域數量的 16 位整數計數開始（當前，一個表中的所有元組都應該具有相同的計數，但是這可能不會總是為真）。然後是元組中的每一個域，它是一個 32 位的長度值，後面則跟隨著這麼多個位元組的域資料（長度值不包括其本身，並且可以是零）。作為一種特殊情況，-1 表示一個 NULL 域值。在 NULL 情況下，後面不會跟隨值位元組。

在域之間沒有對齊填充或者任何其他額外的資料。

當前，一個二進位格式檔案中的所有資料值都被假設為二進位格式（格式代碼一）。 可以預見未來的擴充可能會增加一個允許獨立指定各列的格式代碼的頭部域。

如果檔案中包含 OID，OID 域會緊跟在域計數字之後。它是一個普通域， 不過它沒有被包含在域計數中。注意PolarDB目前的版本不支援 oid 系統列。

檔案尾

檔案位由一個包含 -1 的 16 位整數字組成。這很容易與一個元組的域計數字區分開。

如果一個域計數字不是 -1 也不是期望的列數，讀取者應該報告錯誤。 這提供了一種針對某種資料不同步的額外檢查。

樣本

使用豎線（|）作為域定界符把一個表複製到用戶端：

    COPY country TO STDOUT (DELIMITER '|');

從一個檔案中複製資料到country表中：

    COPY country FROM '/usr1/proj/bray/sql/country_data';

只把名稱以 'A' 開頭的國家複製到一個檔案中：

    COPY (SELECT * FROM country WHERE country_name LIKE 'A%') TO '/usr1/proj/bray/sql/a_list_countries.copy';

要複製到一個壓縮檔中，你可以用管道把輸出導到一個外部壓縮程式：

    COPY country TO PROGRAM 'gzip > /usr1/proj/bray/sql/country_data.gz';

這裡是一個適合於從STDIN複製到表中的資料：

    AF      AFGHANISTAN
    AL      ALBANIA
    DZ      ALGERIA
    ZM      ZAMBIA
    ZW      ZIMBABWE

下面是用二進位格式輸出的相同資料。該資料是用 Unix 工具 od -c過濾後顯示的。該表具有三列， 第一列類型是char(2)，第二列類型是text， 第三列類型是integer。所有行在第三列都是空值。

    0000000   P   G   C   O   P   Y  \n 377  \r  \n  \0  \0  \0  \0  \0  \0
    0000020  \0  \0  \0  \0 003  \0  \0  \0 002   A   F  \0  \0  \0 013   A
    0000040   F   G   H   A   N   I   S   T   A   N 377 377 377 377  \0 003
    0000060  \0  \0  \0 002   A   L  \0  \0  \0 007   A   L   B   A   N   I
    0000100   A 377 377 377 377  \0 003  \0  \0  \0 002   D   Z  \0  \0  \0
    0000120 007   A   L   G   E   R   I   A 377 377 377 377  \0 003  \0  \0
    0000140  \0 002   Z   M  \0  \0  \0 006   Z   A   M   B   I   A 377 377
    0000160 377 377  \0 003  \0  \0  \0 002   Z   W  \0  \0  \0  \b   Z   I
    0000200   M   B   A   B   W   E 377 377 377 377 377 377