批量导入导出（COPY）

COPY 是 PostgreSQL 吞吐最高的数据搬运工具：它绕开 SQL 解析与逐行往返，直接在服务端与文件/流之间做批量读写，速度通常是批量 INSERT 的数倍到数十倍。数据迁移、日志装载、报表导出、备份快照都离不开它。本节讲清服务端 COPY 与客户端 \copy 的分工、文本/CSV/二进制三种格式、常见报错的定位方法，以及生产环境的安全与调优要点。

前置知识

阅读本节前，建议先了解：合并数据（MERGE）

基础概念

COPY 有两种形态，区别只在文件由谁打开：

形态	执行位置	文件位于	权限要求
COPY（SQL 语句）	服务端进程	数据库服务器的路径	超级用户或 pg_read_server_files / pg_write_server_files 成员
\copy（psql 元命令）	客户端（psql）	你本机的路径	普通用户即可，走的是 COPY ... STDIN/STDOUT

这是新手最容易卡住的地方：COPY FROM '/tmp/a.csv' 里的 /tmp 是服务器上的 /tmp，不是你的笔记本。

几个关键术语：

• STDIN / STDOUT：COPY 的流式端点。\copy 的本质就是客户端把本地文件转成流，喂给服务端的 COPY ... FROM STDIN。
• 文本格式（text）：PostgreSQL 默认格式，制表符分隔，反斜杠转义。机器可读但不如 CSV 通用。
• CSV 格式（csv）：标准 CSV，可指定引号、表头、编码。跨系统交换数据首选。
• 二进制格式（binary）：COPY BINARY 专有格式，最快但不跨版本兼容，不适合当作归档格式。
• FREEZE：导入时把行直接标记为"已冻结"（可见于所有事务），跳过后续 VACUUM 的一步。只应在新建或刚 TRUNCATE 过的表上使用。
• ON_ERROR：COPY 的容错开关，默认遇到第一行错误就整条语句失败（stop），可改为 ignore 跳过坏行。

语法与用法

完整语法骨架：

COPY table_name [ ( column_name [, ...] ) ]
    FROM { 'filename' | PROGRAM 'command' | STDIN }
    [ [ WITH ] ( option [, ...] ) ]
    [ WHERE condition ]

COPY { table_name [ ( column_name [, ...] ) ] | ( query ) }
    TO { 'filename' | PROGRAM 'command' | STDOUT }
    [ [ WITH ] ( option [, ...] ) ]

四种最常用的形态：

-- 1. 服务端从 CSV 导入（注意路径在数据库服务器上）
COPY inventory (sku, name, qty, price) FROM '/var/lib/postgresql/import/inventory.csv'
  WITH (FORMAT csv, HEADER true);

-- 2. 服务端导出查询结果到 CSV
COPY (SELECT sku, name, qty FROM inventory WHERE qty > 0)
  TO '/var/lib/postgresql/export/inventory.csv'
  WITH (FORMAT csv, HEADER true);

-- 3. psql 客户端导入本地文件（路径在你的机器上）
-- \copy inventory (sku, name, qty, price) FROM 'inventory.csv' WITH (FORMAT csv, HEADER true)

-- 4. 导出为压缩文件（服务端执行 gzip）
COPY inventory TO PROGRAM 'gzip > /var/lib/postgresql/export/inventory.csv.gz'
  WITH (FORMAT csv, HEADER true);

常用选项：

选项	取值	说明
FORMAT	text / csv / binary	格式，默认 text
HEADER	true / false	是否带表头（csv 常用）
DELIMITER	单字符	分隔符，csv 默认 ,，text 默认制表符
QUOTE / ESCAPE	单字符	csv 的引号与转义符，默认 "
NULL	字符串	NULL 的表示，默认 text 为 \N、csv 为空串
ENCODING	编码名	指定文件编码，如 'GBK'，由服务端转换
FREEZE	true / false	冻结导入的行，仅限新表/刚 TRUNCATE 的表
ON_ERROR	stop / ignore	出错即停还是跳过坏行（PostgreSQL 17+）
LOG_VERBOSITY	default / verbose	跳过坏行时的日志详细度（PostgreSQL 17+）

