logo - 跳到首页

VastbaseE100

基于开源技术的HTAP数据库管理系统。性能优异，稳定可靠，提供诸多专属领域特性。

Menu

COPY

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

语法格式

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 [, ...] ) ]

其中 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'

说明

COPY在Vastbase E100表和标准文件系统文件之间 移动数据。COPY TO把一个表的内容复制 到一个文件，而COPY FROM 则从一个文件复制数据到一个表（把数据追加到表中原有数 据）。COPY TO也能复制一个 SELECT查询的结果。

如果指定了一个列列表，COPY将只把指定列的 数据复制到文件或者从文件复制数据到指定列。如果表中有列不在列列表中， COPY FROM将会为那些列插入默认值。

带一个文件名的COPY指示Vastbase E100服务器直接从一个文件读取 或者写入到一个文件。该文件必须是Vastbase E100用户（运行服务器的用户 ID） 可访问的并且应该以服务器的视角来指定其名称。当指定了 PROGRAM时，服务器执行给定的命令并且从该程序的标准 输出读取或者写入到该程序的标准输入。该程序必须以服务器的视角指定，并且 必须是Vastbase E100用户可执行的。在指定 STDIN或者STDOUT时，数据会通过客 户端和服务器之间的连接传输。

参数说明

  • 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 命令，你必须小心地剥离那些可能对 shell 有特殊意义的特殊 字符。出于安全原因，最好使用一个固定的命令字符串，或者至少避免传递 任何用户输入到其中。

  • STDIN

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

  • STDOUT

    指定输出会去到客户端应用。

  • boolean

    指定选中的选项是应该被关闭还是打开。可以写TRUE、 ON或1来启用选项，写 FALSE、OFF或0禁用它。 boolean值也可以被省略， 那样会假定为TRUE。

  • FORMAT

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

  • OIDS

    指定为每一行复制 OID（如果为一个没有 OID 的表指定了 OIDS或者正在复制的是一个查询，则会出现错误）。

  • FREEZE

    请求复制已经完成了行冻结的数据，就好像在运行 VACUUM FREEZE命令之后复制。这是为了初始 数据载入的性能而设计的。只有被载入表已经在当前子事务中被创建 或截断、该事务中没有游标打开并且该事务没有持有更旧的快照时， 行才会被冻结。目前无法在分区表上执行COPY FREEZE。

    注意一旦成功地载入，所有其他会话将能立即看到该数据。这违背了 普通的 MVCC 可见性规则，指定该选项的用户应该注意这可能会导致 的潜在问题。

  • DELIMITER

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

  • NULL

    指定表示一个空值的字符串。文本格式中默认是 \N（反斜线-N），CSV格式中默认 是一个未加引用的空串。在你不想区分空值和空串的情况下，即使在文本 格式中你也可能更喜欢空串。使用binary格式时不允许这 个选项。

  • HEADER

    指定文件包含标题行，其中有每一列的名称。在输出时，第一行包含 来自表的列名。在输入时，第一行会被忽略。只有使用 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编码。如果省略 这个选项，将使用当前的客户端编码。详见下文的注解。

输出

成功完成后， COPY 命令将返回表单的命令标记

COPY count

count 是复制的行数。

注意

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

注意事项

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命令中提到的文件会被服务器（而不是 客户端应用）直接读取或写入。因此它们必须位于数据库服务器（不是客户 端）的机器上或者是数据库服务器可以访问的。它们必须是Vastbase E100用户（运行服务器的用户 ID）可访问的并且是可读或者可写的。类似地，用PROGRAM 指定的命令也会由服务器（不是客户端应用）直接执行，它也必须是 Vastbase E100用户可以执行的。 只允许数据库超级用户或者授予了默认角色pg_read_server_files、pg_write_server_files及pg_execute_server_program之一的用户COPY一个文件或者命令， 因为它允许读取或者写入服务器有特权访问的任何文件或者运行服务器有特权访问的程序。

不要把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设置的Vastbase E100安装的可移植性，在使用 COPY TO前应该把 DateStyle设置为ISO。避免转储把 IntervalStyle设置为 sql_standard的数据也是一个好主意，因为负的区间值可能会 被具有不同IntervalStyle设置的服务器解释错误。

