在PostgreSQL數(shù)據(jù)庫中，數(shù)據(jù)導入和導出是日常工作中常見的操作。傳統(tǒng)的插入（INSERT）方法雖然可以實現(xiàn)數(shù)據(jù)的導入，但在處理大量數(shù)據(jù)時效率較低。而COPY命令則提供了一個快速、高效的方式來完成這一任務。COPY命令不僅可以用于將數(shù)據(jù)從文件導入到表中，還可以將表中的數(shù)據(jù)導出到文件中，支持多種文件格式，如TEXT、BINARY和CSV。通過使用COPY命令，可以大大提高數(shù)據(jù)導入和導出的效率，尤其是在處理大量數(shù)據(jù)時。

一、引言

數(shù)據(jù)的導入與導出在數(shù)據(jù)庫操作中的重要性：

數(shù)據(jù)庫作為存儲和管理數(shù)據(jù)的核心組件，其數(shù)據(jù)的導入和導出操作對于維護數(shù)據(jù)完整性、實現(xiàn)數(shù)據(jù)遷移、備份和恢復等任務至關重要。無論是對于大型企業(yè)還是個人用戶，數(shù)據(jù)的導入和導出都是日常數(shù)據(jù)庫操作中不可或缺的一部分。

傳統(tǒng)數(shù)據(jù)導入方法的局限性：

傳統(tǒng)的插入（INSERT）方法雖然可以實現(xiàn)數(shù)據(jù)的導入，但在處理大量數(shù)據(jù)時效率較低，且容易出錯。
導入大量數(shù)據(jù)時，需要編寫大量的SQL語句，這不僅耗時，而且容易引發(fā)錯誤。
對于數(shù)據(jù)的格式和一致性檢查，傳統(tǒng)方法也缺乏足夠的自動化和靈活性。

COPY命令的引入及其優(yōu)勢：

COPY命令為PostgreSQL數(shù)據(jù)庫提供了一種快速、高效的數(shù)據(jù)導入和導出方法。
它支持多種格式，如TEXT、BINARY和CSV，可以根據(jù)實際需求選擇合適的格式。
COPY命令可以直接從文件導入數(shù)據(jù)到表，或將表中的數(shù)據(jù)導出到文件，大大提高了數(shù)據(jù)導入和導出的效率。
與傳統(tǒng)方法相比，COPY命令具有更高的自動化程度，能夠減少人工錯誤，提高數(shù)據(jù)導入和導出的準確性。

二、COPY命令的基礎

COPY命令概述：

COPY命令是PostgreSQL中用于高效導入和導出數(shù)據(jù)的命令。它支持從文件導入數(shù)據(jù)到表，或將表中的數(shù)據(jù)導出到文件。COPY命令可以處理文本、二進制和CSV格式的數(shù)據(jù)。支持的格式：TEXT、BINARY和CSV：

• TEXT格式：適用于純文本數(shù)據(jù)，每個字段由分隔符分隔。
• BINARY格式：適用于二進制數(shù)據(jù)，例如圖像、音頻和視頻等。
• CSV格式：以逗號分隔值（Comma Separated Values）的形式存儲數(shù)據(jù)，易于閱讀和編輯。

數(shù)據(jù)的來源和去向：

• 數(shù)據(jù)來源可以是本地文件、遠程文件或通過網(wǎng)絡傳輸?shù)臄?shù)據(jù)流。
• 數(shù)據(jù)去向可以是本地文件、遠程文件或通過網(wǎng)絡發(fā)送的數(shù)據(jù)流。

通過COPY命令，我們可以將數(shù)據(jù)庫中的數(shù)據(jù)導出到文件，或者從文件導入數(shù)據(jù)到數(shù)據(jù)庫表中。

三、COPY命令的用法

將數(shù)據(jù)從表導出到文件

a. 語法示例：

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

b. 參數(shù)解釋：

• table_name：要導出數(shù)據(jù)的表名。
• column_name：可選，指定要導出的列名。
• filename：要導出的數(shù)據(jù)文件的路徑。
• PROGRAM 'command'：可選，指定執(zhí)行命令以發(fā)送數(shù)據(jù)。
• STDOUT：將數(shù)據(jù)發(fā)送到標準輸出流。
• option：可選，指定COPY命令的選項，如格式、分隔符、編碼等。

c. 注意事項：

• 確保文件存在且可寫。
• 根據(jù)需要選擇正確的格式和分隔符。
• 注意文件路徑的權限和所有權。