COPY 与 INSERT 的能力边界：

能力	COPY	多行 INSERT
速度（万行级）	最快	慢数倍至数十倍
逐行处理/转换	不支持（仅列映射与表达式常量）	可用函数、CASE、子查询
跳过坏行	ON_ERROR ignore（17+）	单行失败整条语句失败
返回导入的数据	不支持 RETURNING	支持 RETURNING
触发触发器	支持	支持
读取服务端文件	支持（需权限）	不支持

详细说明

服务端 COPY 与客户端 \copy

判断"文件在哪"只需问一句：这条命令是谁在跑？

-- 这是 SQL 语句，由服务端执行 → 路径必须在数据库服务器上
COPY inventory FROM '/data/inventory.csv' WITH (FORMAT csv, HEADER true);

# 这是 psql 元命令，由 psql 客户端执行 → 路径在你本机
psql -h db.example.com -U app -d shop \
  -c "\copy inventory FROM 'inventory.csv' WITH (FORMAT csv, HEADER true)"

也可以在 psql 交互里直接用（注意反斜杠，且不要加分号）：

\copy inventory FROM 'inventory.csv' WITH (FORMAT csv, HEADER true)
COPY 1200

从别的机器拉数据、或用应用代码导入时，走流式接口最灵活：

# 从远端直接流进本地库，不落中间文件
psql -h db.example.com -U app -d shop \
  -c "COPY inventory TO STDOUT WITH (FORMAT csv, HEADER true)" > inventory.csv

三种格式的取舍

-- text：默认格式，制表符分隔，转义特殊字符
COPY inventory TO STDOUT WITH (FORMAT text);

-- csv：跨系统交换首选，可读、可被 Excel/脚本直接消费
COPY inventory TO STDOUT WITH (FORMAT csv, HEADER true, DELIMITER ',');

-- binary：最快，但格式与版本/平台绑定，不能当长期归档
COPY inventory TO STDOUT WITH (FORMAT binary);

-- text 输出示例（制表符分隔）
SKU-1001	机械键盘	150	379.00

-- csv 输出示例
sku,name,qty,price
SKU-1001,机械键盘,150,379.00

格式	速度	可读性	跨版本/跨平台	典型用途
text	快	中	好	库间搬运、psql 默认
csv	快	高	最好	与业务系统/Excel 交换
binary	最快	无	差（与版本绑定）	同版本大批量搬运

列映射、默认值与表达式

COPY FROM 只允许列列表，不允许写函数或 CASE。需要转换时，先导入到暂存表，再用 SQL 加工。

-- 只提供部分列，其余列走默认值（含 IDENTITY 自增）
COPY inventory (sku, name, qty, price) FROM STDIN WITH (FORMAT csv);

SKU-2001,HDMI 线,300,29.00
SKU-2002,网线,500,15.00
\.
COPY 2

一个列都不给（省略列列表）意味着文件必须按表定义顺序提供所有列，这非常脆弱：

-- 危险：表结构一变，导入就全错位
COPY inventory FROM '/data/inventory.csv' WITH (FORMAT csv);

-- 推荐：显式列出列，与文件列顺序一一对应
COPY inventory (sku, name, qty, price) FROM '/data/inventory.csv' WITH (FORMAT csv, HEADER true);

想边导入边转换，走"暂存表 + INSERT ... SELECT"：

-- 1. 先原样导入暂存表（列全部为 text，容错最强）
CREATE TEMP TABLE stage_inventory (sku text, name text, qty text, price text);
COPY stage_inventory FROM '/data/inventory.csv' WITH (FORMAT csv, HEADER true, NULL '');

-- 2. 清洗、转换、去重后写入正式表
INSERT INTO inventory (sku, name, qty, price)
SELECT sku, name, qty::int, price::numeric(12,2)
FROM stage_inventory
ON CONFLICT (sku) DO UPDATE
SET qty = EXCLUDED.qty, price = EXCLUDED.price;

导出查询结果与列名

COPY ... TO 支持子查询，导出时列名来自子查询的输出列名（用 AS 别名控制表头）。

