复制CSV到PostgreSQL时忽略违反约束的行。

Question

我有一个包含 ~300,000 行的 .csv 文件，其中一些行违反了我在 postgres 数据库中设置的某些约束条件。是否有一种方法可以将我的 .csv 文件复制到数据库中，并让 postgres 过滤掉违反约束条件的行？我不希望这些行出现在数据库中。

如果这不可行，还有其他解决方法吗？

我目前正在做的事情是：

COPY blocksequences from '/tmp/blocksequences.csv CSV HEADER;

我理解了

'ERROR:  new row for relation "blocksequences" violates check constraint "blocksequences_partid3_check"
DETAIL:  Failing row contains (M001-M049-S186, M001, null, M049, S186).
CONTEXT:  COPY blocksequences, line 680: "M001-M049-S186,M001,,M049,S186"

错误原因：包含M049的列不允许输入该字符串。许多其他行也存在类似违规情况。

我了解到一些关于“检查违规时异常-什么都不做”的内容，这是正确的方向吗？似乎只是MySQL的事情。

- Beeba

---

这似乎是个好主意，但我在想如何删除不符合约束条件的行？我的想法是不想强制删除，因为有太多的行。 - Beeba

哦，当然！我太蠢了，没想到这个……谢谢。如果你写一个答案，我会接受它。 - Beeba

Answer 1

通常这样做：

• 创建一个和目标表具有相同结构但没有约束条件的临时表，
• 使用COPY命令将数据复制到临时表中，
• 使用INSERT命令将满足表约束条件的行从临时表复制到目标表中，并在WHERE子句中加入条件，
• 删除临时表。

当处理非常大的CSV文件或者服务器资源非常有限时，可以使用file_fdw扩展代替临时表。它是一种更高效的方式，但需要访问CSV文件（而将数据复制到临时表可以通过网络完成）。

在Postgres 12中，您可以在COPY FROM命令中使用WHERE子句。

- klin