Skip to Content
参考手册SQL命令参考COPY

COPY

在文件和表之间复制数据。

语法

COPY <table_name> [(<column_name> [, ...])] 
     FROM {'<file_name>' | STDIN}
     [ [ WITH ] ( <option> [, ...] ) ]
 
COPY { <table_name> [(<column_name> [, ...])] | (<query>)} 
     TO STDOUT
     [ [ WITH ] ( <option> [, ...] ) ]
 
其中 <option> 可以是以下任何一项:
 
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>'       
FILL MISSING FIELDS
LOG ERRORS [ SEGMENT REJECT LIMIT <count> [ ROWS | PERCENT ] ]

描述

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

如果指定了列列表，COPY 只会将这些列的数据复制到文件或从文件中复制出来。对于表中未包含在列表中的列，COPY ... FROM 将为它们插入默认值。

输出

成功完成后，COPY 命令返回一个命令标签，其中 <count> 是复制的行数：

COPY <count>

如果在单行错误隔离模式下运行 COPY ... FROM 命令，如果有任何行因格式错误而未加载，将返回以下通知消息，其中 <count> 为被拒绝的行数：

NOTICE: Rejected <count> badly formatted rows.

参数

  • <table_name>

    要复制的表的名称，支持使用 Schema 名称进行限定。

  • <column_name>

    要复制的列的可选列表。如果没有指定该参数，将复制表的所有列。

    在文本格式下复制时，默认情况下，bytea 类型的列中的一行数据可以达到 256 MB。

  • <query>

    要复制的 SELECT 或 VALUES 命令的结果。注意，查询需要用括号括起来。

  • <file_name>

    输入或输出文件的路径名。目前，只支持 S3 文件。

  • STDIN

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

  • STDOUT

    指定输出到客户端应用。

  • <boolean>

    指定所选选项应该打开还是关闭。打开选项，设置为 TRUE、ON 或 1；关闭选项，设置为 FALSE、OFF 或 0。如不填写该值，则默认为 TRUE。

  • FORMAT

    选择要读取或写入的数据格式：text、csv 或 binary。默认为 text。

  • OIDS

    指定复制每行的 OID。如果对没有 OID 的表或在复制查询的情况下指定 OIDS，将引发错误。

  • FREEZE

    请求在复制过程中冻结数据的行，效果等同于执行了 VACUUM FREEZE 之后的操作。FREEZE 是用于初始数据加载的性能选项。只有在加载的表在当前子事务中被创建或截断、没有打开的游标，并且此事务没有持有任何旧的快照时，行才会被冻结。

    注意，数据一旦加载成功即对所有其他会话可见。这违背 MVCC 可见性的一般原则。设置该选项时，用户需要关注此设置可能带来的潜在问题。

  • DELIMITER

    指定分隔符，用于分割文件中各行里的列。默认情况下，text 为制表符，CSV 为英文逗号。分隔符必须是单个一字节字符。binary 不支持该选项。

  • NULL

    指定表示空值的字符串。默认情况下，text 为 \N（反斜杠 + N），CSV 为未引用的空字符串。在 text，你也可以将空值设置为空字符串，从而不用对空值和空字符串进行区分。binary 文件不支持该选项。

?>提示
在使用 COPY ... FROM 时，任何与此字符串匹配的数据项都将被存储为 null 值，因此你应确保使用与 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。在默认情况下，空字符串为空时，此选项会将加引号的空字符串转换为 NULL。此选项仅在使用 COPY FROM 且格式为 CSV 时可用。

  • ENCODING

    指定文件以 <encoding_name> 进行编码。如果省略此选项，将使用当前的客户端编码。更多信息，参考 使用注意事项。

  • NEWLINE

    指定你的数据文件中使用的换行符 - LF（换行，0x0A），CR（回车，0x0D），或 CRLF（回车加换行，0x0D 0x0A）。

  • CSV

    选择 CSV 模式。

  • FILL MISSING FIELDS

    在 COPY ... FROM 中指定 FILL MISSING FIELDS 时，无论是 TEXT 还是 CSV 格式，如果某行数据在末尾缺少字段值，则会将缺失的字段值设置为 NULL（而不是报告错误）。然而，空行、具有 NOT NULL 约束的字段以及行末的分隔符会导致报告错误。

文件格式

COPY 支持的文件格式包括：

文本格式

当使用 text 格式时，读写的数据是一个文本文件，文件中的每行对应表中的一行。行中的列由 delimiter_character 进行分隔（默认为制表符）。列值本身是由每个属性的数据类型的输出函数生成的字符串或可以被输入函数接受的字符串。指定的空字符串用于替代 null 列。如果输入文件中的任何一行包含的列数多于或少于预期的列数，COPY ... FROM 将报错。如果指定了 OIDS，OID 将作为第一列读写，位于用户数据列之前。

