# COPY

> 使用服务端 COPY 和客户端 psql copy 命令导入和导出 CSV 数据。

---

LLMS 索引： [llms.txt](/llms.txt)

---

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

## 本章要点 {#learn}

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

## 示例 {#example}

```sql {num="57.1" caption="COPY"}
-- 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` 会在目标表上调用触发器，并强制执行检查约束和外键约束。触发器的工作因此会计入装载成本。它不会调用规则。暂存表适合在将数据插入最终表之前，对装载内容进行验证和转换。

## 运行示例 {#run}

运行方式：

```text
$ 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
```

## 常见陷阱 {#pitfalls}

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

> [!TIP] 提示
> 对于非常大的导入，在 `COPY` 前删除索引（主键除外），然后重新创建。这样 `COPY` 会更快，而且最后一次性建立索引，比在数百万行上逐行维护索引更快。将操作包在一个事务中，这样失败时就能回滚。

> [!NOTE] 动手练习
> 将 `fruits` 导出到文件：`psql -f source/copy.sql postgres > out.csv`。然后将它重新导入暂存表：在一个 psql 会话中创建 `fruits_stage`，再单独占一行运行 `\copy`：
>
> ```sql
> 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;
> ```
>
> 比较行数。

## 下载 {#downloads}

SQL 文件：[copy.sql](/examples/source/copy.sql) · [全部示例文件](/examples/examples.zip) · [准备工作](/#prerequisites)

[返回目录](/)