COPY (
  SELECT i.sku,
         i.name,
         i.qty,
         i.qty * i.price AS amount,
         now()           AS exported_at
  FROM inventory i
  WHERE i.status = 'on_sale'
  ORDER BY i.sku
) TO '/var/lib/postgresql/export/on_sale.csv'
  WITH (FORMAT csv, HEADER true);

-- 验证导出结果（重新读回来）
CREATE TEMP TABLE verify_inventory (sku text, name text, qty int, amount numeric, exported_at timestamptz);
COPY verify_inventory FROM '/var/lib/postgresql/export/on_sale.csv' WITH (FORMAT csv, HEADER true);
SELECT count(*) AS exported_rows FROM verify_inventory;

 exported_rows
---------------
             3
(1 row)

出错排查：行号与列号

COPY 的报错信息相当精确，会给出"第几行、哪一列、期望什么"，照着定位即可。

COPY inventory (sku, name, qty, price) FROM '/data/bad_inventory.csv' WITH (FORMAT csv, HEADER true);

ERROR:  invalid input syntax for type integer: "abc"
CONTEXT:  COPY inventory, line 3, column qty: "abc"

常见报错与原因：

报错信息	原因	处理
invalid input syntax for type integer	数据含非数字	清洗数据，或先导入 text 暂存表
extra data after last expected column	列数多于列列表	检查分隔符与列数，或漏写引号
missing data for column "xxx"	列数少于列列表	补齐列，或为缺失列提供 NULL
unterminated CSV quoted field	引号未闭合、字段内有换行	检查 QUOTE / ESCAPE 设置
null value in column "xxx" violates not-null constraint	NULL 写法不匹配	调整 NULL 选项或清洗数据
COPY file ... could not open file	路径在客户端而非服务端	改用 \copy 或把文件放到服务器
permission denied for ...	无服务端文件读取权限	授予 pg_read_server_files

从 PostgreSQL 17 起，可以跳过坏行继续导入，把坏行记入日志：

-- PostgreSQL 17+：跳过错误行
COPY inventory (sku, name, qty, price) FROM '/data/bad_inventory.csv'
  WITH (FORMAT csv, HEADER true, ON_ERROR ignore, LOG_VERBOSITY verbose);

NOTICE:  skipping row due to data type incompatibility at line 3, column qty: "abc"
COPY 1199
-- 1200 行里跳过了 1 行

对 CSV 里为空的数字列，用 NULL '' 明确空串即 NULL，避免把它当成空字符串试图转数字：

COPY inventory (sku, name, qty, price) FROM '/data/inventory.csv'
  WITH (FORMAT csv, HEADER true, NULL '', DELIMITER ',', QUOTE '"');

性能：为什么 COPY 这么快

COPY 比 INSERT 快，主要来自三件事：单次网络往返搬运整批数据、批量 WAL 写入、批量页填充。

-- 完整性能组合：新表 + 单事务 + FREEZE + 关闭同步提交
BEGIN;
SET LOCAL synchronous_commit = off;
CREATE TABLE inventory_bulk (LIKE inventory INCLUDING ALL);
COPY inventory_bulk FROM '/data/inventory.csv' WITH (FORMAT csv, HEADER true, FREEZE true);
COMMIT;

-- 示意性实测对比（100 万行，单机 SSD，仅供参考，请以实际环境为准）
方式                                    耗时        相对倍数
单行 INSERT × 1000000 次               412000 ms     1.000
多行 VALUES（每批 1000 行）              9800 ms      0.024
COPY（默认）                              2400 ms      0.006
COPY（FREEZE + synchronous_commit=off）   1650 ms      0.004

几条经验：

• FREEZE 只在刚创建或刚 TRUNCATE 的表上安全。 对已有数据的表使用会导致旧行可见性错误。
• 临时关掉索引与约束（大表全量灌数据时）比导入完再建索引更快：

BEGIN;
ALTER TABLE inventory DROP CONSTRAINT IF EXISTS inventory_pkey;
ALTER TABLE inventory DROP CONSTRAINT IF EXISTS inventory_qty_check;
COPY inventory FROM '/data/inventory.csv' WITH (FORMAT csv, HEADER true);
ALTER TABLE inventory ADD PRIMARY KEY (sku);
ALTER TABLE inventory ADD CHECK (qty >= 0);
COMMIT;