数据文件中有两个保留字符在 COPY 中有特殊含义：

  • 指定的分隔符字符（默认是制表符），用于在数据文件中分隔字段。

  • UNIX 风格的换行符（\n 或 0x0a），用于指定数据文件中的新行。强烈建议生成 COPY 数据的应用程序将数据中的换行符转换为 UNIX 风格的换行符，而不是 Microsoft Windows 风格的回车换行符（\r\n 或 0x0a 0x0d）。

如果数据中包含这些字符中的任何一个，则必须对字符进行转义，以便 COPY 将其视为数据，而不是字段分隔符或新行。

默认情况下，文本格式的文件使用反斜杠（\）作为转义字符，而 csv 格式的文件使用双引号（“）作为转义字符。如果想使用其他转义字符，可以使用 ESCAPE AS 子句指定。确保选择的转义字符在数据文件中不作为实际数据值使用。还可以通过使用 ESCAPE 'OFF' 在文本格式的文件中禁用转义。

例如，假设有一个包含三列的表，需要使用 COPY 加载以下三个字段：

  • percentage sign = %
  • vertical bar = |
  • backslash = \

你指定的 <delimiter_character> 是 |（竖线），你指定的转义字符是 *（星号）。你的数据文件中的格式化行将如下所示：

percentage sign = % | vertical bar = *| | backslash = \

请注意，数据中的竖线字符已使用星号（*）作为转义符进行了转义。同样，注意我们不需要对反斜杠进行转义，因为我们使用了一个替代的转义字符。

以下字符如果出现在列值中，必须前面加上转义字符：转义字符本身、换行符、回车符和当前的分隔符字符。您可以使用 ESCAPE AS 子句指定不同的转义字符。

CSV 格式

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

每条记录中的值由 DELIMITER 字符分隔。如果值包含分隔符字符、QUOTE 字符、ESCAPE 字符（默认是双引号）、NULL 字符串、回车符或换行符，则整个值会由 QUOTE 字符前后包围。也可以使用 FORCE_QUOTE 来强制在输出特定列的非 NULL 值时加引号。

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

由于反斜杠在 CSV 格式中不是特殊字符，\.（数据结束标记）也可能作为数据值出现。为了避免误解，作为一行唯一条目的 \. 数据值在输出时会自动加引号，而在输入时，如果加了引号，则不会被解释为数据结束标记。如果您正在加载由另一个应用程序创建的文件，其中有一个未加引号的列且可能包含 \. 值，可能需要在输入文件中为该值加引号。

?>提示
在 CSV 格式中，所有字符都有意义。如果由引号包围的值的两侧有空白字符或任何非 DELIMITER 的字符，这些字符也会被包含在内。这意味着，如果从一个使用空白字符填充 CSV 行至固定宽度的系统中导入数据，可能会导致错误。如果遇到这种情况，需要在将数据导入 MAXIR 之前，预处理 CSV 文件以去除末尾的空白字符。

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

?>提示
许多程序生成的 CSV 文件很奇怪，有时甚至是反常的，所以文件格式更像是一个约定，而不是一个标准。因此，你可能会遇到一些无法使用此机制导入的文件，而 COPY 可能会生成其他程序无法处理的文件。

二进制格式

binary 格式选项会将所有数据以二进制格式进行存储或读取，而不是以文本格式。虽然它的速度比文本和 CSV 格式更快，但二进制格式的文件在不同机器架构和 MAXIR 版本之间的可移植性较差。此外，二进制格式对数据类型非常敏感。例如，在文本格式中可以将 smallint 列的数据输出并读取到 integer 列中，但在二进制格式下则不可行。

二进制文件格式由文件头、若干个包含行数据的元组，以及文件尾组成。文件头和数据都采用网络字节序（大端序）。

  • 文件头 — 文件头由 15 字节的固定字段和一个可变长度的扩展区域组成。固定字段包括：

    • 签名 — 11字节序列 PGCOPY\n\377\r\n\0，注意零字节是签名的一部分。（这个签名用于识别那些在传输过程中被破坏的文件。任何行尾转换、丢失的零字节或高位、奇偶校验变化都会导致签名被修改。）

    • 标志字段 — 32 位整数，用作位掩码来标识文件格式的关键属性。位编号从 0（最低位）到 31（最高位）。注意，该字段以及文件格式中所有的整数字段都以网络字节序（最高位字节优先）进行存储。位 16 到 31 是保留位，表示关键的文件格式问题；如果发现这些位有非常规设置，读取器应停止读取。位 0 到 15 则保留用于向后兼容的格式问题；读取器应忽略这些位的任何非常规设置。目前只定义了一个标志，其他位必须为零（位16：如果数据包含 OID，则为 1，否则为 0）。

    • 扩展区域长度 — 32 位整数，表示文件头其余部分的长度（不包括自身），单位为字节。目前，这个值为零，第一个元组紧随其后。将来可能会对格式进行更新，允许文件头中包含更多数据。读取器应跳过任何它无法识别的扩展数据。扩展区域的设计是为包含一系列自我标识的数据块提供空间。标志字段并不负责告知读取器扩展区域的具体内容，这部分设计将留待后续版本实现。

  • 元组 — 每个元组以一个 16 位整数开始，表示元组中的字段数量。（目前，表中的所有元组字段数量相同，但将来可能会有所不同。）接下来是每个字段的 32 位长度字段，紧跟该长度的字段数据。（长度字段不包括其自身，且可以为零。）特殊情况下，-1 表示字段值为 NULL。在这种情况下，字段数据不会跟随。

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

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

    如果文件中包含 OID 字段，该字段会紧跟在字段计数之后。它作为一个常规字段存在，但不计入字段数量。特别地，它也有一个长度字段，这样可以更方便地处理 4 字节和 8 字节 OID，甚至在必要时将 OID 显示为 NULL。

  • 文件尾 — 文件尾由一个包含 -1 的 16 位整数构成。它与元组的字段计数字易于区分。如果字段计数字既不是 -1 也不是预期的列数，读取器应报告错误。这样可以额外防止数据同步错误。

