21. COPY 导入导出数据

本节目标：学完你能用 COPY 把 CSV 文件批量灌进表，也能把表导出成 CSV，并分清 COPY 和 \copy 在权限和文件位置上的差别，还会用 HEADER、DELIMITER、NULL、ENCODING 等常用选项。

INSERT 一次插一行或几行还行，但要导入几万行数据，一条条写就不现实了。COPY 是 PostgreSQL 专门干「批量搬运文件数据」的利器。

COPY 从文件导入（FROM）

假设有个 users.csv 文件，内容如下（第一行是表头）：

username,email,age
xiaoming,ming@example.com,28
xiaohong,hong@example.com,22
wangwu,wang@example.com,35

在 psql 里执行导入时，提示符是这样的：

postgres=# COPY users (username, email, age) FROM '/tmp/users.csv' WITH (FORMAT csv, HEADER true);
COPY 3

用 COPY ... FROM 把它导进 users 表：

COPY users (username, email, age)
FROM '/tmp/users.csv'
WITH (FORMAT csv, HEADER true);

几个关键点：

COPY 表 (列...)：列的顺序要和 CSV 文件的列顺序一致。
FROM '路径'：文件位置。
HEADER true：告诉 PostgreSQL 第一行是表头，导入时跳过它。
FORMAT csv：指明文件格式是 CSV（也可写 DELIMITER ',' CSV HEADER 这种老式写法）。

成功后回显类似 COPY 3，表示导了 3 行。

COPY 导出到文件（TO）

反过来，把表导出成 CSV：

COPY users (username, email, age)
TO '/tmp/users_out.csv'
WITH (FORMAT csv, HEADER true);

不写 HEADER 就不带表头。也可以直接导整张表：

COPY users TO '/tmp/users_all.csv' WITH (FORMAT csv, HEADER true);

导出查询结果，而不是整张表

TO 前面不一定非得写表名，也能写一个括号里的查询。比如只导出年龄大于 30 的用户：

COPY (SELECT username, age FROM users WHERE age > 30)
TO '/tmp/old_users.csv'
WITH (FORMAT csv, HEADER true);

这招在「导出一份定制报表」时特别好用，不用先建临时表。

常用选项

COPY 还有几个常用开关，按需组合：

DELIMITER：列分隔符，默认是制表符 \t。CSV 一般用逗号。

NULL：文件里表示「空值」的字符串。比如数据里用 NULL 三个字母代表缺失：

COPY users (username, email, age)
FROM '/tmp/users.csv'
WITH (FORMAT csv, HEADER true, NULL 'NULL');

QUOTE：CSV 里包围字段的引号字符，默认是 "。

ENCODING：文件编码。中文环境常遇到 Excel 要 GBK：

COPY users TO '/tmp/users_gbk.csv'
WITH (FORMAT csv, HEADER true, ENCODING 'GBK');

Tip

从 Excel 另存为 CSV 时，Windows 上常是 GBK 编码、用逗号分隔。导出入库前确认编码，否则中文会乱码。用 ENCODING 'GBK' 能直接读这种文件。

COPY 与 \copy 的区别

这是新手最容易迷糊的地方，记住一句话：COPY 是服务端读写文件，\copy 是客户端读写文件。

对比项	COPY	\copy
文件位置	数据库服务器机器上	你本地（客户端）机器上
读写方	PostgreSQL 服务进程	psql 客户端程序
所需权限	通常需要超级用户	普通用户即可
使用场景	服务器本机有文件、有超级权限	远程服务器、只想读写本地文件

\copy 是 psql 里带反斜杠的元命令，语法和 COPY 几乎一样，只是放在 psql 里、文件路径指你自己的电脑：

postgres=# \copy (SELECT username, email FROM users) TO '/tmp/local_users.csv' WITH (FORMAT csv, HEADER true)

Warning

直接用 COPY 时，文件路径指的是数据库服务器那台机器，不是你敲命令的电脑。如果文件在你本地、你又没有服务器超级权限，就会报错。这种情况请改用 \copy。

