COPY

COPY — 在文件和表之间复制数据

语法

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'
    DEFAULT 'default_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'

描述

COPY在PostgreSQL表和标准文件系统文件之间移动数据。COPY TO将表的内容复制到文件中，而COPY FROM将数据从文件复制到表中（将数据追加到表中已有的内容）。COPY TO还可以复制SELECT查询的结果。

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

带有文件名COPY指示PostgreSQL服务器直接从文件读取或写入文件。PostgreSQL用户（服务器运行的 User ID）必须可以访问该文件，并且必须从服务器的角度指定名称。当指定PROGRAM时，服务器执行给定的命令并从程序的标准输出读取，或写入程序的标准输入。必须从服务器的角度指定命令，并且PostgreSQL用户可以执行该命令。当指定STDIN或STDOUT时，数据通过客户端和服务器之间的连接传输。

运行COPY的每个后端将在pg_stat_progress_copy视图中报告其进度。有关详细信息，请参见第 28.4.3 节。

参数

table_name

现有表的名称（可选模式限定）。

column_name

要复制的列的可选列表。如果未指定列列表，将复制表的所有列，但生成的列除外。

query

SELECT、VALUES、INSERT、UPDATE 或 DELETE 命令，其结果将被复制。请注意，查询周围需要括号。

对于 INSERT、UPDATE 和 DELETE 查询，必须提供 RETURNING 子句，并且目标关系不能有条件规则、ALSO 规则或扩展为多个语句的 INSTEAD 规则。

filename

输入或输出文件的路径名。输入文件名可以是绝对路径或相对路径，但输出文件名必须是绝对路径。Windows 用户可能需要使用 E'' 字符串，并对路径名中使用的任何反斜杠加倍。

PROGRAM

要执行的命令。在 COPY FROM 中，从命令的标准输出读取输入，而在 COPY TO 中，将输出写入命令的标准输入。

请注意，该命令是由 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（反斜杠-N），CSV 格式为未引用的空字符串。即使在文本格式中，您可能也更喜欢使用空字符串，以防您不想将空值与空字符串区分开来。使用 binary 格式时不允许此选项。

注意

使用 COPY FROM 时，任何与该字符串匹配的数据项都将存储为空值，因此您应确保使用与 COPY TO 相同的字符串。

DEFAULT

指定表示默认值的字符串。每次在输入文件中找到该字符串时，都将使用相应列的默认值。此选项仅允许在 COPY FROM 中使用，并且仅在不使用 binary 格式时使用。

HEADER

指定该文件包含一行标题，其中包含文件中的每个列的名称。在输出中，第一行包含表中的列名。在输入中，当此选项设置为 true（或等效布尔值）时，第一行将被丢弃。如果此选项设置为 MATCH，则标题行中的列数和名称必须按顺序与表的实际列名匹配；否则会引发错误。使用 binary 格式时不允许此选项。 MATCH 选项仅对 COPY FROM 命令有效。

QUOTE

指定在引用数据值时要使用的引用字符。默认情况下为双引号。这必须是一个单字节字符。此选项仅在使用 CSV 格式时才允许。

ESCAPE

指定在与 QUOTE 值匹配的数据字符前应显示的字符。默认值与 QUOTE 值相同（因此如果引用字符出现在数据中，则该字符会加倍）。这必须是一个单字节字符。仅在使用 CSV 格式时允许此选项。

FORCE_QUOTE

强制对每个指定列中所有非 NULL 值使用引用。永远不会引用 NULL 输出。如果指定 *，则在所有列中都会引用非 NULL 值。仅在 COPY TO 中允许此选项，并且仅在使用 CSV 格式时允许。

FORCE_NOT_NULL

不将指定列的值与空字符串进行匹配。在空字符串为空的默认情况下，这意味着即使空值没有加引号，也会将它们读作零长度字符串，而不是空值。仅在 COPY FROM 中允许此选项，并且仅在使用 CSV 格式时允许。

FORCE_NULL

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

ENCODING

指定文件以 encoding_name 编码。如果省略此选项，则使用当前客户端编码。有关更多详细信息，请参阅下面的注释。

WHERE

可选的 WHERE 子句具有以下通用形式

WHERE condition