示例：

創(chuàng)建一個表并插入1000000條數(shù)據(jù)

postgres=# create table test_big(id int,name varchar(50));
CREATE TABLE
postgres=# insert into test_big select n,'test_name' from generate_series(1,1000000) as n;
INSERT 0 1000000
postgres=# select count(*) from test_big;count
---------1000000
(1 row)

默認不帶條件導出

postgres=# \copy test_big to '/home/postgres/test_big.sql'
COPY 1000000

查看導出的數(shù)據(jù)文件

[postgres@pcp ~]$ cat test_big.sql |more
1       test_name
2       test_name
3       test_name
4       test_name
5       test_name
6       test_name
7       test_name
8       test_name
...

導出文件帶字段名

如果需要把列名也打出來，可以加 with csv header;

postgres=# \copy test_big to '/home/postgres/test_big2.sql' with csv header;
COPY 1000000

查看數(shù)據(jù)文件內容，可以看到第一行是表的字段名

[postgres@pcp ~]$ cat test_big2.sql |more
id,name
1,test_name
2,test_name
3,test_name
4,test_name
5,test_name
6,test_name
7,test_name
8,test_name
...

導出文件自定義數(shù)據(jù)分割符

如果想把這個逗號改成其他分隔符，可以使用delimiter關鍵字：

postgres=# \copy test_big to '/home/postgres/test_big3.sql' with csv header delimiter '|';
COPY 1000000

查看數(shù)據(jù)文件內容:

[postgres@pcp ~]$ cat test_big3.sql |more
id|name
1|test_name
2|test_name
3|test_name
4|test_name
5|test_name
6|test_name
7|test_name
8|test_name
...

導出部分數(shù)據(jù)

如果只想導出表中的部分數(shù)據(jù)，可以這樣操作：

postgres=# \copy (select * from test_big limit 10) to '/home/postgres/test_big4.sql' with csv header delimiter '|';
COPY 10

查看文件內容：

[postgres@pcp ~]$ cat test_big4.sql
id|name
1|test_name
2|test_name
3|test_name
4|test_name
5|test_name
6|test_name
7|test_name
8|test_name
9|test_name
10|test_name

可以看到只有10條數(shù)據(jù)，導出的條件可以根據(jù)sql自己定義。
更多語法可以通過\h copy查看

postgres=# \h copy
Command:     COPY
Description: copy data between a file and a table
Syntax:
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 [, ...] ) ]where option can be one of:FORMAT format_nameFREEZE [ boolean ]DELIMITER 'delimiter_character'NULL 'null_string'HEADER [ boolean | MATCH ]QUOTE 'quote_character'ESCAPE 'escape_character'FORCE_QUOTE { ( column_name [, ...] ) | * }FORCE_NOT_NULL ( column_name [, ...] )FORCE_NULL ( column_name [, ...] )ENCODING 'encoding_name'URL: https://www.postgresql.org/docs/15/sql-copy.html

將數(shù)據(jù)從文件導入到表

a. 語法示例：

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

b. 參數(shù)解釋：

• table_name：要導入數(shù)據(jù)的表名。
• column_name：可選，指定要導入的列名。
• filename：要導入的數(shù)據(jù)文件的路徑。
• PROGRAM 'command'：可選，指定執(zhí)行命令以獲取數(shù)據(jù)。
• STDIN：從標準輸入流讀取數(shù)據(jù)。
• option：可選，指定COPY命令的選項，如格式、分隔符、編碼等。

c. 注意事項：

• 確保數(shù)據(jù)文件與數(shù)據(jù)庫中的表結構匹配。
• 根據(jù)需要選擇正確的格式和分隔符。
• 確保數(shù)據(jù)文件存在且可讀。

示例

從剛才導出的文件中導入數(shù)據(jù)。先創(chuàng)建一個空表

postgres=# create table test_copy(id int,name varchar(50));
CREATE TABLE

導入數(shù)據(jù)，按照剛才導出的順序，先導入第一個文件test_big.sql，不帶列名的

postgres=# \copy test_copy from '/home/postgres/test_big.sql';
COPY 1000000
postgres=# select count(*) from test_copy;count
---------1000000
(1 row)

導入第二個文件test_big2.sql，文件里面數(shù)據(jù)帶列名。

postgres=# \copy test_copy from '/home/postgres/test_big2.sql' with csv header;
COPY 1000000
postgres=# select count(*) from test_copy;count
---------2000000
(1 row)

