# COPY ## Name COPY?--?在表和文件之間拷貝數據 ## Synopsis ``` COPY _table_name_ [ ( _column_name_ [, ...] ) ] FROM { '_filename_' | PROGRAM '_command_' | STDIN } [ [ WITH ] ( _option_ [, ...] ) ] COPY { _table_name_ [ ( _column_name_ [, ...] ) ] | ( _query_ ) } TO { '_filename_' | PROGRAM '_command_' | STDOUT } [ [ WITH ] ( _option_ [, ...] ) ] where `_option_` can be one of: FORMAT _format_name_ OIDS [ _boolean_ ] 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_ [, ...] ) ENCODING '_encoding_name_' ``` ## 描述 `COPY`在PostgreSQL表和文件之間交換數據。 `COPY TO`把一個表的所有內容都拷貝到一個文件，而`COPY FROM`從一個文件里拷貝數據到一個表里(把數據附加到表中已經存在的內容里)。 `COPY TO`還能拷貝`SELECT`查詢的結果。 如果聲明了一個字段列表，`COPY`將只在文件和表之間拷貝已聲明字段的數據。 如果表中有任何不在字段列表里的字段，那么`COPY FROM`將為那些字段插入缺省值。 帶文件名的`COPY`指示PostgreSQL服務器直接從文件中讀寫數據。 如果聲明了文件名，那么服務器必須可以訪問該文件，而且文件名必須從服務器的角度聲明。 如果使用了`PROGRAM`選項，則服務器會從指定的這個程序進行輸入或是寫入該程序作為輸出。 如果使用了`STDIN` 或`STDOUT`選項，那么數據將通過客戶端和服務器之間的連接來傳輸。 ## 參數 `_table_name_` 現存表的名字(可以有模式修飾)。。 `_column_name_` 可選的待拷貝字段列表。如果沒有聲明字段列表，那么將使用所有字段。 `_query_` 一個必須用圓括弧包圍的[SELECT](#calibre_link-104)或[VALUES](#calibre_link-106)命令，其結果將被拷貝。 `_filename_` 輸入或輸出文件的路徑名。輸入文件名可以是絕對或是相對的路徑，但輸出文件名必須是絕對路徑。 Windows用戶可能需要使用`E''`字符串和雙反斜線作為路徑名稱。 `PROGRAM` 需執行的程序名。在`COPY FROM`命令中，輸入是從程序的標準輸出中讀取，而在`COPY TO`中，命令的輸出會作為程序的標準輸入。 注意，程序一般是在命令行界面下執行，當用戶需要傳遞一些變量給程序時，如果這些變量的來源不是可靠的，用戶必須小心過濾處理那些對命令行界面來說是有特殊意義的字符。 基于安全的原因，最好是使用固定的命令字符串，或者至少是應避免直接使用用戶輸入（應先過濾特殊字符）。 `STDIN` 聲明輸入是來自客戶端應用。 `STDOUT` 聲明輸入將寫入客戶端應用。 `_boolean_` 聲明用戶所選的選項是否應該被開啟或者關閉。您可以寫`TRUE`、 `ON`或`1`來啟用這個選項，并且用`FALSE`、`OFF`或`0`來關閉它。 `_boolean_`值也可以被省略，此時系統使用缺省值`TRUE`。 `FORMAT` 選擇被讀或者寫的數據格式：`text`、`csv`（逗號分隔值），或者`binary`。 默認是`text`。 `OIDS` 聲明為每行記錄都拷貝內部對象標識(OID)。 （如果為一個`_query_`拷貝或者沒有`OIDS`的表聲明了OIDS選項，則拋出一個錯誤。） `FREEZE` 請求拷貝那些已凍結的數據，就類似使用`VACUUM FREEZE`的效果。 這主要于用于初始化時加載數據時的性能考慮。 僅在表記錄初始創建或是在當前子事務中被清理的記錄會補凍結，沒有游標會打開，事務中也沒有數據快照。 注意此時其他的事務會立刻看見剛加載的數據。 這不符合MVCC正常的可見性規則，用戶應注意這可能帶來的潛在問題。 `DELIMITER` 指定分隔每一行記錄中的列的字符。默認是文本格式的制表符，`CSV`格式的逗號。 必須有一個獨立的一字節的字符。 在使用`binary`格式時這個選項是不允許的。 `NULL` 聲明代表一個空值的字符串。默認是文本格式的`\N`，`CSV`格式的一個未被引用的空字符串。 即使是文本格式您可能也更偏向于空串，例如您不想從空字符串中區分空值。 在使用`binary`格式時這個選項是不允許的。 > **Note:** 在使用`COPY FROM`的時候，任何匹配這個字符串的字符串將被存儲為NULL值，所以你應該確保你用的字符串和`COPY TO`相同。 `HEADER` 聲明文件包含一個帶有文件中每列名稱的標題行。 在輸出時，第一行包含表中的列名，在輸入時，第一行是被忽略的。該選項僅僅在使用`CSV`格式時是允許的。 `QUOTE` 指定引用數據的引用字符。默認的是雙引號。這一定是一個1字節的字符。該選項僅僅在使用`CSV`格式時允許。 `ESCAPE` 聲明應該出現在一個匹配`QUOTE`值的數據字符之前的字符。 默認與`QUOTE`值相同（所以若它出現在數據中，則引用字符是翻一倍）。 這一定是一個1字節的字符。該選項只有在使用`CSV`格式時允許。 `FORCE_QUOTE` 強制引用在每個指定列的所有非`NULL`值。`NULL`從不被引用。 如果聲明了`*`，非`NULL`值將在所有列中被引用。 這個選項僅僅在`COPY TO`中并且僅僅在使用`CSV`格式時允許。 `FORCE_NOT_NULL` 默認情況下空字符串是空的，這意味著空值將會被讀作長度為零的字符串而不是空值，即使當他們不被引用。 這個選項僅僅在`COPY FROM`中并且僅僅在使用`CSV`格式時允許。 `ENCODING` 聲明文件的編碼集是`_encoding_name_`。 如果這個選項省略，則系統使用當前的用戶編碼集。 閱讀下面的注意事項以了解更多內容。 ## Outputs 當`COPY`命令執行成功后，會在屏幕上顯示 ``` COPY _count_ ``` 式樣內容， 這里`_count_`是已拷貝成功的記錄數。 ## 注意 `COPY`只能用于表，不能用于視圖。當然也可以用于`COPY (SELECT * FROM` `_viewname_`) TO ... `COPY`僅僅處理已指定的特定表；它將不復制數據到子表或從子表中復制數據。 因此比如`COPY` `_table_` TO顯示與`SELECT * FROM ONLY` `_table_`相同的數據。 但是`COPY (SELECT * FROM` `_table_`) TO ...可以用于轉儲在繼承層次結構的所有數據。 你對任何要`COPY TO`出來的數據必須有查詢的權限，對任何要`COPY FROM`入數據的表必須有插入權限。 對列在命令中的字段擁有列權限也是必須的。 `COPY`命令里面的文件必須是由服務器直接讀或寫的文件，而不是由客戶端應用讀寫。 因此，它們必須位于數據庫服務器上或者可以被數據庫服務器所訪問，而不是客戶端程序。 它們必須被運行PostgreSQL服務器的用戶可讀或寫，而不是客戶端程序。 由`PROGRAM`選項指定的命令必須是由服務器來執行的，而不是客戶端程序，必須是由PostgreSQL所屬的用戶。 `COPY`在指定一個程序或是命令時只允許數據庫超級用戶來執行，因為它允許讀寫任意服務器有權限訪問的文件。 不要混淆`COPY`和 psql應用程序中的`[\copy](#calibre_link-1638)`指令。 `\copy`調用`COPY FROM STDIN`或`COPY TO STDOUT`，然后把數據抓取/存儲到一個psql客戶端可以訪問的文件中。 因此，使用`\copy`的時候，文件訪問權限是由客戶端應用程序而不是服務器端決定的。 建議在`COPY`里的文件名字總是使用絕對路徑。 在`COPY TO`的時候是由服務器強制進行的，但是對于`COPY FROM`，你的確可從一個相對路徑的文件里讀取。 該路徑將解釋為相對于服務器的工作目錄(通常是數據目錄)，而不是客戶端的工作目錄。 執行一個`PROGRAM`選項指定的命令有可能還會受到操作系統的存取權限控制，如在SELinux下。 `COPY FROM`在執行時會觸發目標表上所有觸發器和檢查約束。不過，不會執行規則。 `COPY`輸入和輸出會被`DateStyle`參數影響。 為了和其它PostgreSQL不同服務器間進行數據轉移(它們可能是非缺省`DateStyle`設置)，應該在使用`COPY TO`前把`DateStyle`參數值設置為`ISO`。 另外也建議在導出數據時，不要將`IntervalStyle`參數設置為`sql_standard`。 因為負的區間值可能會被對`IntervalStyle`有不同設置的服務器誤解。 輸入數據通過`ENCODING`參數或是當前客戶端編碼來解譯，輸出數據也是通過`ENCODING`參數或是為當前客戶端的編碼來編碼， 即使數據不經過客戶端的，仍會通過服務器直接將數據從文件中讀出或者寫入到文件中去。 `COPY`在第一個錯誤處停下來。 這些在`COPY TO`中不應該導致問題，但在`COPY FROM`時目標表會已經接收到早先的行， 這些行將不可見或不可訪問，但是仍然會占據磁盤空間。如果你碰巧拷貝大量數據文件的話，這些東西積累起來可能會占據相當大的磁盤空間。 你可以調用`VACUUM`來恢復那些磁盤空間。 ## 文件格式 ### 文本格式 當使用`text`格式時，讀寫的文件是一個文本文件，每行代表表中一個行。 行中的列(字段)用分隔符分開。字段值本身是由與每個字段類型相關的輸出函數生成的字符串，或者是輸入函數可接受的字符串。 數據中使用特定的NULL字符串表示那些值為NULL的字段。 如果輸入文件的任意行包含比預期多或者少的字段，那么`COPY FROM`將拋出一個錯誤。 如果聲明了`OIDS`選項，那么OID將作為第一個字段讀寫，放在所有用戶字段前面。 數據的結束可以用一個只包含反斜杠和句點(`\.`)的行表示。 如果從文件中讀取數據，那么數據結束的標記是不必要的，因為文件結束符可以起到相同的作用； 但是在3.0之前的客戶端協議里， 如果在客戶端應用之間拷貝數據，那么必須要有結束標記。 反斜杠字符(`\`)可以用于`COPY`數據，來引用那些可能會被當作行或列分隔符的數據字符。 特別是以下字符，若以一列值的一部分出現則必須在前面加上反斜杠：反斜杠、換行符、回車以及當前的分隔符字符。 聲明的空字符串被`COPY TO`不加任何反斜杠發送； 與之相對，`COPY FROM`在刪除反斜杠之前拿它的輸入與空字符串比較。 因此，像`\N`這樣的空字符串不會和實際數據值`\N`之間混淆(因為后者會表現成`\\N`)。 `COPY FROM`能夠識別下列特殊反斜杠字符： | 字符形式 | 字符含義 | | --- | --- | | `\b` | 反斜杠 (ASCII 8) | | `\f` | 進紙 (ASCII 12) | | `\n` | 換行符 (ASCII 10) | | `\r` | 回車符 (ASCII 13) | | `\t` | 水平制表符 (ASCII 9) | | `\v` | 垂直制表符 (ASCII 11) | | `\``_digits_` | 反斜杠后面跟著一到三個八進制數，表示ASCII值為該數的字符 | | `\x``_digits_` | 反斜杠`\x`后面跟著一個或兩個十六進制位聲明指定數值編碼的字符 | 目前，`COPY TO`絕不會發出一個八進制或者十六進制反斜杠序列，但是它的確使用了上面列出的其它字符用于控制字符。 任何其他未在上表中提及的斜字符將會用來表示其本身。然而，也要注意不必要的情況添加反斜杠。 因為這可能意外地生成一個匹配數據結束標記(`\.`)或者空字符串 (默認為`\N`）的字符串。 這些字符串將在任何其他反斜杠處理做完之前確認。 強烈建議產生`COPY`數據的應用程序將數據換行符和回車分別轉換為 `\n`和`\r`序列。 目前，可以由反斜杠和回車代表一個數據回車，并且由反斜杠和換行符代表一個數據換行。 然而，這些表示法在將來的版本中可能無法接受。`COPY`文件在不同操作系統之間轉移時，它們也非常容易被誤解讀， （例如：從Unix 系統移到Windows系統，或者反過來）。 `COPY TO`將在每行的結尾用一個Unix風格的換行符("`\n`")。 運行在Windows上的服務器會輸出的回車換行符("`\r\n`")，但只是用于`COPY`到服務器 文件里； 為了在不同平臺之間一致，`COPY TO STDOUT`總是發送"`\n`"而不管服務器平臺是什么。 `COPY FROM`可以處理那些以回車符、換行符、回車/換行符作為行結束的數據。 為了減少在數據中出現的未逃逸的新行或者回車導致的錯誤，如果輸入的行結尾不像上面這些符號，`COPY FROM`會發出警告。 ### CSV 格式 這個格式用于輸入和輸出逗號分隔數值(`CSV`)文件格式，許多其它程序都用這個文件格式，比如電子表格。 這個模式下生成并識別逗號分隔的CSV逃逸機制，而不是使用PostgreSQL標準文本的逃逸模式。 每條記錄的值都是用`DELIMITER`字符分隔的。 如果數值本身包含分隔字符、`QUOTE`字符、`NULL`字符串、回車符、換行符，那么整個數值用`QUOTE`字符前綴和后綴(包圍)， 并且數值里任何`QUOTE`字符或`ESCAPE`字符都前導逃逸字符。 你也可以使用`FORCE_QUOTE`在輸出非`NULL`的指定字段值時強制引號包圍。 `CSV`格式沒有標準的辦法區分一個`NULL`值和一個空字符串。 PostgreSQL的`COPY`通過引號包圍來處理這些。 一個當作`NULL` 輸出的`NULL` 參數值是沒有引號包圍的，而匹配非`NULL`字符串的參數值是用引號包圍的。 比如，使用缺省設置時，一個`NULL`是寫做一個無引號包圍的空字符串，而一個空字符串數值寫做 雙引號包圍(`""`)。 讀取數值也遵循類似的規則。 你可以使用`FORCE_NOT_NULL`來避免為特定字段進行`NULL`比較。 因為對于`CSV`格式而言，反斜杠不是特殊字符，數據的結束標志(`\.`) 可以作為數據值出現。 為了避免任何可能的歧意，一個單獨的`\.`數據值在輸出中將被自動使用引號包圍； 在輸入中，如果被引號界定，那么將不會當作數據結束標志。如果你要加載其它程序創建的、有未用引號界定字段的文件，并且其中含有`\.`值，你就必須用引號進行界定。 > **Note:** 在`CSV`模式下，所有字符都是有效的。 一個被空白包圍的引號界定數值，或者任何非`DELIMITER`字符，都會被包含這些字符。 如果你給`CSV`行填充空白的系統里導入數據到定長字段，那么可能會導致錯誤。 如果出現這種情況，你可能需要先 處理一下`CSV`文件，刪除結尾空白，然后再向PostgreSQL里導入數據。 > **Note:** CSV格式可以識別和生成引號包圍的回車和換行的CSV文件。因此這些文件并不像文本模式的文件那樣嚴格地每條記錄一行。 > **Note:** 許多程序生成奇怪的并且有時候不正確的CSV文件，所以這個文件格式更像一種慣用格式，而不是一種標準。 因此你可能碰到一些不能使用這個機制輸入的文件，而`COPY`也可能生成一些其它程序不能處理的文件。 ### 二進制格式 `binary`形式的選項會使得所有的數據被存儲/讀作二進制格式而不是文本。 這比文本和`CSV`格式的要快一些，但是一個二進制格式文件在機器架構和PostgreSQL版本之間的可移植性比較差。 另外，二進制格式是對數據類型有一定要求的；例如，不能從`smallint`列中輸出二進制數據 并將二進制數據讀入`integer`列，盡管在文本格式下那會運行良好。 `binary`文件格式包含一個文件頭，0或更多包含行數據的元組，以及一個文件尾。頭和數據按照網絡字節順序。 > **Note:** 7.4版本之前的PostgreSQL版本使用的是不同的二進制文件格式。 #### 文件頭 文件頭由15個字節的固定域組成，后面跟著一個變長的頭擴展區。固定域是： 簽名 11字節的序列`PGCOPY\n\377\r\n\0`—，請注意字節零是簽名必須的一部分。 （使用這個簽名是為了能夠很容易看出文件是否已經被一個非8位安全的轉換器給破壞了。 這個簽名會被行尾轉換過濾器、刪除字節零、刪除高位、奇偶變化而改變。） 標志域 32位整數掩碼表示該文件格式的重要方面。 位是從 0(LSB)到 31(MSB) 編碼的，請注意這個域是以網絡字節順序存儲的(高位在前)，后繼的整數都是如此。 位16-31是 保留用做關鍵文件格式信息的； 如果閱讀器發現一個不認識的位出現在這個范圍內，那么它應該退出。 位0-15都保留為標志向后兼容的格式使用；閱讀器可以忽略這個范圍內的不認識的位。 目前只定義了 一個標志位，而其它的必須是零： Bit 16 如果為1，那么在數據中包括了OIDS；如果為0，則沒有。 頭擴展范圍長度 32位整數，以字節計的頭剩余長度，不包括自身。 目前，它是零，后面緊跟第一條記錄行。 對該格式的更多修改都將允許額外的數據出現在頭中。 閱讀器應該忽略任何它不知道該如何處理的頭擴展數據。 頭擴展數據用來保存自定義數據序列塊。 這個標志域無意告訴閱讀器擴展區的內容是什么。 頭擴展的具體設計內容留給以后的版本使用。 這樣設計就允許向后兼容的頭擴展(增加頭擴展塊或設置低位序標志位)以及非向后兼容的修改(設置高位標志位以標識這樣的修改，并且根據需要向擴展區域增加支持數據)。 #### 行記錄 每條行都以一個16位整數計數開頭，該計數是行中字段的數目(目前，在一個表里的每行都有相同的計數，但可能不會永遠這樣)。 然后后面不斷出現行中的各個字段，字段先是一個32位的長度字，后面跟著很多的字段數據。長度字并不包括自己，并且可以為零。 一個特例是：-1表示一個NULL字段值。在NULL情況下，后面不會跟著數值字節。 在數據域之間沒有對齊填充或者任何其它額外的數據。 目前，一個二進制格式文件里的所有數據值都假設是二進制格式的(格式代碼為一)。 預計將來的擴展可能增加一個頭域，允許為每個字段聲明格式代碼。 為了判斷實際行數據的正確二進制格式，你應該閱讀PostgreSQL源代碼，特別是該字段數據類型的`*send`和`*recv`函數 (這些函數可以在源代碼的`src/backend/utils/adt/`目錄找到)。 如果在文件中包括了OIDs，那么該OID域立即跟在字段計數字后面。 它是一個普通的字段， 只不過它沒有包括在字段計數。 但它包括長度字，這樣就允許方便的處理4字節和8字節的OIDs，并且如果某個家伙允許OIDs是可選的話，那么還可以把OIDs顯示成空。 #### 文件尾 文件尾包括保存著-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`。 所有的行在第三個域都是一個null值。 ``` 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 ``` ## 兼容性 SQL標準里沒有`COPY`語句。 PostgreSQL9.0以前使用下面的語法，現在仍然支持： ``` COPY _table_name_ [ ( _column_name_ [, ...] ) ] FROM { '_filename_' | STDIN } [ [ WITH ] [ BINARY ] [ OIDS ] [ DELIMITER [ AS ] '_delimiter_' ] [ NULL [ AS ] '_null string_' ] [ CSV [ HEADER ] [ QUOTE [ AS ] '_quote_' ] [ ESCAPE [ AS ] '_escape_' ] [ FORCE NOT NULL _column_name_ [, ...] ] ] ] COPY { _table_name_ [ ( _column_name_ [, ...] ) ] | ( _query_ ) } TO { '_filename_' | STDOUT } [ [ WITH ] [ BINARY ] [ OIDS ] [ DELIMITER [ AS ] '_delimiter_' ] [ NULL [ AS ] '_null string_' ] [ CSV [ HEADER ] [ QUOTE [ AS ] '_quote_' ] [ ESCAPE [ AS ] '_escape_' ] [ FORCE QUOTE { _column_name_ [, ...] | * } ] ] ] ``` 請注意：在這個語法中，`BINARY`和`CSV`是作為獨立的關鍵字，而不是作為`FORMAT`選項的一個參數。 PostgreSQL7.3以前使用下面的語法，現在仍然支持： ``` COPY [ BINARY ] _table_name_ [ WITH OIDS ] FROM { '_filename_' | STDIN } [ [USING] DELIMITERS '_delimiter_' ] [ WITH NULL AS '_null string_' ] COPY [ BINARY ] _table_name_ [ WITH OIDS ] TO { '_filename_' | STDOUT } [ [USING] DELIMITERS '_delimiter_' ] [ WITH NULL AS '_null string_' ] ```