COPY · PostgreSQL 中文文档 9.3

# COPY ## Name 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_ [, ...] ) ENCODING '_encoding_name_' ``` ## 描述 `COPY`在PostgreSQL表和文件之间交换数据。 `COPY TO`把一个表的所有内容都拷贝到一个文件，而`COPY FROM`从一个文件里拷贝数据到一个表里(把数据附加到表中已经存在的内容里)。 `COPY TO`还能拷贝`SELECT`查询的结果。如果声明了一个字段列表，`COPY`将只在文件和表之间拷贝已声明字段的数据。如果表中有任何不在字段列表里的字段，那么`COPY FROM`将为那些字段插入缺省值。带文件名的`COPY`指示PostgreSQL服务器直接从文件中读写数据。如果声明了文件名，那么服务器必须可以访问该文件，而且文件名必须从服务器的角度声明。如果使用了`PROGRAM`选项，则服务器会从指定的这个程序进行输入或是写入该程序作为输出。如果使用了`STDIN` 或`STDOUT`选项，那么数据将通过客户端和服务器之间的连接来传输。 ## 参数 `_table_name_` 现存表的名字(可以有模式修饰)。。 `_column_name_` 可选的待拷贝字段列表。如果没有声明字段列表，那么将使用所有字段。 `_query_` 一个必须用圆括弧包围的[SELECT](#calibre_link-104)或[VALUES](#calibre_link-106)命令，其结果将被拷贝。 `_filename_` 输入或输出文件的路径名。输入文件名可以是绝对或是相对的路径，但输出文件名必须是绝对路径。 Windows用户可能需要使用`E''`字符串和双反斜线作为路径名称。 `PROGRAM` 需执行的程序名。在`COPY FROM`命令中，输入是从程序的标准输出中读取，而在`COPY TO`中，命令的输出会作为程序的标准输入。注意，程序一般是在命令行界面下执行，当用户需要传递一些变量给程序时，如果这些变量的来源不是可靠的，用户必须小心过滤处理那些对命令行界面来说是有特殊意义的字符。基于安全的原因，最好是使用固定的命令字符串，或者至少是应避免直接使用用户输入（应先过滤特殊字符）。 `STDIN` 声明输入是来自客户端应用。 `STDOUT` 声明输入将写入客户端应用。 `_boolean_` 声明用户所选的选项是否应该被开启或者关闭。您可以写`TRUE`、 `ON`或`1`来启用这个选项，并且用`FALSE`、`OFF`或`0`来关闭它。 `_boolean_`值也可以被省略，此时系统使用缺省值`TRUE`。 `FORMAT` 选择被读或者写的数据格式：`text`、`csv`（逗号分隔值），或者`binary`。默认是`text`。 `OIDS` 声明为每行记录都拷贝内部对象标识(OID)。（如果为一个`_query_`拷贝或者没有`OIDS`的表声明了OIDS选项，则抛出一个错误。） `FREEZE` 请求拷贝那些已冻结的数据，就类似使用`VACUUM FREEZE`的效果。这主要于用于初始化时加载数据时的性能考虑。仅在表记录初始创建或是在当前子事务中被清理的记录会补冻结，没有游标会打开，事务中也没有数据快照。注意此时其他的事务会立刻看见刚加载的数据。这不符合MVCC正常的可见性规则，用户应注意这可能带来的潜在问题。 `DELIMITER` 指定分隔每一行记录中的列的字符。默认是文本格式的制表符，`CSV`格式的逗号。必须有一个独立的一字节的字符。在使用`binary`格式时这个选项是不允许的。 `NULL` 声明代表一个空值的字符串。默认是文本格式的`\N`，`CSV`格式的一个未被引用的空字符串。即使是文本格式您可能也更偏向于空串，例如您不想从空字符串中区分空值。在使用`binary`格式时这个选项是不允许的。 > **Note:** 在使用`COPY FROM`的时候，任何匹配这个字符串的字符串将被存储为NULL值，所以你应该确保你用的字符串和`COPY TO`相同。 `HEADER` 声明文件包含一个带有文件中每列名称的标题行。在输出时，第一行包含表中的列名，在输入时，第一行是被忽略的。该选项仅仅在使用`CSV`格式时是允许的。 `QUOTE` 指定引用数据的引用字符。默认的是双引号。这一定是一个1字节的字符。该选项仅仅在使用`CSV`格式时允许。 `ESCAPE` 声明应该出现在一个匹配`QUOTE`值的数据字符之前的字符。默认与`QUOTE`值相同（所以若它出现在数据中，则引用字符是翻一倍）。这一定是一个1字节的字符。该选项只有在使用`CSV`格式时允许。 `FORCE_QUOTE` 强制引用在每个指定列的所有非`NULL`值。`NULL`从不被引用。如果声明了`*`，非`NULL`值将在所有列中被引用。这个选项仅仅在`COPY TO`中并且仅仅在使用`CSV`格式时允许。 `FORCE_NOT_NULL` 默认情况下空字符串是空的，这意味着空值将会被读作长度为零的字符串而不是空值，即使当他们不被引用。这个选项仅仅在`COPY FROM`中并且仅仅在使用`CSV`格式时允许。 `ENCODING` 声明文件的编码集是`_encoding_name_`。如果这个选项省略，则系统使用当前的用户编码集。阅读下面的注意事项以了解更多内容。 ## Outputs 当`COPY`命令执行成功后，会在屏幕上显示 ``` COPY _count_ ``` 式样内容，这里`_count_`是已拷贝成功的记录数。 ## 注意 `COPY`只能用于表，不能用于视图。当然也可以用于`COPY (SELECT * FROM` `_viewname_`) TO ... `COPY`仅仅处理已指定的特定表；它将不复制数据到子表或从子表中复制数据。因此比如`COPY` `_table_` TO显示与`SELECT * FROM ONLY` `_table_`相同的数据。但是`COPY (SELECT * FROM` `_table_`) TO ...可以用于转储在继承层次结构的所有数据。你对任何要`COPY TO`出来的数据必须有查询的权限，对任何要`COPY FROM`入数据的表必须有插入权限。对列在命令中的字段拥有列权限也是必须的。 `COPY`命令里面的文件必须是由服务器直接读或写的文件，而不是由客户端应用读写。因此，它们必须位于数据库服务器上或者可以被数据库服务器所访问，而不是客户端程序。它们必须被运行PostgreSQL服务器的用户可读或写，而不是客户端程序。由`PROGRAM`选项指定的命令必须是由服务器来执行的，而不是客户端程序，必须是由PostgreSQL所属的用户。 `COPY`在指定一个程序或是命令时只允许数据库超级用户来执行，因为它允许读写任意服务器有权限访问的文件。不要混淆`COPY`和 psql应用程序中的`[\copy](#calibre_link-1638)`指令。 `\copy`调用`COPY FROM STDIN`或`COPY TO STDOUT`，然后把数据抓取/存储到一个psql客户端可以访问的文件中。因此，使用`\copy`的时候，文件访问权限是由客户端应用程序而不是服务器端决定的。建议在`COPY`里的文件名字总是使用绝对路径。在`COPY TO`的时候是由服务器强制进行的，但是对于`COPY FROM`，你的确可从一个相对路径的文件里读取。该路径将解释为相对于服务器的工作目录(通常是数据目录)，而不是客户端的工作目录。执行一个`PROGRAM`选项指定的命令有可能还会受到操作系统的存取权限控制，如在SELinux下。 `COPY FROM`在执行时会触发目标表上所有触发器和检查约束。不过，不会执行规则。 `COPY`输入和输出会被`DateStyle`参数影响。为了和其它PostgreSQL不同服务器间进行数据转移(它们可能是非缺省`DateStyle`设置)，应该在使用`COPY TO`前把`DateStyle`参数值设置为`ISO`。另外也建议在导出数据时，不要将`IntervalStyle`参数设置为`sql_standard`。因为负的区间值可能会被对`IntervalStyle`有不同设置的服务器误解。输入数据通过`ENCODING`参数或是当前客户端编码来解译，输出数据也是通过`ENCODING`参数或是为当前客户端的编码来编码，即使数据不经过客户端的，仍会通过服务器直接将数据从文件中读出或者写入到文件中去。 `COPY`在第一个错误处停下来。这些在`COPY TO`中不应该导致问题，但在`COPY FROM`时目标表会已经接收到早先的行，这些行将不可见或不可访问，但是仍然会占据磁盘空间。如果你碰巧拷贝大量数据文件的话，这些东西积累起来可能会占据相当大的磁盘空间。你可以调用`VACUUM`来恢复那些磁盘空间。 ## 文件格式 ### 文本格式当使用`text`格式时，读写的文件是一个文本文件，每行代表表中一个行。行中的列(字段)用分隔符分开。字段值本身是由与每个字段类型相关的输出函数生成的字符串，或者是输入函数可接受的字符串。数据中使用特定的NULL字符串表示那些值为NULL的字段。如果输入文件的任意行包含比预期多或者少的字段，那么`COPY FROM`将抛出一个错误。如果声明了`OIDS`选项，那么OID将作为第一个字段读写，放在所有用户字段前面。数据的结束可以用一个只包含反斜杠和句点(`\.`)的行表示。如果从文件中读取数据，那么数据结束的标记是不必要的，因为文件结束符可以起到相同的作用；但是在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_` | 反斜杠后面跟着一到三个八进制数，表示ASCII值为该数的字符 | | `\x``_digits_` | 反斜杠`\x`后面跟着一个或两个十六进制位声明指定数值编码的字符 | 目前，`COPY TO`绝不会发出一个八进制或者十六进制反斜杠序列，但是它的确使用了上面列出的其它字符用于控制字符。任何其他未在上表中提及的斜字符将会用来表示其本身。然而，也要注意不必要的情况添加反斜杠。因为这可能意外地生成一个匹配数据结束标记(`\.`)或者空字符串 (默认为`\N`）的字符串。这些字符串将在任何其他反斜杠处理做完之前确认。强烈建议产生`COPY`数据的应用程序将数据换行符和回车分别转换为 `\n`和`\r`序列。目前，可以由反斜杠和回车代表一个数据回车，并且由反斜杠和换行符代表一个数据换行。然而，这些表示法在将来的版本中可能无法接受。`COPY`文件在不同操作系统之间转移时，它们也非常容易被误解读，（例如：从Unix 系统移到Windows系统，或者反过来）。 `COPY TO`将在每行的结尾用一个Unix风格的换行符("`\n`")。运行在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`是写做一个无引号包围的空字符串，而一个空字符串数值写做双引号包围(`""`)。读取数值也遵循类似的规则。你可以使用`FORCE_NOT_NULL`来避免为特定字段进行`NULL`比较。因为对于`CSV`格式而言，反斜杠不是特殊字符，数据的结束标志(`\.`) 可以作为数据值出现。为了避免任何可能的歧意，一个单独的`\.`数据值在输出中将被自动使用引号包围；在输入中，如果被引号界定，那么将不会当作数据结束标志。如果你要加载其它程序创建的、有未用引号界定字段的文件，并且其中含有`\.`值，你就必须用引号进行界定。 > **Note:** 在`CSV`模式下，所有字符都是有效的。一个被空白包围的引号界定数值，或者任何非`DELIMITER`字符，都会被包含这些字符。如果你给`CSV`行填充空白的系统里导入数据到定长字段，那么可能会导致错误。如果出现这种情况，你可能需要先处理一下`CSV`文件，删除结尾空白，然后再向PostgreSQL里导入数据。 > **Note:** CSV格式可以识别和生成引号包围的回车和换行的CSV文件。因此这些文件并不像文本模式的文件那样严格地每条记录一行。 > **Note:** 许多程序生成奇怪的并且有时候不正确的CSV文件，所以这个文件格式更像一种惯用格式，而不是一种标准。因此你可能碰到一些不能使用这个机制输入的文件，而`COPY`也可能生成一些其它程序不能处理的文件。 ### 二进制格式 `binary`形式的选项会使得所有的数据被存储/读作二进制格式而不是文本。这比文本和`CSV`格式的要快一些，但是一个二进制格式文件在机器架构和PostgreSQL版本之间的可移植性比较差。另外，二进制格式是对数据类型有一定要求的；例如，不能从`smallint`列中输出二进制数据并将二进制数据读入`integer`列，尽管在文本格式下那会运行良好。 `binary`文件格式包含一个文件头，0或更多包含行数据的元组，以及一个文件尾。头和数据按照网络字节顺序。 > **Note:** 7.4版本之前的PostgreSQL版本使用的是不同的二进制文件格式。 #### 文件头文件头由15个字节的固定域组成，后面跟着一个变长的头扩展区。固定域是：签名 11字节的序列`PGCOPY\n\377\r\n\0`—，请注意字节零是签名必须的一部分。（使用这个签名是为了能够很容易看出文件是否已经被一个非8位安全的转换器给破坏了。这个签名会被行尾转换过滤器、删除字节零、删除高位、奇偶变化而改变。）标志域 32位整数掩码表示该文件格式的重要方面。位是从 0(LSB)到 31(MSB) 编码的，请注意这个域是以网络字节顺序存储的(高位在前)，后继的整数都是如此。位16-31是保留用做关键文件格式信息的；如果阅读器发现一个不认识的位出现在这个范围内，那么它应该退出。位0-15都保留为标志向后兼容的格式使用；阅读器可以忽略这个范围内的不认识的位。目前只定义了一个标志位，而其它的必须是零： Bit 16 如果为1，那么在数据中包括了OIDS；如果为0，则没有。头扩展范围长度 32位整数，以字节计的头剩余长度，不包括自身。目前，它是零，后面紧跟第一条记录行。对该格式的更多修改都将允许额外的数据出现在头中。阅读器应该忽略任何它不知道该如何处理的头扩展数据。头扩展数据用来保存自定义数据序列块。这个标志域无意告诉阅读器扩展区的内容是什么。头扩展的具体设计内容留给以后的版本使用。这样设计就允许向后兼容的头扩展(增加头扩展块或设置低位序标志位)以及非向后兼容的修改(设置高位标志位以标识这样的修改，并且根据需要向扩展区域增加支持数据)。 #### 行记录每条行都以一个16位整数计数开头，该计数是行中字段的数目(目前，在一个表里的每行都有相同的计数，但可能不会永远这样)。然后后面不断出现行中的各个字段，字段先是一个32位的长度字，后面跟着很多的字段数据。长度字并不包括自己，并且可以为零。一个特例是：-1表示一个NULL字段值。在NULL情况下，后面不会跟着数值字节。在数据域之间没有对齐填充或者任何其它额外的数据。目前，一个二进制格式文件里的所有数据值都假设是二进制格式的(格式代码为一)。预计将来的扩展可能增加一个头域，允许为每个字段声明格式代码。为了判断实际行数据的正确二进制格式，你应该阅读PostgreSQL源代码，特别是该字段数据类型的`*send`和`*recv`函数 (这些函数可以在源代码的`src/backend/utils/adt/`目录找到)。如果在文件中包括了OIDs，那么该OID域立即跟在字段计数字后面。它是一个普通的字段，只不过它没有包括在字段计数。但它包括长度字，这样就允许方便的处理4字节和8字节的OIDs，并且如果某个家伙允许OIDs是可选的话，那么还可以把OIDs显示成空。 #### 文件尾文件尾包括保存着-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 ] [ OIDS ] [ DELIMITER [ AS ] '_delimiter_' ] [ NULL [ AS ] '_null string_' ] [ CSV [ HEADER ] [ QUOTE [ AS ] '_quote_' ] [ ESCAPE [ AS ] '_escape_' ] [ FORCE NOT NULL _column_name_ [, ...] ] ] ] COPY { _table_name_ [ ( _column_name_ [, ...] ) ] | ( _query_ ) } TO { '_filename_' | STDOUT } [ [ WITH ] [ BINARY ] [ OIDS ] [ DELIMITER [ AS ] '_delimiter_' ] [ NULL [ AS ] '_null string_' ] [ CSV [ HEADER ] [ QUOTE [ AS ] '_quote_' ] [ ESCAPE [ AS ] '_escape_' ] [ FORCE QUOTE { _column_name_ [, ...] | * } ] ] ] ``` 请注意：在这个语法中，`BINARY`和`CSV`是作为独立的关键字，而不是作为`FORMAT`选项的一个参数。 PostgreSQL7.3以前使用下面的语法，现在仍然支持： ``` COPY [ BINARY ] _table_name_ [ WITH OIDS ] FROM { '_filename_' | STDIN } [ [USING] DELIMITERS '_delimiter_' ] [ WITH NULL AS '_null string_' ] COPY [ BINARY ] _table_name_ [ WITH OIDS ] TO { '_filename_' | STDOUT } [ [USING] DELIMITERS '_delimiter_' ] [ WITH NULL AS '_null string_' ] ```