導入第三個文件test_big3.sql，文件數(shù)據(jù)帶列名且分割符自定義類型。

postgres=# \copy test_copy from '/home/postgres/test_big3.sql' with csv header delimiter '|';
COPY 1000000
postgres=# select count(*) from test_copy;count
---------3000000
(1 row)

全部成功導入，總結一下

怎樣導出的，就可以怎樣導入

注意點

使用COPY命令進行數(shù)據(jù)導入或導出時，如果操作被中斷（例如通過按Ctrl+C），其行為會依賴于COPY命令的具體執(zhí)行方式以及你的操作環(huán)境。

使用psql命令行工具：

如果你使用psql命令行工具并運行\COPY命令，那么當操作被中斷時，通常psql會停止并可能顯示錯誤消息。但是，已經(jīng)成功傳輸?shù)綌?shù)據(jù)庫的數(shù)據(jù)不會被回滾，而已經(jīng)讀取但尚未傳輸?shù)綌?shù)據(jù)庫的數(shù)據(jù)可能會留在psql的緩沖區(qū)中。
如果你使用的是psql的\COPY命令，并且數(shù)據(jù)是通過管道（pipe）從另一個程序讀取的，那么當操作被中斷時，這個管道會被關閉，但已經(jīng)讀取的數(shù)據(jù)仍然可能留在psql的緩沖區(qū)中。

使用COPY SQL命令：

如果你在SQL腳本或命令行中使用COPY命令，并且該命令被中斷，那么已經(jīng)成功寫入數(shù)據(jù)庫的數(shù)據(jù)不會被回滾，但讀取的數(shù)據(jù)可能仍然在COPY命令的緩沖區(qū)中。
如果COPY命令使用了事務，并且事務被回滾，那么已經(jīng)寫入數(shù)據(jù)庫的數(shù)據(jù)會被回滾，但讀取的數(shù)據(jù)可能仍然留在COPY命令的緩沖區(qū)中。

COPY命令在PostgreSQL中非?？斓脑蛑饕獨w因于以下幾點：

直接文件訪問

COPY命令直接訪問文件，繞過了數(shù)據(jù)庫內部的一些中間層，從而減少了數(shù)據(jù)在數(shù)據(jù)庫和文件系統(tǒng)之間的額外傳輸。這使得COPY命令能夠更快地傳輸數(shù)據(jù)。

避免事務開銷

傳統(tǒng)的SQL插入操作可能涉及多個事務和回滾，這會增加額外的開銷。而COPY命令通常在一個事務中執(zhí)行，從而減少了事務開銷，提高了效率。

批量操作

COPY命令允許你一次性插入或導出大量數(shù)據(jù)，而不是一次插入或導出一條記錄。這種批量操作減少了數(shù)據(jù)庫與客戶端之間的通信次數(shù)，從而提高了效率。

跳過索引和觸發(fā)器

在執(zhí)行COPY命令時，PostgreSQL可以跳過索引的更新和觸發(fā)器的執(zhí)行，這進一步提高了性能。

減少鎖競爭

由于COPY命令通常在一個事務中執(zhí)行，所以它可以減少鎖競爭，從而避免阻塞其他操作。

利用磁盤緩存

PostgreSQL使用磁盤緩存來緩存數(shù)據(jù)，這有助于減少磁盤I/O操作，從而提高性能。

由于上述原因，COPY命令在PostgreSQL中通常比傳統(tǒng)的插入或導出方法更快。

COPY命令在PostgreSQL數(shù)據(jù)庫操作中扮演著重要角色，它提供了一種高效、自動化的數(shù)據(jù)導入和導出方法。通過正確的使用COPY命令，我們可以大大提高數(shù)據(jù)導入和導出的效率，減少人工錯誤，并確保數(shù)據(jù)的完整性和安全性。在實際應用中，我們需要根據(jù)數(shù)據(jù)量、格式和數(shù)據(jù)庫配置等因素，選擇合適的導入方法，并注意監(jiān)控數(shù)據(jù)庫的性能和資源使用情況，以確保系統(tǒng)的穩(wěn)定和數(shù)據(jù)的安全。

隨著數(shù)據(jù)庫技術的不斷發(fā)展，我們可以期待更多高效、自動化的數(shù)據(jù)操作方法的出現(xiàn)，以更好地滿足實際應用的需求。