COPY 在 PostgreSQL 表和标准文件系统文件之间移动数据。COPY TO 将表的内容复制到文件中，而 COPY FROM 将数据从文件复制到表中(将数据附加到表中已经存在的任何内容中)。`COPY TO`也可以复制`SELECT`查询的结果。

如果指定了列表列，则 COPY TO 仅将指定列中的数据复制到文件中。对于 COPY FROM，按顺序将文件中的每个字段插入到指定的列中。`COPY FROM`列表列中未指定的表列将接收它们的默认值。

语法

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 ]
    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/12/sql-copy.html

参数

• table_name

  现有表的名称(可选模式限定)。

• column_name

  要复制的列的可选列表。如果没有指定列列表，则将复制表中除生成列之外的所有列。

• query

  根据 SELECT、VALUES、INSERT、UPDATE 或 DELETE 命令的结果进行复制。注意，查询周围需要圆括号。对于 INSERT、UPDATE 和 DELETE 查询，必须提供一个返回子句，并且目标关系不能有条件规则，也不能有 ALSO 规则，也不能有扩展为多个语句的 INSTEAD 规则。

• filename

  输入或输出文件的路径名。输入文件名可以是绝对路径，也可以是相对路径，但输出文件名必须是绝对路径。Windows 用户可能需要使用“E”字符串，并在路径名中使用双斜杠。

• PROGRAM

  要执行的命令。在`COPY FROM`中，输入是从命令的标准输出读取的，而在`COPY TO`中，输出被写入命令的标准输入。注意，该命令是由 shell 调用的，因此，如果需要将来自不可信源的任何参数传递给 shell 命令，则必须小心地去掉或转义任何可能对 shell 有特殊意义的特殊字符。出于安全考虑，最好使用固定的命令字符串，或者至少避免在其中传递任何用户输入。

• STDIN

  指定输入来自客户端应用程序。

• STDOUT

  指定输出到客户端应用程序。

• boolean

  指定所选选项应该打开还是关闭。您可以写`TRUE`、`ON`或`1`来启用该选项，而写`FALSE`、`OFF`或`0`来禁用它。也可以省略*`boolean`*值，在这种情况下假定为' TRUE '。

• FORMAT

  选择要读取或写入的数据格式：`text`、`csv`(逗号分隔值)或`binary`。默认是`text`。

• FREEZE

  请求使用已经冻结的行复制数据，就像在运行`VACUUM FREEZE` 命令之后所做的那样。这是用于初始数据加载的性能选项。只有在当前子事务中创建或截断了正在加载的表，没有打开游标，也没有该事务持有的旧快照时，才会冻结行。目前无法在分区表上执行`COPY FREEZE`。注意，一旦成功加载数据，所有其他会话将立即能够看到数据。这违反了MVCC可见性的正常规则，用户在指定时应该意识到这可能会导致的潜在问题。

• DELIMITER

  指定在文件的每一行(行)中分隔列的字符。默认是文本格式的制表符，`CSV`格式的逗号。这必须是一个单字节字符。当使用`binary`格式时，不允许使用此选项。

• NULL

  指定表示空值的字符串。默认是`\N` 的文本格式，以及`CSV`格式的无引号空字符串。对于不希望将 null 与空字符串区分开的情况，即使是文本格式，您也可能更喜欢使用空字符串。当使用`binary` 格式时，不允许使用此选项。当使用`COPY FROM`时，任何与该字符串匹配的数据项都将被存储为空值，因此您应该确保使用与`COPY TO`相同的字符串。

• HEADER

  指定文件包含一个标题行，其中包含文件中每个列的名称。在输出时，第一行包含表中的列名，而在输入时，第一行被忽略。只有在使用`CSV`格式时才允许使用此选项。

• QUOTE

  指定在引用数据值时使用的引用字符。默认是双引号。这必须是一个单字节字符。只有在使用`CSV`格式时才允许使用此选项。

• ESCAPE

  指定应该出现在匹配`QUOTE`值的数据字符之前的字符。默认值与`QUOTE`值相同(因此如果引用字符出现在数据中，它将被加倍)。这必须是一个单字节字符。只有在使用`CSV`格式时才允许使用此选项。

• FORCE_QUOTE

  强制引用用于每个指定列中的所有非`NULL`值。`NULL`输出从不被引用。如果`*`被指定，非`NULL`值将在所有列中引用。此选项只允许在`COPY TO`，并只在使用`CSV`格式。

• FORCE_NOT_NULL

  不要将指定列的值与空字符串匹配。在默认情况下，空字符串为 null，这意味着空值将被读取为零长度的字符串，而不是空值，即使它们没有被引用。此选项只允许在`COPY FROM`中使用，并且只在使用`CSV`格式时使用。

• FORCE_NULL

  将指定列的值与空字符串匹配，即使它已被引用，如果找到匹配，则将值设置为`NULL`。在空字符串为空的默认情况下，这将把带引号的空字符串转换为空字符串。此选项只允许在`COPY FROM`中使用，并且只在使用`CSV`格式时使用。

• ENCODING

  指定文件编码在`encoding_name`中。如果省略此选项，则使用当前客户端编码。

• WHERE

  可选的`WHERE` 子句具有一般形式`WHERE *`条件在*`condition`* 是计算结果为`boolean`类型的任何表达式。任何不满足此条件的行都不会被插入到表中。当实际的行值被替换为任何变量引用时，如果行返回 true，则满足该条件。目前，子查询在`WHERE` 表达式中是不允许的，并且计算不会看到`COPY` 本身所做的任何更改(当表达式包含对`VOLATILE` 函数的调用时，这一点很重要)。

示例

导出CSV：

命令

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

copy to的导出速度非常之快，经测试 10W 的数据量只需要 3 秒左右的时间

示例

COPY user TO '/tmp/data/test.csv' WITH csv;

也可以导出指定的属性

COPY user(name,password) TO '/tmp/data/test.csv' WITH csv;

也可以使用select 语句

COPY (select * from user) TO '/tmp/data/test.csv' WITH csv;

也可以指定要导出哪些字段：

COPY (select name,age from user) TO '/tmp/data/test.csv' WITH csv header;

也可以使用竖线('|')作为字段分隔符将表复制到客户端：

COPY country TO STDOUT (DELIMITER '|');

导入CSV：

命令

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

示例

COPY user_1 FROM '/tmp/data/test.csv' WITH csv;

导入命令基本与导出一样，只是将 TO 改为 FROM

如果导出的时候，指定了header属性，那么在导入的时候，也需要指定：

COPY user_1(name, age) FROM '/tmp/data/test.csv' WITH csv header;

复制到一个压缩文件，你可以通过管道输出通过一个外部压缩程序：

COPY country TO PROGRAM 'gzip > /usr1/proj/bray/sql/country_data.gz';

注意

第一点 

copy命令必须在 plsql 命令行执行，执行用户必须为 superuser，否则会提示：

ERROR:  must be superuser to COPY to or from a file
HINT:  Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.

普通用户进行执行，需要在copy前面加入 “\”，即 \copy即可

第二点

如果导出的字段，有 integer[] 类型，直接导出，再导入的话，会有问题，解决办法是需要在导出的时候，进行处理：

COPY ( select coalesce(integer_array, '{}')::integer[] as integer_array from table ) TO '/tmp/data.csv' with csv header;