在PostgreSQL数据库中，数据导入和导出是日常工作中常见的操作。传统的插入（INSERT）方法虽然可以实现数据的导入，但在处理大量数据时效率较低。而COPY命令则提供了一个快速、高效的方式来完成这一任务。COPY命令不仅可以用于将数据从文件导入到表中，还可以将表中的数据导出到文件中，支持多种文件格式，如TEXT、BINARY和CSV。通过使用COPY命令，可以大大提高数据导入和导出的效率，尤其是在处理大量数据时。
一、引言
数据的导入与导出在数据库操作中的重要性：
数据库作为存储和管理数据的核心组件，其数据的导入和导出操作对于维护数据完整性、实现数据迁移、备份和恢复等任务至关重要。无论是对于大型企业还是个人用户，数据的导入和导出都是日常数据库操作中不可或缺的一部分。
传统数据导入方法的局限性：
传统的插入（INSERT）方法虽然可以实现数据的导入，但在处理大量数据时效率较低，且容易出错。
导入大量数据时，需要编写大量的SQL语句，这不仅耗时，而且容易引发错误。
对于数据的格式和一致性检查，传统方法也缺乏足够的自动化和灵活性。
COPY命令的引入及其优势：
COPY命令为PostgreSQL数据库提供了一种快速、高效的数据导入和导出方法。
它支持多种格式，如TEXT、BINARY和CSV，可以根据实际需求选择合适的格式。COPY命令可以直接从文件导入数据到表，或将表中的数据导出到文件，大大提高了数据导入和导出的效率。
与传统方法相比，COPY命令具有更高的自动化程度，能够减少人工错误，提高数据导入和导出的准确性。
二、COPY命令的基础
COPY命令概述：
COPY命令是PostgreSQL中用于高效导入和导出数据的命令。它支持从文件导入数据到表，或将表中的数据导出到文件。COPY命令可以处理文本、二进制和CSV格式的数据。支持的格式：TEXT、BINARY和CSV：
-
TEXT格式：适用于纯文本数据，每个字段由分隔符分隔。 -
BINARY格式：适用于二进制数据，例如图像、音频和视频等。 -
CSV格式：以逗号分隔值（Comma Separated Values）的形式存储数据，易于阅读和编辑。
数据的来源和去向：
- 数据来源可以是本地文件、远程文件或通过网络传输的数据流。
- 数据去向可以是本地文件、远程文件或通过网络发送的数据流。
通过COPY命令，我们可以将数据库中的数据导出到文件，或者从文件导入数据到数据库表中。
三、COPY命令的用法
将数据从表导出到文件
a. 语法示例：
COPY { table_name [ ( column_name [, ...] ) ] | ( query ) }
TO { 'filename' | PROGRAM 'command' | STDOUT }
[ [ WITH ] ( option [, ...] ) ]
b. 参数解释：
-
table_name：要导出数据的表名。 -
column_name：可选，指定要导出的列名。 -
filename：要导出的数据文件的路径。 -
PROGRAM 'command'：可选，指定执行命令以发送数据。 -
STDOUT：将数据发送到标准输出流。 -
option：可选，指定COPY命令的选项，如格式、分隔符、编码等。
c. 注意事项：
- 确保文件存在且可写。
- 根据需要选择正确的格式和分隔符。
- 注意文件路径的权限和所有权。
示例：
创建一个表并插入1000000条数据
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
查看导出的数据文件
[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
查看数据文件内容，可以看到第一行是表的字段名
[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
...
导出文件自定义数据分割符
如果想把这个逗号改成其他分隔符，可以使用delimiter关键字：
postgres=# \copy test_big to '/home/postgres/test_big3.sql' with csv header delimiter '|';
COPY 1000000
查看数据文件内容:
[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
...
导出部分数据
如果只想导出表中的部分数据，可以这样操作：
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条数据，导出的条件可以根据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_name
FREEZE [ 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
将数据从文件导入到表
a. 语法示例：
COPY table_name [ ( column_name [, ...] ) ]
FROM { 'filename' | PROGRAM 'command' | STDIN }
[ [ WITH ] ( option [, ...] ) ]
b. 参数解释：
-
table_name：要导入数据的表名。 -
column_name：可选，指定要导入的列名。 -
filename：要导入的数据文件的路径。 -
PROGRAM 'command'：可选，指定执行命令以获取数据。 -
STDIN：从标准输入流读取数据。 -
option：可选，指定COPY命令的选项，如格式、分隔符、编码等。
c. 注意事项：
- 确保数据文件与数据库中的表结构匹配。
- 根据需要选择正确的格式和分隔符。
- 确保数据文件存在且可读。
示例
从刚才导出的文件中导入数据。先创建一个空表
postgres=# create table test_copy(id int,name varchar(50));
CREATE TABLE
导入数据，按照刚才导出的顺序，先导入第一个文件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，文件里面数据带列名。
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，文件数据带列名且分割符自定义类型。
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命令进行数据导入或导出时，如果操作被中断（例如通过按Ctrl+C），其行为会依赖于COPY命令的具体执行方式以及你的操作环境。
使用psql命令行工具：
如果你使用psql命令行工具并运行\COPY命令，那么当操作被中断时，通常psql会停止并可能显示错误消息。但是，已经成功传输到数据库的数据不会被回滚，而已经读取但尚未传输到数据库的数据可能会留在psql的缓冲区中。
如果你使用的是psql的\COPY命令，并且数据是通过管道（pipe）从另一个程序读取的，那么当操作被中断时，这个管道会被关闭，但已经读取的数据仍然可能留在psql的缓冲区中。
使用COPY SQL命令：
如果你在SQL脚本或命令行中使用COPY命令，并且该命令被中断，那么已经成功写入数据库的数据不会被回滚，但读取的数据可能仍然在COPY命令的缓冲区中。
如果COPY命令使用了事务，并且事务被回滚，那么已经写入数据库的数据会被回滚，但读取的数据可能仍然留在COPY命令的缓冲区中。
COPY命令在PostgreSQL中非常快的原因主要归因于以下几点：
直接文件访问
COPY命令直接访问文件，绕过了数据库内部的一些中间层，从而减少了数据在数据库和文件系统之间的额外传输。这使得COPY命令能够更快地传输数据。
避免事务开销
传统的SQL插入操作可能涉及多个事务和回滚，这会增加额外的开销。而COPY命令通常在一个事务中执行，从而减少了事务开销，提高了效率。
批量操作
COPY命令允许你一次性插入或导出大量数据，而不是一次插入或导出一条记录。这种批量操作减少了数据库与客户端之间的通信次数，从而提高了效率。
跳过索引和触发器
在执行COPY命令时，PostgreSQL可以跳过索引的更新和触发器的执行，这进一步提高了性能。
减少锁竞争
由于COPY命令通常在一个事务中执行，所以它可以减少锁竞争，从而避免阻塞其他操作。
利用磁盘缓存
PostgreSQL使用磁盘缓存来缓存数据，这有助于减少磁盘I/O操作，从而提高性能。
由于上述原因，COPY命令在PostgreSQL中通常比传统的插入或导出方法更快。
COPY命令在PostgreSQL数据库操作中扮演着重要角色，它提供了一种高效、自动化的数据导入和导出方法。通过正确的使用COPY命令，我们可以大大提高数据导入和导出的效率，减少人工错误，并确保数据的完整性和安全性。在实际应用中，我们需要根据数据量、格式和数据库配置等因素，选择合适的导入方法，并注意监控数据库的性能和资源使用情况，以确保系统的稳定和数据的安全。
随着数据库技术的不断发展，我们可以期待更多高效、自动化的数据操作方法的出现，以更好地满足实际应用的需求。