Note

COPY

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

Synopsis

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

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

where option can be one of:

    FORMAT format_name
    OIDS [ boolean ]
    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'

Description

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

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

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

Parameters

• table_name

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

• column_name

  • 要复制的列的可选列表。如果未指定列列表，则将复制表的所有列。

• query

  • 一条要复制其结果的SELECT，VALUES，INSERT，UPDATE或DELETE命令。请注意，在查询周围需要括号。

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

• filename

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

• PROGRAM

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

请注意，该命令是由 Shell 程序调用的，因此，如果您需要将来自不受信任来源的任何参数传递给 Shell 程序命令，则必须小心删除或转义可能对 Shell 程序有特殊含义的任何特殊字符。出于安全原因，最好使用固定的命令字符串，或者至少避免在其中传递任何用户 Importing。

• STDIN

  • 指定 Importing 来自 Client 端应用程序。

• STDOUT

  • 指定将输出发送到 Client 端应用程序。

• boolean

  • 指定是打开还是关闭所选选项。您可以编写TRUE，ON或1启用该选项，并编写FALSE，OFF或0禁用该选项。 * boolean *的值也可以省略，在这种情况下假定为TRUE。

• FORMAT

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

• OIDS

  • 指定为每行复制 OID。 (如果为没有 OID 的表指定OIDS，或者复制* query *，则会引发错误.)

• FREEZE

  • 请求复制已冻结的行的数据，就像运行VACUUM FREEZE命令后一样。这旨在用作初始数据加载的性能选项。仅当在当前子事务中已创建或截断了要加载的表，没有打开游标且该事务没有任何较旧的快照时，行才会被冻结。当前无法在分区表上执行COPY FREEZE。

请注意，数据成功加载后，所有其他会话将立即能够看到数据。这违反了 MVCC 可见性的常规规则，指定用户应注意这可能引起的潜在问题。

• DELIMITER

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

• NULL

  • 指定表示空值的字符串。默认值为文本格式的\N(反斜杠-N)，以及CSV格式的无引号的空字符串。对于不想将空值与空字符串区分开的情况，甚至可能以文本格式使用空字符串。使用binary格式时，不允许使用此选项。

• HEADER

  • 指定文件包含标题行，其中包含文件中每一列的名称。输出时，第一行包含表中的列名，Importing 时，第一行被忽略。仅当使用CSV格式时才允许使用此选项。

• 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 *中。如果省略此选项，则使用当前的 Client 端编码。有关更多详细信息，请参见下面的 Comments。

Outputs

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

COPY count

• count *是复制的行数。

Notes

COPY TO只能用于普通表，不能用于视图。但是，您可以编写COPY (SELECT * FROM viewname) TO ...来复制视图的当前内容。

COPY FROM可以与普通表，外部表或分区表一起使用，也可以与具有INSTEAD OF INSERT触发器的视图一起使用。

COPY仅处理名为的特定表；它不会在子表之间复制数据。因此，例如COPY table TO显示的数据与SELECT * FROM ONLY table相同。但是COPY (SELECT * FROM table) TO ...可用于转储继承层次结构中的所有数据。

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

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

COPY命令中命名的文件由服务器而不是 Client 端应用程序直接读取或写入。因此，它们必须驻留在数据库服务器计算机上，或者必须位于数据库服务器计算机上，而不是 Client 端可以访问。 PostgreSQL 用户(服务器运行时使用的用户 ID)，而不是 Client 端，必须是可访问和可读写的。同样，用PROGRAM指定的命令直接由服务器执行，而不是由 Client 端应用程序执行，必须由 PostgreSQL 用户执行。 COPY仅允许数据库超级用户或被授予默认角色pg_read_server_files，pg_write_server_files或pg_execute_server_program之一的用户命名文件或命令，因为它允许读取或写入任何文件或运行服务器有权访问的程序。

不要将COPY与 psql 指令\copy混淆。 \copy调用COPY FROM STDIN或COPY TO STDOUT，然后将数据提取/存储在 psqlClient 端可访问的文件中。因此，使用\copy时，文件可访问性和访问权限取决于 Client 端而不是服务器。

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

用PROGRAM执行命令可能会受到 os 的访问控制机制(例如 SELinux)的限制。

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

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

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

Importing 数据将根据ENCODING选项或当前 Client 端编码进行解释，而输出数据将以ENCODING或当前 Client 端编码进行编码，即使数据没有通过 Client 端，而是由服务器直接从文件中读取或写入文件中。

COPY在第一个错误时停止操作。如果出现COPY TO，这应该不会导致问题，但是目标表将已经收到COPY FROM中的较早行。这些行将不可见或不可访问，但仍会占用磁盘空间。如果故障在大型复制操作中发生得很好，则可能会浪费大量磁盘空间。您可能希望调用VACUUM来恢复浪费的空间。

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

File Formats

Text Format

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

数据结尾可以由仅包含反斜杠句点(\.)的一行表示。从文件读取时，数据末尾标记不是必需的，因为文件末尾可以很好地工作；仅在使用 3.0 之前的 Client 端协议向 Client 端应用程序复制数据或从 Client 端应用程序复制数据时才需要。

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

指定的空字符串由COPY TO发送，不添加任何反斜杠；相反，在删除反斜杠之前，COPY FROM将 Importing 与空字符串进行匹配。因此，不能将诸如\N之类的空字符串与实际数据值\N(将其表示为\\N)混淆。

COPY FROM可以识别以下特殊的反斜杠序列：

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

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

强烈建议生成COPY数据的应用程序将数据换行符和回车分别转换为\n和\r序列。目前，可以通过反斜杠和回车符来表示数据回车，并且可以通过反斜杠和换行符来表示数据换行。但是，将来的发行版中可能不接受这些表示。如果COPY文件在不同的计算机上传输(例如，从 Unix 到 Windows，反之亦然)，它们也极易受到破坏。