权限注意

COPY 命令一般要求执行者是数据库超级用户（superuser），因为服务端要直接读磁盘文件。
\copy 不需要超级权限，它是在 psql 客户端侧完成文件读写的，对普通账号更友好。
服务端读写文件时，文件还要能被 PostgreSQL 服务运行的系统用户读到或写入，否则也会失败。

Tip

大量数据导入时，COPY 比一条条 INSERT 快得多，因为它少了很多往返开销。日常搬数据，优先想 COPY / \copy。

常见错误

文件路径指错机器：本地文件用 COPY 会报「找不到文件」。换成 \copy。
分隔符不匹配：文件用逗号，却没写 DELIMITER ','，会按默认制表符切，列数对不上。
表头没跳过：忘了 HEADER true，第一行表头被当成数据插进去了。
权限不足：普通用户直接 COPY 报权限错，改用 \copy 或找管理员。

从标准输入导入

除了读磁盘文件，COPY 还能从「标准输入」读数据，常配合 shell 管道用。比如在 psql 之外这样喂数据：

cat /tmp/users.csv | psql -d 数据库名 -c "COPY users(username,email,age) FROM STDIN WITH (FORMAT csv, HEADER true)"

在 psql 内部，对应的就是 \copy ... FROM stdin，然后粘贴数据、用反斜杠加点（.）结束。适合临时塞一小段数据，不用先落盘成文件。

导入出错怎么办

COPY 导入是「整批」的：某行格式不对，默认整条语句失败、一行都不进。常见原因：

某行的列数和其他行不一致。
数字列里混进了非数字字符。
日期格式写错，比如 '2026/13/40' 这种不存在的日期。

出错时 PostgreSQL 会告诉你第几行附近有问题，按提示修好文件再重跑。如果想「跳过坏行」，COPY 本身不支持，需要先在外部把数据清洗好。

Tip

大文件导入前，先拿前几行做成小文件用 COPY 试跑，确认格式、编码、分隔符都没问题，再跑全量，能省不少排查时间。

用 COPY 做备份与恢复

COPY 也是轻量的备份手段。把一个表导成 CSV，需要时再导回去：

-- 备份
COPY users TO '/tmp/users_bak.csv' WITH (FORMAT csv, HEADER true);
-- 恢复（表已建好的前提下）
COPY users (username, email, age) FROM '/tmp/users_bak.csv' WITH (FORMAT csv, HEADER true);

它不像 pg_dump 那样保留结构，只搬数据，适合「临时导一份」或「在库之间搬表」。

乱码怎么排查

中文乱码多半是编码对不上。排查顺序：

看导出时用的 ENCODING，导入时也要一致。
Excel 在 Windows 上常存成 GBK，用 ENCODING 'GBK' 读。
确认终端和文件本身不是混合编码。

COPY users TO '/tmp/users_gbk.csv' WITH (FORMAT csv, HEADER true, ENCODING 'GBK');

Tip

拿不准编码时，先用 file 文件名 之类的工具看一眼文件真实编码，再决定 COPY 的 ENCODING 参数。

超大文件怎么导更稳

文件特别大（几 GB）时，一次性 COPY 可能占满事务日志。实务上有几个办法：

把大文件按行拆成几份，分多次 COPY。
用 \copy 在客户端侧传，减轻服务端压力。
导入前先在小样本上验证格式，避免跑了一半才发现错。

Tip

数据量上了规模，导入速度往往不是瓶颈，瓶颈在「出错后重跑」。所以清洗和试跑比盲目求快更重要。

小结

COPY 表 FROM 文件 导入，COPY 表 TO 文件 导出，常配 HEADER、DELIMITER。
COPY 在服务端读写文件、要超级权限；\copy 在客户端读写、普通用户可用。
文件路径在 COPY 里指服务器，在 \copy 里指你本地机器。
还能导查询结果（COPY (SELECT …) TO）、指定 NULL 串和编码。
批量搬数据用 COPY 系列，比循环 INSERT 高效。