PostgreSQL中的COPY命令：高效数据导入与导出

在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_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

将数据从文件导入到表

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命令，我们可以大大提高数据导入和导出的效率，减少人工错误，并确保数据的完整性和安全性。在实际应用中，我们需要根据数据量、格式和数据库配置等因素，选择合适的导入方法，并注意监控数据库的性能和资源使用情况，以确保系统的稳定和数据的安全。

随着数据库技术的不断发展，我们可以期待更多高效、自动化的数据操作方法的出现，以更好地满足实际应用的需求。