postgresql - 防止复制 csv postgresql 上重复数据的最佳方法

Question

这更像是一个概念性问题，因为我正在计划如何最好地实现我们的目标。

我有一个包含 5 列的 postgresql/postgis 表。我将每 10 分钟左右通过复制命令将数据从 csv 文件插入/附加到数据库中。可能会有一些重复的数据行，所以我想将数据从 csv 文件复制到 postgresql 表，但要防止任何重复的条目从 csv 文件进入表。共有三列，如果它们都相等，则意味着该条目是重复的。它们是“纬度”、“经度”和“时间”。我应该从所有三列中制作一个复合键吗？如果我这样做，它会在尝试将 csv 文件复制到数据库时抛出错误吗？我将自动复制 csv 文件，所以我希望它继续复制文件的其余部分，而不是重复文件，而不是复制重复文件。有办法做到这一点吗？

此外，我当然希望它以最有效的方式查找重复项。我不需要通过整个表(这将是相当大的)来查找重复项......只需过去 20 分钟左右通过行上的时间戳。我已经用时间列为数据库编制了索引。

感谢您的帮助!

Answer 1

更新

Answer by Linoff是正确的，但可以通过 Postgres 9.5 new ”UPSERT“ feature 稍微简化一下(又名 MERGE )。该新功能在 Postgres 中实现为 INSERT ON CONFLICT语法。

我们可以让 ON CONFLICT 子句检测违规情况，而不是明确检查唯一索引的违规情况。然后我们DO NOTHING，这意味着我们放弃了 INSERT 的努力，而不必费心尝试 UPDATE。因此，如果我们不能插入，我们就移到下一行。

我们得到了与 Linoff 代码相同的结果，但丢失了 WHERE 子句。

INSERT INTO bigtable(col1, … )
    SELECT col1, …
    FROM stagingtable st
ON CONFLICT idx_bigtable_col1_col2_col
DO NOTHING
;