即使数据会被服务器直接从一个文件读取或者写入一个文件而不通过 客户端，输入数据也会被根据ENCODING选项或者当前 客户端编码解释，并且输出数据会被根据ENCODING或 者当前客户端编码进行编码。

COPY会在第一个错误处停止操作。这在 COPY TO的情况下不会导致问题，但是 在COPY FROM中目标表将已经收到了一 些行。这些行将不会变得可见或者可访问，但是它们仍然占据磁盘空间。 如果在一次大型的复制操作中出现错误，这可能浪费相当可观的磁盘空间。 你可能希望调用VACUUM来恢复被浪费的 空间。

FORCE_NULL和FORCE_NOT_NULL可以被同时 用在同一列上。这会导致把已被引用的空值串转换为空值并且把未引用的空值 串转换为空串。

文件格式

文本格式

在使用text格式时，读取或写入的是一个文本文件， 其中每一行就是表中的一行。一行中的列被定界字符分隔。列值 本身是由输出函数产生的或者是可被输入函数接受的属于每个属性 数据类型的字符串。在为空值的列的位置使用指定的空值串。如果 输入文件的任何行包含比预期更多或者更少的列， COPY FROM将会抛出一个错误。如 果指定了OIDS，在处理用户数据列志气啊， 会从第一列读取 OID 或者把 OID 写入第一列。

数据的结束可以表示为一个只包含反斜线-点号（.）的 单一行。从一个文件读取时，数据结束标记并不是必要的，因为文件结束符就已经足够用了。

反斜线字符（\）可以被用在 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 反斜杠后跟一到三个八进制数字指定带有该数字代码的字符
\x digits 反斜杠 x 后跟一个或两个十六进制数字指定带有该数字代码的字符

当前，COPY TO不会发出一个十进制或十六进制位 反斜线序列，但是它确实把上面列出的其他序列用于那些控制字符。

任何上述表格中没有提到的其他反斜线字符将被当作表示其本身。不过，要注意 增加不必要的反斜线，因为那可能意外地产生一个匹配数据结束标记（ .）或者空值串（默认是\N）的字符串。这些字符串 将在完成任何其他反斜线处理之前被识别。

强烈建议产生COPY数据的应用把数据新行和回车分别 转换为\n和\r序列。当前可以把一个数据回车表示为 一个反斜线和回车，把一个数据新行表示为一个反斜线和新行。不过，未来的发行 可能不会接受这些表示。如果在不同的机器之间（例如从 Unix 到 Windows） 传输COPY文件，它们也很容易受到破坏。

COPY TO将用一个 Unix 风格的新行（ “\n”）终止每一行。运行在 Microsoft Windows 上的服务器则会输出回车/新行（“\r\n”），不过只对 COPY到一个服务器文件这样做。为了做到跨平台一致， COPY TO STDOUT总是发送“\n”而 不管服务器平台是什么。COPY FROM能够处理以 新行、回车或者回车/新行结尾的行。

CSV格式

这种格式选项被用于导入和导出很多其他程序（例如电子表格）使用的逗号 分隔值（CSV）文件格式。不同于 Vastbase E100标准文本格式使用的转义 规则，它产生并且识别一般的 CSV 转义机制。

每个记录中的值用DELIMITER字符分隔。如果值包含 定界符字符、QUOTE字符、NULL字符串、 一个回车或者换行字符，那么整个值会被加上QUOTE字符 作为前缀或者后缀，并且在该值内QUOTE字符或者 ESCAPE字符的任何一次出现之前放上转义字符。在输出 指定列中非NULL值时，还可以使用 FORCE_QUOTE来强制加上引用。

CSV格式没有标准方式来区分NULL值和空字符串。 Vastbase E100的COPY用引用来处理 这种区分工作。NULL被按照NULL参数字符串输出 并且不会被引用，而匹配NULL参数字符串的非NULL 值会被加上引用。例如，使用默认设置时，NULL被写作一个未 被引用的空字符串，而一个空字符串数据值会被写成带双引号（”“）。 值的读取遵循类似的规则。你可以用FORCE_NOT_NULL来防止 对指定列的NULL输入比较。你还可以使用 FORCE_NULL把带引用的空值字符串数据值转换成NULL。