• 导入完成后必须 ANALYZE，否则优化器拿不到新数据的分布信息。

VACUUM (ANALYZE) inventory;

权限与安全

COPY ... FROM/TO 'file' 是服务端以数据库进程身份读写文件，因此默认只允许超级用户。这既是限制也是保护。

-- 授权角色读取服务端文件（谨慎！这等于让该角色能读数据库能读的所有文件）
GRANT pg_read_server_files TO etl_user;

-- 也可以只授予写入权限
GRANT pg_write_server_files TO etl_user;

更安全的做法是不用服务端文件路径，改用 STDIN/STDOUT：

# 把文件内容通过 stdin 灌进去，数据库进程完全不接触文件系统
cat inventory.csv | psql -h db -U app -d shop \
  -c "COPY inventory (sku, name, qty, price) FROM STDIN WITH (FORMAT csv, HEADER true)"

PROGRAM 选项更危险，它会在服务器上执行 shell 命令，只有超级用户可用：

-- 危险：以数据库进程身份执行任意命令
COPY inventory TO PROGRAM 'gzip > /var/lib/postgresql/export/inventory.csv.gz' WITH (FORMAT csv, HEADER true);

另外注意：COPY 与普通 INSERT 一样会触发 BEFORE/AFTER 触发器，大表导入前应评估触发器的开销。

实战示例

下面用一个"每日库存快照导入 + 报表导出"的完整流程演示 COPY 的典型用法。

-- 步骤 1：准备数据（用 psql 的 \copy 从本地文件导入）
DROP TABLE IF EXISTS inventory_snapshot;
CREATE TABLE inventory_snapshot (
  sku        text PRIMARY KEY,
  name       text          NOT NULL,
  qty        int           NOT NULL CHECK (qty >= 0),
  price      numeric(12,2) NOT NULL CHECK (price >= 0),
  status     text          NOT NULL DEFAULT 'on_sale',
  updated_at timestamptz   NOT NULL DEFAULT now()
);

-- 步骤 2：创建一个用于演示的导入表（用 SQL 直接灌数据，效果等同 COPY）
DROP TABLE IF EXISTS inventory_incoming;
CREATE TABLE inventory_incoming (LIKE inventory_snapshot INCLUDING ALL);

INSERT INTO inventory_incoming (sku, name, qty, price, status) VALUES
  ('SKU-1001', '机械键盘',  150, 379.00, 'on_sale'),
  ('SKU-1002', '无线鼠标',  100, 109.00, 'on_sale'),
  ('SKU-1003', 'USB 集线器', 200,  89.00, 'on_sale'),
  ('SKU-1004', '显示器支架',  30, 199.00, 'off_shelf');

DROP TABLE
CREATE TABLE
DROP TABLE
CREATE TABLE
INSERT 0 4

-- 步骤 3：导出为 CSV（带表头），走 STDOUT 便于检查内容
COPY inventory_incoming TO STDOUT WITH (FORMAT csv, HEADER true, DELIMITER ',');

sku,name,qty,price,status,updated_at
SKU-1001,机械键盘,150,379.00,on_sale,2026-09-24 16:05:11.203+08
SKU-1002,无线鼠标,100,109.00,on_sale,2026-09-24 16:05:11.203+08
SKU-1003,USB 集线器,200,89.00,on_sale,2026-09-24 16:05:11.203+08
SKU-1004,显示器支架,30,199.00,off_shelf,2026-09-24 16:05:11.203+08

-- 步骤 4：只导出在售商品，交给下游报表系统
COPY (
  SELECT sku, name, qty, price, qty * price AS amount
  FROM inventory_incoming
  WHERE status = 'on_sale'
  ORDER BY sku
) TO STDOUT WITH (FORMAT csv, HEADER true);

sku,name,qty,price,amount
SKU-1001,机械键盘,150,379.00,56850.00
SKU-1002,无线鼠标,100,109.00,10900.00
SKU-1003,USB 集线器,200,89.00,17800.00