其中 condition 是任何求值为 boolean 类型的表达式的表达式。任何不满足此条件的行都不会插入到表中。当使用实际行值替换任何变量引用时，如果行返回 true，则该行满足条件。

目前，不允许在 WHERE 表达式中使用子查询，并且评估不会看到 COPY 本身所做的任何更改（当表达式包含对 VOLATILE 函数的调用时，这一点很重要）。

输出

成功完成后，COPY命令将返回以下形式的命令标记

COPY count

*count*是已复制的行数。

注意

psql仅在命令不是COPY ... TO STDOUT或等效的psql元命令\copy ... to stdout时才打印此命令标记。这是为了防止将命令标记与刚刚打印的数据混淆。

注释

COPY TO只能与普通表（而非视图）一起使用，并且不复制子表或子分区中的行。例如，COPY*table*TO复制的行与SELECT * FROM ONLY*table*相同。语法COPY (SELECT * FROM*table*) TO ...可用于转储继承层次结构、分区表或视图中的所有行。

COPY FROM可用于普通表、外部表或分区表，或具有INSTEAD OF INSERT触发器的视图。

您必须对COPY TO读取其值的表具有选择权限，并且对COPY FROM插入值的表具有插入权限。在命令中列出的列上具有列权限就足够了。

如果为表启用了行级安全性，则相关的SELECT策略将应用于COPY*table*TO语句。目前，COPY FROM不支持具有行级安全性的表。请改用等效的INSERT语句。

COPY命令中命名的文件由服务器直接读取或写入，而不是由客户端应用程序读取或写入。因此，它们必须驻留在数据库服务器机器上或可由其访问，而不是客户端。它们必须可供PostgreSQL用户（服务器运行时使用的用户 ID）访问和读写，而不是客户端。类似地，使用PROGRAM指定的命令由服务器直接执行，而不是由客户端应用程序执行，并且必须可供PostgreSQL用户执行。只有数据库超级用户或被授予pg_read_server_files、pg_write_server_files或pg_execute_server_program角色之一的用户才能命名文件或命令的COPY，因为它允许读取或写入服务器有权访问的任何文件或运行程序。

