PostgreSQL：\copy方法输入有效条目并丢弃异常 [英] postgresql: \copy method enter valid entries and discard exceptions

问题描述

输入以下命令时：

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

我收到以下错误：

ERROR: duplicate key value violates unique constraint "mmcompany_phonenumber_key"

I了解为什么会发生这种情况，但是如何以插入有效条目的方式执行命令，而丢弃产生错误的条目呢？

I understand why it's happening, but how do I execute the command in a way that valid entries will be inserted and ones that create an error will be discarded?

推荐答案

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

The reason PostgreSQL doesn't do this is related to how it implements constraints and validation. When a constraint fails it causes a transaction abort. The transaction is in an unclean state and cannot be resumed.

可以为每一行创建一个新的子事务，但这非常慢，并且无法使用<首先是code> COPY ，因此PostgreSQL在 COPY 中不支持它。您可以在PL / PgSQL中通过 LOOP 中的 BEGIN ... EXCEPTION 块自己完成操作将数据复制到临时表中。

It is possible to create a new subtransaction for each row but this is very slow and defeats the purpose of using COPY in the first place, so it isn't supported by PostgreSQL in COPY at this time. You can do it yourself in PL/PgSQL with a BEGIN ... EXCEPTION block inside a LOOP over a select from the data copied into a temporary table. This works fairly well but can be slow.

最好在可能的情况下使用SQL检查约束，然后再执行任何违反约束的插入操作。这样，您就可以：

It's better, if possible, to use SQL to check the constraints before doing any insert that violates them. That way you can just:

CREATE TEMPORARY TABLE stagingtable(...);

\copy stagingtable FROM 'somefile.csv'

INSERT INTO realtable
SELECT * FROM stagingtable
WHERE check_constraints_here;

但是请记住并发问题。如果您尝试通过 COPY 进行合并/上载，则必须 LOCK TABLE可实现； 在交易开始时，否则您仍然有可能出错。看来这就是您要执行的操作-副本（如果不存在）。如果是这样，跳过错误绝对是错误的方法。请参阅：

Do keep concurrency issues in mind though. If you're trying to do a merge/upsert via COPY you must LOCK TABLE realtable; at the start of your transaction or you will still have the potential for errors. It looks like that's what you're trying to do - a copy if not exists. If so, skipping errors is absolutely the wrong approach. See:

• 如何进行UPSERT（合并，在PostgreSQL中插入...（在重复更新时插入）？


• 插入，重复在PostgreSQL中更新？


• Postgresql-如果记录没有插入记录的干净方法不存在，请进行更新


• 可以将COPY与函数一起使用吗？


• 跳过行的Postgresql csv导入

• 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

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

... this is a much-discussed issue.

这篇关于PostgreSQL：\copy方法输入有效条目并丢弃异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！