# 步骤 5：在 psql 中把本地文件导入目标表（真正的 \copy 写法）
# \copy inventory_snapshot (sku, name, qty, price, status) FROM 'inventory.csv' WITH (FORMAT csv, HEADER true)
# 输出：COPY 4

-- 步骤 6：用 STDIN 流式导入（可在任意客户端触发，无需服务端文件权限）
COPY inventory_snapshot (sku, name, qty, price, status) FROM STDIN
  WITH (FORMAT csv, HEADER true);

SKU-1001,机械键盘,150,379.00,on_sale
SKU-1002,无线鼠标,100,109.00,on_sale
SKU-1003,USB 集线器,200,89.00,on_sale
SKU-1004,显示器支架,30,199.00,off_shelf
\.
COPY 4

-- 步骤 7：核对导入结果
SELECT status, count(*) AS cnt, sum(qty) AS total_qty, sum(qty * price) AS amount
FROM inventory_snapshot
GROUP BY status
ORDER BY status;

  status   | cnt | total_qty |  amount
-----------+-----+-----------+----------
 off_shelf |   1 |        30 |  5970.00
 on_sale   |   3 |       450 | 85550.00
(2 rows)

-- 步骤 8：导出为 text 格式做对照（默认格式，制表符分隔，NULL 为 \N）
COPY (SELECT sku, name, qty FROM inventory_snapshot ORDER BY sku)
  TO STDOUT WITH (FORMAT text, NULL 'NULL');

SKU-1001	机械键盘	150
SKU-1002	无线鼠标	100
SKU-1003	USB 集线器	200
SKU-1004	显示器支架	30

-- 步骤 9：演示坏行跳过（PostgreSQL 17+ 的 ON_ERROR 选项）
-- 文件第 2 行的 qty 是 "abc"，会触发类型错误
COPY inventory_snapshot (sku, name, qty, price) FROM STDIN
  WITH (FORMAT csv, ON_ERROR ignore, LOG_VERBOSITY verbose);

SKU-9001,测试商品,10,9.90
SKU-9002,坏行商品,abc,9.90
SKU-9003,测试商品2,20,19.90
\.
NOTICE:  skipping row due to data type incompatibility at line 2, column qty: "abc"
COPY 2
-- 3 行中导入了 2 行，坏行被跳过并记录

-- 步骤 10：导入后更新统计信息
VACUUM (ANALYZE) inventory_snapshot;

SELECT relname, n_live_tup, last_analyze
FROM pg_stat_user_tables
WHERE relname = 'inventory_snapshot';

      relname       | n_live_tup |         last_analyze
--------------------+------------+-------------------------------
 inventory_snapshot |          6 | 2026-09-24 16:12:38.902+08
(1 row)

上面的流程覆盖了 COPY 的主要形态：CSV/text 导出、查询结果导出、STDIN 流式导入、坏行跳过，以及导入后的统计信息刷新。

注意事项

• COPY 的文件路径默认在数据库服务器上。 这是最常见的困惑点。要走本机路径必须用 psql 的 \copy。

-- 报错：客户端以为路径在本机
COPY inventory FROM '/Users/me/inventory.csv' WITH (FORMAT csv);
-- ERROR: could not open file "/Users/me/inventory.csv" for reading: No such file or directory

-- 正确：改用 psql 元命令（路径在本机）
-- \copy inventory FROM '/Users/me/inventory.csv' WITH (FORMAT csv, HEADER true)

• COPY 不支持 RETURNING。 需要"边写边拿回数据"只能用 INSERT ... RETURNING。

• COPY FROM 不能写函数或表达式。 只接受列列表，转换逻辑必须靠暂存表 + INSERT ... SELECT。

• FREEZE 只能用在新建或刚 TRUNCATE 的表上。 用在已有数据的表上会造成可见性错误，属于破坏性误用。

• binary 格式不跨版本、不跨平台。 它绑定了类型内部表示，升级大版本后旧文件可能读不了，不要用它做长期归档。

• ON_ERROR ignore 会静默跳过数据。 必须同时检查 COPY n 的返回行数与文件行数是否一致，否则丢数据无人察觉。

• COPY 会触发触发器与约束。 大表导入前应确认触发器必要性与约束校验开销，必要时先禁用再恢复。