不要将COPY与psql指令[\copy](app-psql.html#APP-PSQL-META-COMMANDS-COPY)混淆。\copy调用COPY FROM STDIN或COPY TO STDOUT，然后获取/存储可供psql客户端访问的文件中的数据。因此，当使用\copy时，文件可访问性和访问权限取决于客户端，而不是服务器。

建议始终将COPY中使用的文件名指定为绝对路径。对于COPY TO，服务器会强制执行此操作，但对于COPY FROM，您可以选择从相对路径指定的文件中读取。路径将相对于服务器进程的工作目录（通常是集群的数据目录）解释，而不是客户端的工作目录。

使用PROGRAM执行命令可能会受到 SELinux 等操作系统访问控制机制的限制。

COPY FROM将调用任何触发器并检查目标表上的约束。但是，它不会调用规则。

对于标识列，COPY FROM命令将始终写入输入数据中提供的列值，就像INSERT选项OVERRIDING SYSTEM VALUE。

COPY输入和输出受DateStyle影响。为了确保可移植到可能使用非默认DateStyle设置的其他PostgreSQL安装，在使用COPY TO之前，应将DateStyle设置为ISO。最好避免将数据转储到IntervalStyle设置为sql_standard的情况下，因为负间隔值可能会被具有不同IntervalStyle设置的服务器误解。

输入数据根据ENCODING选项或当前客户端编码进行解释，输出数据编码为ENCODING或当前客户端编码，即使数据不通过客户端，而是由服务器直接从文件读取或写入到文件。

COPY在第一个错误时停止操作。在COPY TO事件中，这不应导致问题，但目标表已经在COPY FROM中接收了较早的行。这些行将不可见或不可访问，但它们仍然占用磁盘空间。如果在大型复制操作中发生故障，这可能会浪费大量磁盘空间。您可能希望调用VACUUM以回收浪费的空间。

FORCE_NULL和FORCE_NOT_NULL可以同时在同一列上使用。这会导致将带引号的空字符串转换为 null 值，将不带引号的空字符串转换为空字符串。

文件格式

文本格式

当使用text格式时，读取或写入的数据是一个文本文件，每行对应一个表行。一行中的列由分隔符字符分隔。列值本身是字符串，由输出函数生成，或可接受每个属性数据类型的输入函数。指定的空字符串用于代替为 null 的列。如果输入文件的任何行包含的列多于或少于预期，COPY FROM将引发错误。

数据末尾可以用仅包含反斜杠句点 (\.) 的单行表示。从文件读取时不需要数据末尾标记，因为文件末尾可以很好地发挥作用；仅在使用 3.0 之前的客户端协议将数据复制到或从客户端应用程序复制时才需要。

反斜杠字符 (\) 可用于COPY数据，以引用可能被视为行或列分隔符的数据字符。特别是，如果以下字符必须作为列值的一部分出现，则必须在它们之前加上反斜杠：反斜杠本身、换行符、回车符和当前分隔符字符。

指定的空字符串由COPY TO发送，而无需添加任何反斜杠；相反，COPY FROM在删除反斜杠之前将输入与空字符串进行匹配。因此，诸如\N的空字符串不会与实际数据值\N（表示为\\N）混淆。

以下特殊反斜杠序列由COPY FROM识别

序列	表示
\b	退格 (ASCII 8)
\f	换页符 (ASCII 12)
\n	换行符 (ASCII 10)
\r	回车符 (ASCII 13)
\t	制表符 (ASCII 9)
\v	垂直制表符 (ASCII 11)
\digits	反斜杠后跟一个到三个八进制数字指定具有该数字代码的字节
\xdigits	反斜杠 x 后跟一个或两个十六进制数字指定具有该数字代码的字节

目前，COPY TO永远不会发出八进制或十六进制数字反斜杠序列，但它确实对那些控制字符使用上面列出的其他序列。

上表中未提及的任何其他反斜杠字符都将表示其自身。但是，请注意不要不必要地添加反斜杠，因为这可能会意外生成与数据末尾标记 (\.) 或空字符串 (\N默认) 匹配的字符串。这些字符串将在执行任何其他反斜杠处理之前被识别。

强烈建议生成COPY数据的应用程序将数据换行符和回车符分别转换为\n和\r序列。目前，可以使用反斜杠和回车符表示数据回车符，并使用反斜杠和换行符表示数据换行符。但是，这些表示形式在未来版本中可能不被接受。如果COPY文件在不同的机器（例如，从 Unix 到 Windows 或反之亦然）之间传输，它们也很容易损坏。

所有反斜杠序列在编码转换后解释。使用八进制和十六进制反斜杠序列指定的字节必须在数据库编码中形成有效字符。

COPY TO将使用 Unix 风格的新行符 (“\n”) 终止每行。在 Microsoft Windows 上运行的服务器会输出回车/换行符 (“\r\n”)，但仅适用于COPY到服务器文件；为了跨平台的一致性，COPY TO STDOUT始终发送“\n”，无论服务器平台如何。COPY FROM可以处理以换行符、回车符或回车/换行符结尾的行。为了降低由于未加反斜杠的新行符或回车符（作为数据）而导致错误的风险，如果输入中的行尾不全部相同，COPY FROM将发出警告。

CSV 格式

此格式选项用于导入和导出许多其他程序（例如电子表格）使用的逗号分隔值 (CSV) 文件格式。它生成并识别常见的CSV转义机制，而不是PostgreSQL的标准文本格式使用的转义规则。

每条记录中的值都由DELIMITER字符分隔。如果值包含分隔符字符、QUOTE字符、NULL字符串、回车或换行符字符，那么整个值都将以QUOTE字符为前缀和后缀，并且值中出现的任何QUOTE字符或ESCAPE字符都将以转义字符为前缀。您还可以使用FORCE_QUOTE在输出特定列中的非NULL值时强制使用引号。

CSV格式没有标准方法来区分NULL值和空字符串。PostgreSQL的COPY通过引用来处理此问题。NULL输出为NULL参数字符串，并且不加引号，而与NULL参数字符串匹配的非NULL值则加引号。例如，在默认设置下，NULL写为不加引号的空字符串，而空字符串数据值则写为双引号 ("")。读取值遵循类似的规则。您可以使用FORCE_NOT_NULL来防止对特定列进行NULL输入比较。您还可以使用FORCE_NULL将带引号的空字符串数据值转换为NULL。

由于反斜杠不是CSV格式中的特殊字符，因此\.（数据结束标记）也可能显示为数据值。为了避免任何误解，如果\.数据值显示为行上的孤条目，则在输出时会自动加上引号，并且在输入时，如果加了引号，则不会解释为数据结束标记。如果您正在加载由另一个应用程序创建的文件，该文件具有单个不加引号的列，并且可能具有\.的值，则可能需要在输入文件中加上该值的引号。

注意

在CSV格式中，所有字符都具有重要意义。被空格或DELIMITER以外的任何字符包围的带引号值将包括那些字符。如果您从将CSV行填充为固定宽度的空格的系统导入数据，这可能会导致错误。如果出现这种情况，您可能需要预处理CSV文件以删除尾随空格，然后再将数据导入PostgreSQL。

注意

CSV格式将识别和生成包含嵌入式回车和换行符的带引号值的CSV文件。因此，这些文件不像文本格式文件那样严格地每行对应一个表行。

注意

许多程序会生成奇怪且偶尔会产生反常的CSV文件，因此文件格式更像是一种约定，而不是一种标准。因此，您可能会遇到无法使用此机制导入的一些文件，并且COPY可能会生成其他程序无法处理的文件。

二进制格式

binary格式选项会导致所有数据以二进制格式存储/读取，而不是文本格式。它比文本和CSV格式快一些，但二进制格式文件在机器架构和PostgreSQL版本之间的可移植性较差。此外，二进制格式非常特定于数据类型；例如，它无法从smallint列输出二进制数据并将其读入integer列，即使在文本格式中这样做也可以正常工作。

binary文件格式包含一个文件头、零个或多个包含行数据的元组以及一个文件尾部。标头和数据采用网络字节顺序。

注意

7.4 之前的PostgreSQL版本使用不同的二进制文件格式。

文件头

文件头由 15 个固定字段字节组成，后跟一个可变长度的头扩展区。固定字段为

签名

11 字节序列 PGCOPY\n\377\r\n\0 — 请注意，零字节是签名所需的一部分。（该签名旨在允许轻松识别已被非 8 位干净传输弄乱的文件。此签名将被行尾转换过滤器、丢弃的零字节、丢弃的高位或奇偶校验更改。）

标志字段

32 位整数位掩码，用于表示文件格式的重要方面。位从 0（）到 31（）进行编号。请注意，此字段以网络字节顺序存储（最高有效字节在前），就像文件格式中使用的所有整数字段一样。位 16-31 保留用于表示关键文件格式问题；如果读取器发现此范围内设置了意外的位，则应中止。位 0-15 保留用于发出向后兼容的格式问题信号；读取器应忽略此范围内设置的任何意外位。目前只定义了一个标志位，其余的必须为零

第 16 位

如果为 1，则数据中包含 OID；如果为 0，则不包含。Oid 系统列在 PostgreSQL 中不再受支持，但格式仍然包含该指示符。

标头扩展区域长度

32 位整数，标头其余部分的字节长度，不包括自身。目前，此值为零，第一个元组紧随其后。对格式的未来更改可能会允许在标头中存在其他数据。读取器应静默跳过它不知道如何处理的任何标头扩展数据。

标头扩展区域设想包含一系列自标识块。标志字段不打算告诉读取器扩展区域中有什么。标头扩展内容的具体设计留待以后发布。

此设计允许向后兼容的标头添加（添加标头扩展块或设置低阶标志位）和不向后兼容的更改（设置高阶标志位以发出此类更改的信号，并在需要时将支持数据添加到扩展区域）。

元组

每个元组以一个 16 位整数开始，该整数表示元组中字段的数量。（目前，表中的所有元组都具有相同数量，但这可能并不总是如此。）然后，对于元组中的每个字段，都有一个 32 位长度字，后跟该长度的字段数据字节。（长度字不包括自身，可以为零。）作为特殊情况，-1 表示 NULL 字段值。在 NULL 情况下，没有值字节跟随。

字段之间没有对齐填充或任何其他额外数据。

目前，二进制格式文件中的所有数据值都假定为二进制格式（格式代码一）。预计未来的扩展可能会添加一个标头字段，该字段允许指定按列的格式代码。

要确定实际元组数据的适当二进制格式，您应该查阅PostgreSQL源代码，特别是每列数据类型的*send和*recv函数（通常这些函数位于源分发版的src/backend/utils/adt/目录中）。

如果文件中包含 OID，则 OID 字段紧跟在字段计数词之后。它是一个常规字段，但它不包含在字段计数中。请注意，当前版本的PostgreSQL中不支持 oid 系统列。

文件尾部

文件尾部由一个包含 -1 的 16 位整数词组成。这很容易与元组的字段计数词区分开来。

如果字段计数词既不是 -1，也不是预期的列数，则读取器应报告错误。这提供了额外的检查，以防止与数据不同步。

示例

以下示例使用竖线 (|) 作为字段分隔符，将表复制到客户端

COPY country TO STDOUT (DELIMITER '|');

将数据从文件复制到country表中

COPY country FROM '/usr1/proj/bray/sql/country_data';

仅将名称以“A”开头的国家复制到文件中

COPY (SELECT * FROM country WHERE country_name LIKE 'A%') TO '/usr1/proj/bray/sql/a_list_countries.copy';

要复制到压缩文件中，可以通过外部压缩程序管道输出

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

以下是适合从STDIN复制到表中的数据示例

AF      AFGHANISTAN
AL      ALBANIA
DZ      ALGERIA
ZM      ZAMBIA
ZW      ZIMBABWE

请注意，每行中的空白实际上是一个制表符。

以下是相同的数据，以二进制格式输出。数据在通过 Unix 实用程序od -c过滤后显示。该表有三个列；第一个的类型为char(2)，第二个的类型为text，第三个的类型为integer。第三列的所有行都为 null 值。

0000000   P   G   C   O   P   Y  \n 377  \r  \n  \0  \0  \0  \0  \0  \0
0000020  \0  \0  \0  \0 003  \0  \0  \0 002   A   F  \0  \0  \0 013   A
0000040   F   G   H   A   N   I   S   T   A   N 377 377 377 377  \0 003
0000060  \0  \0  \0 002   A   L  \0  \0  \0 007   A   L   B   A   N   I
0000100   A 377 377 377 377  \0 003  \0  \0  \0 002   D   Z  \0  \0  \0
0000120 007   A   L   G   E   R   I   A 377 377 377 377  \0 003  \0  \0
0000140  \0 002   Z   M  \0  \0  \0 006   Z   A   M   B   I   A 377 377
0000160 377 377  \0 003  \0  \0  \0 002   Z   W  \0  \0  \0  \b   Z   I
0000200   M   B   A   B   W   E 377 377 377 377 377 377

兼容性

SQL 标准中没有COPY语句。

以下语法在PostgreSQL9.0 版之前使用，并且仍然受支持

COPY table_name [ ( column_name [, ...] ) ]
    FROM { 'filename' | STDIN }
    [ [ WITH ]
          [ BINARY ]
          [ DELIMITER [ AS ] 'delimiter_character' ]
          [ NULL [ AS ] 'null_string' ]
          [ CSV [ HEADER ]
                [ QUOTE [ AS ] 'quote_character' ]
                [ ESCAPE [ AS ] 'escape_character' ]
                [ FORCE NOT NULL column_name [, ...] ] ] ]

COPY { table_name [ ( column_name [, ...] ) ] | ( query ) }
    TO { 'filename' | STDOUT }
    [ [ WITH ]
          [ BINARY ]
          [ DELIMITER [ AS ] 'delimiter_character' ]
          [ NULL [ AS ] 'null_string' ]
          [ CSV [ HEADER ]
                [ QUOTE [ AS ] 'quote_character' ]
                [ ESCAPE [ AS ] 'escape_character' ]
                [ FORCE QUOTE { column_name [, ...] | * } ] ] ]

请注意，在此语法中，BINARY和CSV被视为独立关键字，而不是FORMAT选项的参数。

以下语法在PostgreSQL7.3 版之前使用，并且仍然受支持

COPY [ BINARY ] table_name
    FROM { 'filename' | STDIN }
    [ [USING] DELIMITERS 'delimiter_character' ]
    [ WITH NULL AS 'null_string' ]

COPY [ BINARY ] table_name
    TO { 'filename' | STDOUT }
    [ [USING] DELIMITERS 'delimiter_character' ]
    [ WITH NULL AS 'null_string' ]

另请参见

第 28.4.3 节