57 COPY

COPY 是 PostgreSQL 的批量数据导入/导出命令。它以 CSV、文本或二进制格式，在表与文件（或标准输入/输出）之间移动行。与 INSERT 相比，COPY 快得多——有时能快 10–100 倍——因为它跳过了大量逐行开销。要装载大量数据时，优先使用 COPY。

本章要点

• 使用 COPY ... TO 导出，以及使用 COPY ... FROM 导入
• COPY（服务端）与 \copy（客户端）的区别
• CSV 格式以及 HEADER、QUOTE、DELIMITER 选项
• 通过 STDIN / STDOUT 进行流式传输
• 为什么批量装载时 COPY 胜过 INSERT

示例

-- Export to STDOUT in CSV (the rows go to your terminal when run via `psql -f`)
COPY (SELECT id, name FROM fruits ORDER BY id) TO STDOUT WITH (FORMAT csv, HEADER true);

-- Export a whole table
-- (commented because most workflows use the CTAS / pg_dump path instead)
-- COPY fruits TO STDOUT WITH (FORMAT csv, HEADER true);

-- Server-side import: the SERVER must read the path.
-- This is rarely what you want unless you control the server filesystem.
-- COPY fruits FROM '/srv/data/fruits.csv' WITH (FORMAT csv, HEADER true);

-- Client-side import: the psql CLIENT reads the file from your machine.
-- In interactive psql:
-- \copy fruits FROM '/Users/you/Desktop/fruits.csv' WITH (FORMAT csv, HEADER true)

-- COPY can also stream from STDIN — used by drivers for bulk loading.
-- Example shows the protocol; the actual rows would follow the command in a stream.
-- COPY fruits FROM STDIN WITH (FORMAT csv, HEADER true);

COPY ... TO STDOUT 会写入连接的输出流。使用 psql -f 运行 SQL 文件时，这个流就是你的终端——很适合临时导出。可以从 Shell 重定向输出，将结果保存到文件：psql -f source/copy.sql postgres > out.csv。

COPY ... FROM <path> 会从服务器上的文件读取数据。PostgreSQL 进程需要拥有读取权限；在托管云数据库上，你通常没有这种权限。客户端一侧的 \copy（一种 psql 元命令）会从你的机器读取文件，通过连接将其传输，并在服务器上插入——这与 COPY FROM STDIN 的行为完全相同，只是通过一个便捷封装来实现。

对于编写批量装载代码的应用，几乎每个驱动都提供“COPY FROM STREAM” API（psycopg 的 copy_from、pgx 的 CopyFrom 等）。装载几千行时，它比分批执行的 INSERT 快得多；对于数百万行，它通常是最佳起点，不过收益取决于工作负载。

COPY FROM 会在目标表上调用触发器，并强制执行检查约束和外键约束。触发器的工作因此会计入装载成本。它不会调用规则。暂存表适合在将数据插入最终表之前，对装载内容进行验证和转换。

运行示例

运行方式：

$ psql -f source/copy.sql postgres
id,name
1,apricot
2,blueberry
3,chestnut
4,damson
5,elderberry
101,fruit_1
102,fruit_2
103,fruit_3

常见陷阱

• 混淆 COPY 和 \copy：前者在服务器上运行（路径是服务器路径，权限是服务器权限）；后者在客户端运行（路径是本地路径）。一个常见错误是使用 COPY ... FROM '~/file.csv'，然后收到“permission denied”，因为服务器无法读取你的主目录。
• 编码不匹配：文件使用 ENCODING 选项指定的编码；如果没有该选项，则使用当前客户端编码。编码错误的 CSV 会产生乱码或“invalid byte sequence”错误。使用 WITH (ENCODING 'utf8')，或者事先进行转换。
• 包含嵌入换行符的 CSV 需要 QUOTE 和 ESCAPE 选项。默认值（两者都是 "）通常可以正常工作。
• 在大规模装载中使用带约束的 COPY 会很慢。一个技巧是：先装载到暂存表，去重/校验，然后再通过 INSERT ... ON CONFLICT 插入目标表。

CREATE TEMP TABLE fruits_stage (id integer, name text);
\copy fruits_stage FROM 'out.csv' WITH (FORMAT csv, HEADER true)
SELECT count(*) FROM fruits_stage;

下载

SQL 文件：copy.sql · 全部示例文件 · 准备工作

返回目录