因为反斜线在CSV格式中不是一种特殊字符，数据结束标记 .也可以作为一个数据值出现。为了避免任何解释误会，在 一行上作为孤项出现的.数据值输出时会自动被引用，并且 输入时如果被引用，则不会被解释为数据结束标记。如果正在载入一个由 另一个应用创建的文件并且其中具有一个未被引用的列且可能具有 .值，你可能需要在输入文件中引用该值。

注意

CSV格式中，所有字符都是有意义的。一个被空白或者其他 非 DELIMITER字符围绕的引用值将包括那些字符。在导入 来自用空白填充CSV行到固定长度的系统的数据时，这可能 会导致错误。如果出现这种情况，在导入数据到Vastbase E100之前，你可能需要预处理该 CSV文件以移除拖尾的空白。

注意

CSV格式将识别并生成包含嵌入式回车符和换行符的引用值的CSV文件。因此，文件格式文件不是每个表行严格一行。

注意

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

二进制格式

binary格式选项导致所有数据被以二进制格式 而不是文本格式存储/读取。它比文本和CSV格式要 快一些，但是二进制格式文件在不同的机器架构和Vastbase E100版本之间的可移 植性要差些。还有，二进制格式与数据格式非常相关。例如不能从 一个smallint列中输出二进制数据并且把它读入到一个 integer列中，虽然这样做在文本格式中是可行的。

binary文件格式由一个文件头、零个或者更多个包含 行数据的元组以及一个文件尾构成。头部和数据都以网络字节序表示。

  • 文件头

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

  • 签名

    11字节序列 PGCOPYn377rn0 - 请注意，零字节是签名的必需部分。 （该签名旨在允许轻松识别通过非8位清除传输进行的文件。此签名将由行结束转换过滤器，零字节丢弃，高位丢失或平价变化。）

  • 标志字段

    32位整数位掩码表示文件格式的重要方面。位编号从0（LSB）到31（MSB）。请注意，此字段以网络字节顺序存储（最重要的字节优先），文件格式中使用的所有整数字段也是如此。第16-31位保留用于表示关键文件格式问题;如果读者在此范围内发现意外位，则应该中止。保留0-15位以表示向后兼容的格式问题;读者应该简单地忽略在此范围内设置的任何意外位。目前只定义了一个标志位，其余的必须为零：

    位16

    如果为 1，表示数据中包含 OID；如果为 0，则不包含

  • 标头扩展区域长度

    32位整数， Headers余数的字节长度，不包括self。目前，这是零，并且第一个元组立即跟随。对格式的未来更改可能允许Headers 中存在的其他数据。读者应该静默地跳过它不知道如何处理的任何Headers 扩展数据。 设想 Headers 扩展区域包含一系列自识别块。flags字段不是为了告诉读者扩展区域中的内容。 Headers扩展内容的具体设计留待以后发布。 此设计允许向后兼容的标头添加（添加标头扩展组块或设置低位标志位）和非向后兼容的更改（设置高位标志位以发出此类更改信号，并向扩展添加支持数据）区域，如果需要）。

  • 元组

    每个元组以元组中字段数的16位整数计数开始。 （目前，表中的所有元组都具有相同的计数，但可能并非总是如此。）然后，对元组中的每个字段重复，有一个32位长的字，后跟那么多字节的字段数据。 （长度字不包括其自身，并且可以为零。）作为特殊情况，-1表示NULL字段值。NULL情况下没有值字节。 字段之间没有对齐填充或任何其他额外数据。 目前，假设二进制格式文件中的所有数据值都是二进制格式（格式代码一）。预计未来的扩展可能会添加一个Headers 字段，允许指定每列格式代码。 要确定实际元组数据的适当二进制格式，您应该查阅VastbaseE100源代码，特别是每列的数据类型的 *send 和 *recv 函数（通常这些函数位于源代码分发的 src/backend/utils/adt/ 目录中）。 如果文件中包含OID，则OID字段紧跟字段计数字。这是一个正常的字段，除了它不包括在字段计数中。特别是它有一个长度字 这将允许处理4字节与8字节的OID而不会有太多的痛苦，并且如果有必要，将允许OID显示为null。

  • 文件尾

    文件尾部包含一个包含-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 。所有行在第三列中都具有空值。

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