由 John Doe 十二月 20, 2024
摘要：在本文中，我们将会学习，在 PostgreSQL 中使用 COPY FROM 时，如何跳过出现类型转换错误的行。
目录
介绍
PostgreSQL 17 发布了一项很棒的特性。你可能已经知道 COPY 了。每当你想在 PostgreSQL 中导入或导出某些内容时，它就是要使用到的工具。在导入到 PostgreSQL 时，直到 PostgreSQL 16 都需要考虑一些事情，这可能非常烦人：每当 COPY 时有数据行无法写入到目标表时，整个过程会被中止，并且根本不会导入任何内容。也许这是你想要的，但对于其他场景，首选行为是忽略这些行，并将错误消息输出到其他位置。在这两种情况下，您都不会丢失其余数据，并且在修复要导入的数据后不需要从头开始。
示例
像往常一样，让我们从一个小的测试用例开始：假设我们有这样一个文本文件：
$ cat data.txt
1 aaaa 1 aaaa
2 bbbb 2 bbbb
3 cccc 3 cccc
4 dddd 4 dddd
5 eeee 5 eeee
6 ffff 6 ffff
7 gggg 7 gggg
8 hhhh 8 hhhh
可以保存这些数据的表，可能如下所示：
CREATE TABLE import (a int, b varchar(4), c int, d varchar(4));
postgres=# \d import
Table "public.import"
Column | Type | Collation | Nullable | Default
--------+----------------------+-----------+----------+---------
a | integer | | |
b | character varying(4) | | |
c | integer | | |
d | character varying(4) | | |
要将这些数据从文本文件导入到表中，您需要做的就是：
COPY import FROM '/home/postgres/data.txt' with (delimiter ' ');
SELECT * FROM import;
a | b | c | d
---+------+---+------
1 | aaaa | 1 | aaaa
2 | bbbb | 2 | bbbb
3 | cccc | 3 | cccc
4 | dddd | 4 | dddd
5 | eeee | 5 | eeee
6 | ffff | 6 | ffff
7 | gggg | 7 | gggg
8 | hhhh | 8 | hhhh
(8 rows)
这很简单。如果要导入的文件确实如下面这样，则可能会出现问题：
TRUNCATE import;
$ cat data.txt2
1 aaaa 1 aaaa
2 bbbb 2 bbbb
3 cccc 3 cccc
4 dddd 4 dddd
5 eeee 5 eeee
6 ffff 6 ffff
7 gggg 7 gggg
8 hhhh 8 hhhh
9 jjjjjjjjj 9 jjjj
10 kkkk 10 kkkk
11 llll 11 lllllll
12 mmmm 12 mmmm
13 nnnn 13 nnnn
第 9 行和第 11 行肯定不能放入表中，当您尝试导入它时，整个过程会失败，并且根本不会导入任何内容：
COPY import FROM '/home/postgres/data.txt2' with (delimiter ' ');
ERROR: value too long for type character varying(4)
CONTEXT: COPY import, line 9, column b: "jjjjjjjjj"
SELECT * FROM import;
a | b | c | d
---+---+---+---
(0 rows)
从 PostgreSQL 17 开始，您将有另一个选择：
postgres=# \h copy
Command: COPY
Description: copy data between a file and a table
Syntax:
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 [, ...] ) ]
where option can be one of:
FORMAT format_name
FREEZE [ boolean ]
DELIMITER 'delimiter_character'
NULL 'null_string'
DEFAULT 'default_string'
HEADER [ boolean | MATCH ]
QUOTE 'quote_character'
ESCAPE 'escape_character'
FORCE_QUOTE { ( column_name [, ...] ) | * }
FORCE_NOT_NULL { ( column_name [, ...] ) | * }
FORCE_NULL { ( column_name [, ...] ) | * }
ON_ERROR error_action
ENCODING 'encoding_name'
LOG_VERBOSITY verbosity
使用选项 “ON_ERROR”，你可以要求 PostgreSQL 忽略任何不能放入目标表的数据：
COPY import FROM '/home/postgres/data.txt2' with (delimiter ' ', ON_ERROR ignore);
SELECT * FROM import;
a | b | c | d
----+------+----+------
1 | aaaa | 1 | aaaa
2 | bbbb | 2 | bbbb
3 | cccc | 3 | cccc
4 | dddd | 4 | dddd
5 | eeee | 5 | eeee
6 | ffff | 6 | ffff
7 | gggg | 7 | gggg
8 | hhhh | 8 | hhhh
10 | kkkk | 10 | kkkk
12 | mmmm | 12 | mmmm
13 | nnnn | 13 | nnnn
(11 rows)
除了第 9 行和第 11 行，数据都在表里了。现在，您只能忽略错误，或者在第一个错误的地方停止（这是默认值，和以前一样）。