COPY

来自 PostgreSQL 维基
跳转到导航跳转到搜索

COPY 是 PostgreSQL 中的数据加载方法。PostgreSQL 的 COPY 有两种不同的变体，**COPY** 和 **\COPY**：COPY 是服务器端的，\COPY 是客户端的。

COPY 将由 PostgreSQL 后端（用户 "postgres"）运行。后端用户需要有读取和写入数据文件的权限才能从/向该文件复制数据。使用 COPY 时，需要使用绝对路径。另一方面，\COPY 在当前的 $USER 和该用户的环境下运行。而且 \COPY 可以处理相对路径。如果 psql 的 \COPY 可以处理你的需求，那么它更容易使用。

无论使用哪种方法，都需要对要复制的表有插入/更新或选择权限。

语法

以下是 8.3 客户端返回的 COPY 语法

db=# \h COPY
Command:     COPY
Description: copy data between a file and a table
Syntax:
COPY tablename [ ( column [, ...] ) ]
    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 [, ...] ]

COPY { tablename [ ( column [, ...] ) ] | ( 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 [, ...] ]

\COPY 的语法略有不同：(a) 作为 psql 命令，它不以分号结尾 (b) 文件路径相对于当前工作目录。

许多选项可以放在 **CSV** 之后，例如，**WITH CSV NULL AS** 是完全允许的。

导入注意事项

COPY 并不十分智能或巧妙，事实上它很笨拙也很简单。以下是一些你在使用 COPY 时可能会遇到的问题

从包含多余列的源数据复制
假设你从一个包含以下字段的源数据复制数据 (Foo, Bar, Foo+Bar)；在复制数据时，你发现 Foo+Bar 是多余的，因为它是由 Foo 和 Bar 函数计算得出的。PostgreSQL 没有提供在加载时忽略此列的方法 - CSV LOAD 要求馈送中的每个 csv 都必须存在于要加载它的表中。针对此问题的解决方案一直被称为 "ragged csvs"。
嵌入的分隔符
这种情况非常常见，尤其是在用户创建的备忘文本中。COPY 默认情况下使用制表符作为分隔符，但你可以使用 "USING DELIMITERS 'whatever'" 指定其他字符。如果你的数据中包含多余的分隔符字符，COPY 将发现太多字段无法放入你的表中，然后...... 你可以猜到接下来的事情。通常，这会显示为数据类型不匹配，因为 COPY 试图将你的文本字符串塞入日期字段或类似的东西。
反斜杠字符
这会导致问题，因为接下来的分隔符会被转义，不再被识别为分隔符。所有字段都会向下移动一位，结果是 (a) 你的 COPY 由于数据类型不匹配而失败，或者 (b) 你的数据以损坏的形式被静默接受。无论哪种情况，都不是好事。
回车符 (CR)
如果回车符没有被删除，它将出现在表中的最后一个字段中。如果该字段是数字或日期数据类型，你的 COPY 将失败。如果该字段是字符数据类型，你的 COPY 将成功，然后你将不得不绞尽脑汁地弄清楚为什么使用该字段进行比较会产生如此奇怪的结果。一些快速解决方案
  • 如果你是从 ftp 服务器获取的数据文件，你可以使用 "ascii" 传输方法完全避免这种情况。这将自动为你调整行尾。
  • 简单来说，使用一个简单的脚本删除所有回车符：tr -d '\r' < datafile。
  • 使用像GNU recode这样的程序，它只改变用作行分隔符的 CR。相应的命令是 recode /cl datafile。
NULL 混淆
COPY 默认情况下使用 "\N" (反斜杠-N) 表示 NULL。你可以使用以下方法更改此行为：WITH NULL AS 'something_else'. 如果你的数据中包含空字段，我强烈建议使用WITH NULL AS ''. 否则，COPY 将假设空字段表示空字符串，并在遇到第一个空的数字或日期字段时失败。请注意，你不能混合使用 NULL 表示方法！这与关于 NULL 的实现注意事项有关。
数据类型不匹配
你的数据必须与相应的 PostgreSQL 数据类型所要求的格式匹配。例如，如果你的整数使用区域设置特定的命令或句点进行格式化，例如 1,203,327，PostgreSQL 将不会将其强制转换为 int。
速度
虽然 COPY 程序比任何其他导入路径都快得多，但它本身仍然很容易成为导入的瓶颈，而不是服务器。COPY 后端运行时使用 100% 的 CPU，而服务器本身处于空闲状态，这并不罕见。

实现注意事项

COPY 的行为并不对称
  • 如果将数据**COPY**到一个已经包含数据的表中，新数据将被追加。
  • 如果将数据**COPY TO**一个已经包含数据的文件中，现有数据将被覆盖。
NULL 行为反直觉
WITH NULL AS '""'在遇到空引号时不会插入 NULL，任何被引用的内容都非 NULL，而此参数不会绕过该行为。
无法处理约束冲突
如果重复行违反了唯一约束，则不允许重复行。相反，MySQL 可以使用 REPLACE 或 IGNORE 选项。
无法扩展 Pg 转换
数据加载机制依赖于数据是 Pg 数据类型的正式表示形式，或者可以由 Pg 强制转换（例如，可以转换）。但是，目前还没有办法为 Pg 类型添加自定义转换。例如，你不能通过覆盖对 Int 的强制转换来使 '31,337'::int 工作。

有时 \COPY 失败的描述不如 COPY 失败的描述详细。如果 \COPY 失败，请尝试使用服务器端的 COPY，看看是否有助于你排查问题。

其他加载方案

  • pgloader 比内置的 COPY 更好地处理以更灵活的格式加载容易出错的数据，即使它在内部使用 COPY 协议。
  • pg_bulkload 旨在通过跳过一些开销来源来实现比标准 COPY 更高的性能。

外部链接

检索自 "https://wiki.postgresql.ac.cn/index.php?title=COPY&oldid=24667"