COPY TO将以 Unix 样式的换行符(“ \n”)终止每一行。在 Microsoft Windows 上运行的服务器将输出回车换行符(“ \r\n”)，但仅将COPY输出到服务器文件；为了实现跨平台的一致性，无论服务器平台如何，COPY TO STDOUT始终发送“ \n”。 COPY FROM可以处理以换行符，回车符或回车符/换行符结尾的行。为了减少由于反斜杠换行符或回车符(作为数据而引起的错误)的风险，如果 Importing 中的行尾并不完全相同，则COPY FROM将进行投诉。

CSV Format

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

每个 Logging 的值用DELIMITER字符分隔。如果该值包含定界符，QUOTE字符，NULL字符串，回车符或换行符，则整个值将以QUOTE字符作为前缀和后缀，并且在QUOTE字符或ESCAPE字符前面有转义字符。在特定列中输出非NULL值时，也可以使用FORCE_QUOTE强制引号。

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

因为反斜杠不是CSV格式的特殊字符，所以\.(数据结束标记)也可以显示为数据值。为避免任何误解，出现在行上作为单独条目的\.数据值会在输出上自动加引号，并且在 Importing(如果加引号)时也不会解释为数据结束标记。如果要加载的文件是由另一个应用程序创建的，该文件只有一个未加引号的列，并且值可能为\.，则可能需要在 Importing 文件中引用该值。

Binary Format

binary格式选项使所有数据以二进制格式而不是文本形式存储/读取。它比文本和CSV格式要快一些，但是二进制格式的文件在计算机体系结构和 PostgreSQL 版本之间的可移植性较差。同样，二进制格式是非常特定于数据类型的。例如，从smallint列输出二进制数据并将其读入integer列将不起作用，即使这样在文本格式下也可以正常工作。

binary文件格式由文件头，包含行数据的零个或多个 Tuples 和文件尾部组成。Headers 和数据按网络字节 Sequences 排列。

File Header

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

• Signature

  • 11-字节序列PGCOPY\n\377\r\n\0-请注意，零字节是签名的必需部分。 (签名旨在方便识别非 8 位纯净传输所破坏的文件.此签名将通过行尾翻译过滤器，丢失的零字节，丢失的高位或平价更改.)

• Flags field

  • 32 位整数位掩码，表示文件格式的重要方面。位的编号从 0(LSB)到 31(MSB)。请注意，该字段以网络字节 Sequences 存储(最高有效字节在前)，文件格式中使用的所有整数字段也是如此。保留位 16-31 表示关键的文件格式问题；如果读取器发现在此范围内设置了意外的位，则应该中止。保留位 0-15 以指示向后兼容的格式问题；Reader 只需忽略此范围内设置的任何意外位。当前仅定义了一个标志位，其余的必须为零：

• Bit 16

  • 如果为 1，则 OID 包含在数据中；如果为 0，则不

• Headers 扩展区长度

  • 32 位整数，Headers 其余部分的字节长度，不包括 self。当前，它是零，并且第一个 Tuples 紧随其后。将来对格式进行更改可能会允许在 Headers 中显示其他数据。Reader 应静默跳过任何不知道该怎么做的 Headers 扩展数据。

头扩展区被设想为包含一系列自识别块。标志字段不是要告诉 Reader 扩展区域中的内容。Headers 扩展内容的特定设计留给以后的版本。

这种设计既允许向后兼容的 Headers 添加(添加 Headers 扩展块，或设置低位标志位)，也允许非向后兼容的更改(设置高位标志位来发出此类更改 signal，并向 extensions 添加支持数据)区域(如果需要)。

Tuples

每个 Tuples 都以 Tuples 中字段数的 16 位整数开始。 (当前，表中的所有 Tuples 都将具有相同的计数，但这可能并不总是正确的.)然后，对于 Tuples 中的每个字段重复此操作，将有一个 32 位长的字，后跟那么多字节的字段数据。 (长度字不包括自身，并且可以为零.)在特殊情况下，-1 表示 NULL 字段值。在 NULL 情况下，没有值字节。

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

当前，二进制格式文件中的所有数据值都假定为二进制格式(格式代码 1)。可以预期，将来的扩展可能会添加一个 Headers 字段，以允许指定每个列的格式代码。

要为实际的 Tuples 数据确定适当的二进制格式，应查阅 PostgreSQL 源，特别是每列数据类型的*send和*recv函数(通常这些函数在源分发的src/backend/utils/adt/目录中找到)。

如果文件中包含 OID，则 OID 字段紧跟在字段计数字之后。这是一个普通字段，但不包括在字段计数中。特别是它有一个长度字-这将允许处理 4 字节和 8 字节的 OID，而不会带来太多麻烦，并且如果确实需要的话，也可以将 OID 显示为 null。

File Trailer

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

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

Examples

下面的示例使用竖线(|)作为字段定界符将表复制到 Client 端：

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';

要复制到 zipfile 中，可以通过外部压缩程序通过管道传递输出：

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

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

AF      AFGHANISTAN
AL      ALBANIA
DZ      ALGERIA
ZM      ZAMBIA
ZW      ZIMBABWE

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

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

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

Compatibility

SQL 标准中没有COPY语句。

在 PostgreSQL 9.0 之前使用了以下语法，并且仍然受支持：

COPY table_name [ ( column_name [, ...] ) ]
    FROM { 'filename' | STDIN }
    [ [ WITH ]
          [ BINARY ]
          [ OIDS ]
          [ 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 ]
          [ OIDS ]
          [ 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选项的参数。

在 PostgreSQL 7.3 之前使用了以下语法，并且仍受支持：

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

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