由 John Doe 四月 17, 2025
导入数据时可能出现错误，在错误行数可接受的范围内，如何跳过错误行？现在，PostgreSQL 为 COPY 命令添加了 REJECT_LIMIT 选项。
特性提交日志
为 COPY 命令添加 REJECT_LIMIT 选项。
此前，当ON_ERROR设置为'ignore'时，COPY命令会跳过所有存在数据类型转换错误的行，在操作失败前无法限制跳过行的数量。
本次提交引入了REJECT_LIMIT选项，允许用户指定可跳过的错误行的最大数量。如果遇到数据类型转换错误的行数超过了REJECT_LIMIT所允许的数量，即使ON_ERROR = 'ignore'，COPY命令也会失败并报错。
讨论：https://postgr.es/m/63f99327aa6b404cc951217fa3e61fe4@oss.nttdata.com
示例
在 PostgreSQL 17 版本中，COPY 命令新增了一个 “ON_ERROR” 选项。当你加载的数据中包含无法插入到目标表中的行时，这个选项非常有用。在此之前，COPY 命令一旦遇到此类情况就会停止并报错。唯一的解决办法是先修正数据，然后重新执行 COPY 操作。而使用 “ON_ERROR” 选项后，可以让 COPY 命令在遇到错误数据行时继续加载，并忽略这些数据行。
默认情况下，COPY 命令仍然会在遇到无法加载的行时立即停止：
create table t ( a int, b int, c text );
$ cat load.txt
1,1,'aaa'
2,2,'bbb'
3,3,'ccc'
4,four,'ddd'
5,5,'eee'
6,6,'fff'
7,7,'ggg'
8,eight,'hhh'
9,9,'iii'
copy t from '/home/postgres/load.txt' with ( delimiter ',');
ERROR: invalid input syntax for type integer: "four"
CONTEXT: COPY t, line 4, column b: "four"
从 PostgreSQL 17 开始，我们可以让 COPY 命令忽略这些错误行，并继续加载数据：
copy t from '/home/postgres/load.txt' with ( delimiter ',', on_error ignore);
COPY 7
select * from t;
a | b | c
---+---+-------
1 | 1 | 'aaa'
2 | 2 | 'bbb'
3 | 3 | 'ccc'
5 | 5 | 'eee'
6 | 6 | 'fff'
7 | 7 | 'ggg'
9 | 9 | 'iii'
(7 rows)
在 PostgreSQL 17 及之前的版本中，我们无法为错误数据设置一个限制。但从 PostgreSQL 18 开始，这将成为可能，因为 COPY 命令新增了一个 “REJECT_LIMIT” 选项。
使用这个选项，我们可以指定当遇到 n 条无法加载的行时，就停止 COPY 操作：
truncate t;
copy t from '/home/postgres/load.txt' with ( delimiter ',', on_error ignore, reject_limit 1);
ERROR: skipped more than REJECT_LIMIT (1) rows due to data type incompatibility
CONTEXT: COPY t, line 8, column b: "eight"
copy t from '/home/postgres/load.txt' with ( delimiter ',', on_error ignore, reject_limit 2);
ERROR: skipped more than REJECT_LIMIT (2) rows due to data type incompatibility
CONTEXT: COPY t, line 10, column a: ""
copy t from '/home/postgres/load.txt' with ( delimiter ',', on_error ignore, reject_limit 3);
COPY 7
select * from t;
a | b | c
---+---+-------
1 | 1 | 'aaa'
2 | 2 | 'bbb'
3 | 3 | 'ccc'
5 | 5 | 'eee'
6 | 6 | 'fff'
7 | 7 | 'ggg'
9 | 9 | 'iii'
(7 rows)
这非常不错，在处理无法插入到目标表的数据时，它让我们拥有了更多的控制权。
非常不错的体验。感谢社区的所有相关人员。
参考
提交日志：https://git.postgresql.org/pg/commitdiff/4ac2a9beceb10d44806d2cf157d5a931bdade39e