Postgresql 中文操作指南

COPY

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

Synopsis

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'

Description

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

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

带有文件名的 COPY 指示 PostgreSQL 服务器从文件直接读取或将数据直接写入文件。对于 PostgreSQL 用户（服务器运行的身份 ID）来说，该文件必须可访问，且必须从服务器的观点指定该名称。当指定 PROGRAM 时，服务器会执行给定的命令并从该程序的标准输出中读取，或写入该程序的标准输入中。该命令必须从服务器的观点指定，并且对于 PostgreSQL 用户来说是可执行的。当指定 STDIN 或 STDOUT 时，数据则通过客户端与服务器之间的连接进行传输。

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

Parameters

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 格式时不允许该选项。
DEFAULT
- 指定用于表示默认值的字符串。每次在输入文件中找到该字符串时，都将使用相应列的默认值。仅在 COPY FROM 中允许此选项，并且仅在不使用 binary 格式时允许此选项。
HEADER
- 指定该文件包含一个标题行，其中包含文件中每一列的名称。在输出时，第一行包含来自该表的列名。在启用该选项（或等效布尔值）时，在输入时将丢弃第一行。如果将此选项设置为 true ，则标题行中列的数量和名称必须按顺序与表的实际列名匹配；否则将引发错误。在使用 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 函数的调用时，这一点非常重要）。

Note

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

Outputs

在成功完成时， COPY 命令会返回以下形式的命令标记：

COPY count

count 为复制的行数。

Note

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

Notes

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 指定的命令由服务器直接执行，而不是由客户端应用程序执行，并且 PostgeSQL 用户必须可以执行此命令。命名文件或命令的 COPY 仅允许数据库超级用户或被授予 pg_read_server_files 、 pg_write_server_files 或 pg_execute_server_program 角色之一的用户使用，因为它允许读取或写入服务器具有访问权限的任何文件或运行程序。

不要将 COPY 与 psql 指令 _ \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 值，将不带引号的空字符串转换为空字符串。

File Formats

Text Format

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

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

反斜杠字符 ( \ ) 可以用在 COPY 数据中，用来引用可能被当作行或列分隔符的数据字符。特别是，如果以下字符 must 作为列值的一部分出现的话，则其前面应该加上一个反斜杠：反斜杠本身、换行符、回车符以及当前分隔符字符。

指定的空字符串将由 COPY TO 发送，而无需添加任何反斜杠；相反， COPY FROM 在删除反斜杠之前会将输入与空字符串进行匹配。因此，像 \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 可以处理以换行符、回车符或回车符/换行符结尾的行。为了降低由于未加反斜杠的新行或本来应该作为数据的回车符而导致的错误风险，如果输入中的行尾不完全一样， COPY FROM 将抱怨。

CSV Format

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

每条记录中的值由 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 格式中的特殊字符，所以 \. ，数据结束标记，也可以作为数据值出现。为了避免任何误解，如果 \. 数据值显示为在一行中单独存在的条目，那么它将在输出时自动加引号，如果输入时加了引号，则不会将其解释为数据结束标记。如果您正在加载由具有单个不带引号的列并且可能带有 \. 值的其他应用程序创建的文件，则可能需要在输入文件中引用该值。

Note

在 CSV 格式中，所有字符都十分重要。带引号的值周围有空格或除 DELIMITER 以外的任何字符，将包含这些字符。如果您从将 CSV 行填充空白以形成固定宽度的系统导入数据，则这可能会导致错误。如果出现这种情况，则在将数据导入 PostgreSQL 之前，您可能需要预处理 CSV 文件以删除尾随空白。

Note

CSV 格式将识别并创建带有内嵌回车和换行符的引号值 CSV 文件。因此，这些文件不像文本文件那样每行都是一个表行。

Note

许多程序生成稀奇古怪甚至不合常理的 CSV 文件，因此，文件格式更像是一种惯例，而不是一个标准。因此，您可能会遇到一些不能使用这种机制导入的文件，而且 COPY 可能会生成其他程序无法处理的文件。

Binary Format

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

binary 文件格式由文件头、包含行数据的零个或多个元组以及文件尾部组成。头文件和数据按照网络字节顺序排列。

Note

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

File Header

文件头由 15 个字节的固定字段和一个可变长度的头扩展区组成。固定字段如下：

Signature
- 11 字节序列 PGCOPY\n\377\r\n\0 — 请注意，零字节是签名所需的一部分。（该签名旨在让您能够轻松识别出已被非 8 位干净传输弄乱的文件。签名将被换行翻译过滤器更改，删除零字节、删除高位或奇偶校验变化所更改。）
Flags field
- 32 位整数位掩码，表示文件格式的重要方面。位从 0（LSB）编号到 31（MSB）。请注意，此字段按照网络字节顺序（最高有效字节在前）存储，就像文件格式中使用的所有整数字段一样。第 16-31 位保留用于表示文件格式的关键问题；如果读取器发现此范围内的位设置存在异常情况，则它应中止。第 0-15 位保留用于发出向后兼容的格式问题信号；如果读取器发现此范围内的位设置存在异常情况，则应直接忽略。目前仅定义了一个标志位，其余位都必须为零：
Header extension area length
- 32 位整数，不含头文件自身的剩余部分的长度，以字节为单位。目前，此值为零，第一个元组紧跟其后。将来对格式的更改可能会允许头文件中存在其他数据。读取器应静默跳过其不知道如何处理的任何头扩展数据。
Bit 16
- 如果为 1，则数据中包含 OID；如果为 0，则不包含。PostgreSQL 中不再支持 Oid 系统列，但格式中仍包含此指示符。

头扩展区本来应该包含一个自识别块序列。标志字段并非旨在告诉读取器扩展区中有什么内容。头扩展内容的具体设计留待将来的发行版。

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

Tuples

每个元组都以一个 16 位整数计数开始，表示元组中的字段数。（目前，表中的所有元组都具有相同计数，但这可能并不总是如此。）然后，对于元组中的每个字段重复执行以下操作：一个 32 位长度单词，后跟该数量的字段数据字节。（长度单词不包括它本身，可以为零。）作为特例，-1 表示一个 NULL 字段值。在 NULL 情况下，不会出现任何值字节。

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

目前，二进制格式文件中的所有数据值都被假定为二进制格式（格式代码为 1）。预期将来的某个扩展可以添加一个头字段，以便指定每列格式代码。

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

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

File Trailer

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

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

Examples

下面的示例将一个表复制到客户端，使用垂直线 ( | ) 作为字段分隔符：

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 过滤之后显示。表有 3 列；第一列的类型是 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

Compatibility

SQL 标准中没有 COPY 语句。

以下语法曾在 PostgreSQL 版本 9.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 选项的参数。

以下语法曾在 PostgreSQL 版本 7.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' ]

Postgresql 中文操作指南

COPY

Synopsis

Description

Parameters

Note

Outputs

Note

Notes

File Formats

Text Format

CSV Format

Note

Note

Note

Binary Format

Note

File Header

Tuples

File Trailer

Examples

Compatibility

See Also