使用注意事项

COPY 只能用于普通表，不能用于外部表或视图。然而，您可以使用 COPY (SELECT * FROM <view_name>) TO ... 来实现类似的效果。

COPY 仅处理指定的表，不会从子表中复制数据或向子表中写入数据。因此，举例来说，COPY <table> TO 显示的数据与 SELECT * FROM ONLY <table_name> 相同。但可以使用 COPY (SELECT * FROM <table_name>) TO ... 来导出整个继承层次结构中的所有数据。

BINARY 关键字会使所有数据以 binary 格式存储或读取，而非 text 格式。二进制格式比普通文本模式稍快，但二进制格式文件在不同的机器架构和 MAXIR 版本之间的可移植性较差。此外，如果数据为二进制格式，则无法在单行错误隔离模式下运行 COPY FROM。

使用 COPY TO 时，必须对读取的表具有 SELECT 权限，并且对 COPY FROM 插入数据的表具有 INSERT 权限。对于命令中列出的列，则只需要拥有相应列的权限。

在 COPY 命令中命名的文件直接由数据库服务器读取或写入，而不是由客户端应用。因此，它们必须位于或者可以访问 MAXIR 协调器主机机器，而不是客户端。它们必须可以被 MAXIR 系统用户（服务器以用户 ID 运行）访问和读取或写入，而不是客户端。只有数据库超级用户被允许在 COPY 中命名文件，因为这允许读取或写入服务器有权限访问的任何文件。

COPY FROM 会触发目标表上的任何触发器和检查约束。但是，它不会触发重写规则。请注意，在当前版本中，违反约束条件不会在单行错误隔离模式下进行评估。

COPY 的输入和输出会受到 DateStyle 设置的影响。为了确保数据在其他可能使用非默认 DateStyle 设置的 MAXIR 安装环境中的可移植性，应该在使用 COPY TO 之前将 DateStyle 设置为 ISO。此外，建议避免在 IntervalStyle 设置为 sql_standard 时转储数据，因为负间隔值在使用不同 IntervalStyle 设置的服务器上可能会被误解。

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

在文本模式下从文件复制 XML 数据时，服务器配置参数 xmloption 会影响被复制的 XML 数据的验证。如果参数值为 content（默认值），则 XML 数据将作为 XML 内容片段进行验证。如果参数值为 document，则 XML 数据将作为 XML 文档进行验证。如果 XML 数据无效，COPY 将返回错误。

默认情况下，COPY 在遇到第一个错误时停止操作。对于 COPY TO 来说，这通常不会引发问题，但在 COPY FROM 中，目标表可能已经接收了一些先前的行。这些行不会被显示或访问，但它们仍然占用磁盘空间。如果错误发生在较大的 COPY FROM 操作中，这可能导致大量磁盘空间的浪费。在这种情况下，您可以运行 VACUUM 来回收浪费的空间。另一种选择是使用单行错误隔离模式，在加载有效数据的同时过滤掉错误行。

FORCE_NULL 和 FORCE_NOT_NULL 可以同时用于同一列。这样会将带引号的 null 字符串转换为 null 值，而未加引号的 null 字符串则转换为空字符串。

当数仓用户运行 COPY 命令时，该命令可以通过资源队列进行控制。资源队列必须配置 ACTIVE_STATEMENTS 参数，该参数指定分配给该队列的角色可以运行的最大查询数量。MAXIR 不对 COPY 命令应用成本值或内存值，因此仅设置了成本或内存限制的资源队列不会影响 COPY 命令的运行。

示例

使用竖线（|）作为字段分隔符，将表复制到客户端：

COPY country TO STDOUT (DELIMITER '|');

从 S3 文件中复制数据到 country 表：

COPY nation FROM 's3://s3.ap-southeast-1.amazonaws.com/******/tpch/100m/nation.tbl' 
    ACCESS_KEY_ID '******'
    SECRET_ACCESS_KEY '******'
    CSV DELIMITER '|'
    HEADER;

SQL 标准兼容性

SQL 标准中没有 COPY 语句。

COMMIT