PostgreSQL 中的 COPY 命令快速导入导出

PostgreSQL 中的 COPY 命令：快速导入导出实战指南

PostgreSQL 的 COPY 命令是在表和文件之间高速传输数据的利器。它直接绕过 SQL 层，以流式方式读写文件，非常适合批量数据加载、备份和数据交换场景。

1. 两种 COPY：服务器端 vs 客户端

理解 COPY 和 \copy 的区别是避免混淆的第一步。

• SQL COPY（服务器端）
  • 执行位置：PostgreSQL 服务器进程。
  • 文件访问：必须是数据库超级用户，且文件路径相对于服务器文件系统。文件读写权限归运行 PostgreSQL 的系统用户所有。
  • 典型用法：在 psql 内执行纯 SQL，或在应用代码中通过数据库驱动（如 JDBC 的 CopyManager）调用。
• \copy（客户端元命令）
  • 执行位置：psql 客户端工具。
  • 文件访问：基于客户端本地文件系统，无需超级用户权限。它是 COPY ... TO STDOUT 和 COPY ... FROM STDIN 的便捷封装。
  • 适用场景：个人开发者使用 psql 快速导入导出本地 CSV 文件。

2. 导出数据：从表到文件

2.1 将整个表导出为 CSV

最简形式，导出 sales 表到 /tmp/sales.csv（假设使用 SQL COPY 且在服务器端有写权限）：

COPY sales TO '/tmp/sales.csv' WITH (FORMAT CSV, HEADER true);

• FORMAT CSV：指定逗号分隔值格式。
• HEADER true：在文件第一行写入列名。

2.2 只导出查询结果

你可以导出任意 SELECT 语句的结果，无需先创建视图或临时表：

COPY (
  SELECT product_id, product_name, price
  FROM products
  WHERE category = 'Electronics'
) TO '/tmp/electronics.csv' WITH (FORMAT CSV, HEADER true);

2.3 定制分隔符与引用符

处理制表符分隔或需要特殊引用的情况：

COPY users TO '/tmp/users.tsv' WITH (FORMAT TEXT, DELIMITER E'\t', QUOTE '$');

• FORMAT TEXT：默认是制表符分隔，但用 DELIMITER 可改分隔符。
• QUOTE：默认是双引号，这里改为 $，常用于数据中包含双引号的情况。

2.4 处理 NULL 值

默认 CSV 格式中 NULL 为空字符串，易与真正的空字符串混淆。可指定 NULL 标记：

COPY orders TO '/tmp/orders.csv' WITH (CSV, HEADER, NULL 'NULL');

这样 NULL 字段会输出为字符串 NULL，导入时再用相同设置恢复。

3. 导入数据：从文件到表

3.1 导入 CSV 到表（覆盖/追加）

假设已存在表 new_users，结构与文件匹配：

COPY new_users FROM '/tmp/users_import.csv' WITH (FORMAT CSV, HEADER true);

重要：COPY 默认向表中添加数据（类似 INSERT），不会先清空表。如需清空，可提前 TRUNCATE new_users;。

3.2 只导入特定列

如果 CSV 文件包含 5 列，但只想导入第 1、3、4 列到相应字段：

COPY employees (id, department, salary)
FROM '/tmp/emp_data.csv' WITH (FORMAT CSV, HEADER true);

文件仍需包含所有列（至少有正确的分隔符），未列在括号中的列将被忽略。

3.3 处理导入错误

默认遇到任何格式错误都会中止整个 COPY 操作。在 PostgreSQL 14+ 中，可忽略错误行并继续：

COPY sensor_data FROM '/tmp/readings.csv' WITH (FORMAT CSV, LOG ERRORS, ROW LIMIT 100);

错误详情会记录到内部 errlog 表并产生一条警告，其余有效行会正常插入。使用前需创建错误日志表：

CREATE TABLE errlog(
  cmd_time timestamptz,
  line_number bigint,
  error_message text,
  raw_data text
);

4. 客户端工具 psql 的 \copy 实战

当你在本地开发，想导入桌面上的 CSV 文件到远程数据库：

\copy products FROM '~/data/products.csv' WITH (FORMAT CSV, HEADER true)

导出到本地：

\copy (SELECT * FROM products WHERE price > 100) TO '~/exports/expensive_products.csv' WITH (FORMAT CSV, HEADER true, ENCODING 'UTF8')

\copy 支持所有 COPY 选项，还可以指定 ENCODING 来处理字符集转换。

5. 高级控制与数据格式

5.1 二进制格式：极致速度

对于同构 PostgreSQL 环境之间的数据交换，二进制格式比文本/CSV 解析更快，且不会丢失浮点精度。

导出：

COPY users TO '/tmp/users.bin' WITH (FORMAT BINARY);

导入时表结构必须完全一致（列类型、顺序相同），否则会失败。

5.2 自定义 NULL 占位符

在导入时，指定无引号空字符串当作 NULL：

COPY archive FROM '/data/archive.csv' WITH (CSV, NULL '', FORCE_NULL (column1, column2));

FORCE_NULL 强制将指定列的空字符串（无引号）转为 NULL，即使它们不是完全空着（如包含空格则不转换）。

5.3 使用程序化接口

在 Python (psycopg2) 中高效导入 DataFrame：

import io
import psycopg2

conn = psycopg2.connect("...")
cursor = conn.cursor()

buffer = io.StringIO()
df.to_csv(buffer, index=False, header=False)
buffer.seek(0)

cursor.copy_from(buffer, 'my_table', sep=',', null='')
conn.commit()

6. 性能优化要点

1. 移除索引和约束：大批量导入前，先 DROP 索引、外键，导入后重建。重建一索引比逐行维护快一个数量级。
2. 增大校验点距离：临时调高 max_wal_size，减少 WAL 日志冲刷。
3. 使用 UNLOGGED 表：如果允许在崩溃后丢失数据，可将目标表改为 UNLOGGED，导入完再改回 LOGGED。
4. 并行化：先按范围或哈希拆分大文件，再启动多个 COPY 会话同时导入到同一张表（适用于数据无相互依赖且已移除外键）。
5. 选择合适的分隔符：避免使用数据中频繁出现的字符，减少转义开销。

7. 安全权限速查

常见问题排查

“could not open file ... for reading: Permission denied”

• 检查文件路径是绝对路径且存在于服务器端。
• 使用 \copy 替代 SQL COPY，从客户端处理文件。

“extra data after last expected column”

• 导出的 CSV 表头与表列数不匹配，或数据行多余分隔符。检查字段内容中是否有未转义的分隔符。

“missing data for column ...”

• 导入时 CSV 行末尾缺少列，或使用 NULL '' 时误将空字符串当作空值。可尝试用 FORCE_NOT_NULL 保持空字符串而不是转为 NULL。

掌握 COPY 命令后，你就能以原始吞吐量在 PostgreSQL 和外部文件之间搬运数据，轻松实现 TB 级数据的快速导入导出。