• COPY 与 INSERT 一样逐行走 MVCC。 它不绕过 WAL（除非表是 UNLOGGED 或同事务 CREATE 后导入），也不会自动 VACUUM。

• PROGRAM 与文件路径选项都是"数据库进程执行"级别的高危能力。 只授予必要角色，能走 STDIN/STDOUT 就不要开文件权限。

-- 检查哪些角色拥有服务端文件权限
SELECT r.rolname, r.rolsuper, r.rolreplication
FROM pg_roles r
WHERE pg_has_role(current_user, r.oid, 'USAGE')
  AND r.rolname IN ('pg_read_server_files', 'pg_write_server_files');

• \copy 是客户端命令，末尾不加分号。 加了分号会被当作 SQL 交给服务端，直接报语法错误。

• 导入大量数据会显著改变数据分布。 不执行 ANALYZE 会让后续查询选错计划。

最佳实践

1. 默认用 \copy 或 STDIN/STDOUT，不要开服务端文件权限。 除非确实需要在服务器内做本地高速搬运。

# 推荐：文件始终在客户端，数据库进程不接触文件系统
psql -h db -U app -d shop -c "\copy inventory FROM 'inventory.csv' WITH (FORMAT csv, HEADER true)"

2. 跨系统交换一律用 CSV + HEADER true。 可读、可被脚本与 Excel 消费，出问题也容易肉眼定位。

COPY inventory TO STDOUT WITH (FORMAT csv, HEADER true, DELIMITER ',', QUOTE '"', NULL '');

3. 导入时显式列出列，并把清洗放到暂存表。 目标表结构变化时不会"静默错位"。

CREATE TEMP TABLE stage_inventory (sku text, name text, qty text, price text);
COPY stage_inventory FROM STDIN WITH (FORMAT csv, HEADER true, NULL '');

INSERT INTO inventory (sku, name, qty, price)
SELECT upper(trim(sku)), trim(name), qty::int, price::numeric(12,2)
FROM stage_inventory
WHERE sku <> '' AND qty ~ '^[0-9]+$';

4. 大表全量导入用"并行 + 无索引 + 事务"三件套。 多进程按业务键区间 COPY，导入完再建索引与约束。

BEGIN;
SET LOCAL synchronous_commit = off;
COPY inventory (sku, name, qty, price) FROM STDIN WITH (FORMAT csv, FREEZE true);
COMMIT;
CREATE INDEX CONCURRENTLY idx_inventory_status ON inventory (status);
ANALYZE inventory;

5. 导入前后校验行数，把丢数据变成可检测的问题。 尤其在使用 ON_ERROR ignore 时。

-- 导入前
SELECT count(*) AS before_rows FROM inventory;
-- 导入：COPY n（在客户端输出里核对 n）
-- 导入后
SELECT count(*) AS after_rows FROM inventory;

6. 导出大结果集时加 ORDER BY。 便于下游做增量比对与断点校验，也让文件可重复生成、可 diff。

COPY (SELECT sku, qty FROM inventory ORDER BY sku)
  TO STDOUT WITH (FORMAT csv, HEADER true);

7. 压缩交给 PROGRAM 或管道，而不是数据库。 数据库只负责把字节吐出来，压缩在操作系统层完成。

# 客户端侧压缩，最安全
psql -h db -U app -d shop -c "COPY inventory TO STDOUT WITH (FORMAT csv, HEADER true)" \
  | gzip > inventory.csv.gz

8. 导入完成后固定执行 ANALYZE，必要时 VACUUM。 把它写进 ETL 脚本的最后一步。

VACUUM (ANALYZE) inventory;

9. 把 COPY 行数当断言记录到任务日志。 生产 ETL 里"导入了多少行"应当和"期望多少行"一起被审计。

-- 用一条查询沉淀导入统计（示例：核对总数与状态分布）
SELECT count(*) AS rows_loaded,
       count(*) FILTER (WHERE status = 'on_sale') AS on_sale_rows,
       max(updated_at)                             AS latest_update
FROM inventory;

下一节

继续学习：返回数据（RETURNING）

参考链接

• COPY
• Populating a Database
• psql \copy
• Foreign Data Wrappers