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

一、引言

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

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

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

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

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

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

二、COPY命令的基礎(chǔ)

COPY命令概述：

COPY命令是PostgreSQL中用于高效導(dǎo)入和導(dǎo)出數(shù)據(jù)的命令。它支持從文件導(dǎo)入數(shù)據(jù)到表，或?qū)⒈碇械臄?shù)據(jù)導(dǎo)出到文件。COPY命令可以處理文本、二進(jìn)制和CSV格式的數(shù)據(jù)。支持的格式：TEXT、BINARY和CSV：

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

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

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

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

三、COPY命令的用法

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

a. 語(yǔ)法示例：

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

b. 參數(shù)解釋：

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

c. 注意事項(xiàng)：

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

示例：

創(chuàng)建一個(gè)表并插入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)

默認(rèn)不帶條件導(dǎo)出

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

查看導(dǎo)出的數(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
...

導(dǎo)出文件帶字段名

如果需要把列名也打出來(lái)，可以加 with csv header;

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

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

[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
...

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

如果想把這個(gè)逗號(hào)改成其他分隔符，可以使用delimiter關(guān)鍵字：

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

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

[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
...

導(dǎo)出部分?jǐn)?shù)據(jù)

如果只想導(dǎo)出表中的部分?jǐn)?shù)據(jù)，可以這樣操作：

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

查看文件內(nèi)容：

[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ù)，導(dǎo)出的條件可以根據(jù)sql自己定義。
更多語(yǔ)法可以通過(guò)\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ù)從文件導(dǎo)入到表

a. 語(yǔ)法示例：

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

b. 參數(shù)解釋：

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

c. 注意事項(xiàng)：

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

示例

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

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

導(dǎo)入數(shù)據(jù)，按照剛才導(dǎo)出的順序，先導(dǎo)入第一個(gè)文件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)

導(dǎo)入第二個(gè)文件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)

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

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)

全部成功導(dǎo)入，總結(jié)一下

怎樣導(dǎo)出的，就可以怎樣導(dǎo)入

注意點(diǎn)

使用COPY命令進(jìn)行數(shù)據(jù)導(dǎo)入或?qū)С鰰r(shí)，如果操作被中斷（例如通過(guò)按Ctrl+C），其行為會(huì)依賴(lài)于COPY命令的具體執(zhí)行方式以及你的操作環(huán)境。

使用psql命令行工具：

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

使用COPY SQL命令：

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

COPY命令在PostgreSQL中非?？斓脑蛑饕?dú)w因于以下幾點(diǎn)：

直接文件訪問(wèn)

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

避免事務(wù)開(kāi)銷(xiāo)

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

批量操作

COPY命令允許你一次性插入或?qū)С龃罅繑?shù)據(jù)，而不是一次插入或?qū)С鲆粭l記錄。這種批量操作減少了數(shù)據(jù)庫(kù)與客戶(hù)端之間的通信次數(shù)，從而提高了效率。

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

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

減少鎖競(jìng)爭(zhēng)

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

利用磁盤(pán)緩存

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

由于上述原因，COPY命令在PostgreSQL中通常比傳統(tǒng)的插入或?qū)С龇椒ǜ臁?/p>

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

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