sql - postgresql:\copy 方法输入有效条目并丢弃异常

Question

当输入以下命令时:

\copy mmcompany from '<path>/mmcompany.txt' delimiter ',' csv;

我收到以下错误:

ERROR: duplicate key value violates unique constraint "mmcompany_phonenumber_key"

我明白为什么会这样，但我如何以插入有效条目并丢弃产生错误的条目的方式执行命令？

Answer 1

PostgreSQL 不这样做的原因与其实现约束和验证的方式有关。当约束失败时，它会导致事务中止。事务处于不干净状态，无法恢复。

可以为每一行创建一个新的子事务，但这非常慢并且首先违背了使用 COPY 的目的，因此 中的 PostgreSQL 不支持它此时COPY。您可以在 PL/PgSQL 中使用 LOOP 中的 BEGIN ... EXCEPTION block 自行执行此操作，以从复制到临时表中的数据中进行选择。这工作得很好，但可能很慢。

如果可能的话，最好在执行任何违反约束的插入之前使用 SQL 检查约束。这样你就可以:

CREATE TEMPORARY TABLE stagingtable(...);

\copy stagingtable FROM 'somefile.csv'

INSERT INTO realtable
SELECT * FROM stagingtable
WHERE check_constraints_here;

不过请记住并发问题。如果您尝试通过 COPY 进行合并/更新插入，您必须 LOCK TABLE realtable; 在交易开始时，否则您仍然会有可能出错。看起来这就是您要尝试执行的操作 - 如果不存在则复制。如果是这样，跳过错误绝对是错误的做法。见:

• How to UPSERT (MERGE, INSERT ... ON DUPLICATE UPDATE) in PostgreSQL?
• Insert, on duplicate update in PostgreSQL?
• Postgresql - Clean way to insert records if they don't exist, update if they do
• Can COPY be used with a function?
• Postgresql csv importation that skips rows

...这是一个被广泛讨论的问题。