防止在复制CSV到PostgreSQL时出现重复数据的最佳方法

Question

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

我有一个postgresql / postgis表格，它有5列。 我将每隔10分钟左右通过复制命令从csv文件向数据库插入/添加数据。 由于可能会有一些重复的数据行，因此我希望将数据从csv文件复制到postgresql表格中，但是防止来自csv文件的任何重复条目进入表格。 如果这三个列"纬度"，"经度"和"时间"都相等，那么就意味着该条目是重复的。 我应该从所有三列中构建组合键吗？ 如果我这样做，尝试将csv文件复制到数据库时是否只会出现错误？我将自动复制csv文件，因此我希望它可以继续复制其余不是重复项的文件，并且不要复制重复项。 有没有方法可以做到这一点？

另外，当然，我希望以最有效的方式查找重复项。 我不需要在整个表格（将非常大）中查找重复项...只需通过行上的时间戳查找过去约20分钟即可。 我已使用时间列索引了数据库。

感谢您的任何帮助！

- user1610717

Answer 1

Upsert

Linoff的回答是正确的，但通过Postgres 9.5新的"UPSERT"特性（也称为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
;

- Basil Bourque

---

非常感谢您提供这个更新后的答案！我会尽力将其整合到数据库中。 - user1610717

Answer 2

我会采用以下方法：

首先，在你关心的三个列上创建索引：

create unique index idx_bigtable_col1_col2_col3 on bigtable(col1, col2, col3);

然后，使用copy将数据加载到一个暂存表中。最后，您可以执行以下操作：

insert into bigtable(col1, . . . )
    select col1, . . .
    from stagingtable st
    where (col1, col2, col3) not in (select col1, col2, col3 from bigtable);

假设没有其他数据修改，这应该可以实现你想要的目标。使用索引检查重复项在性能方面应该是可行的。

另一种方法是模拟MySQL的“在重复键更新”以忽略这些记录。Bill Karwin在此问题的答案中建议实施规则。规则的文档在这里。类似的事情也可以通过触发器完成。

- Gordon Linoff

---

通过规则实现它确实可行，但我认为向初学者建议这样做是不好的主意。而且就性能而言，它不会带来任何优势，因为在内部它将有效地执行与硬编码版本相同的 SQL 步骤。 - wildplasser

@wildplasser……Bill Karwin的推荐确实让人难以拒绝。不过，我同意你的看法。 - Gordon Linoff

我非常感激这个回应。我期待着在近期测试它。很快我会回复结果。 - user1610717

@user1610717...你可能想再提出另一个问题。 - Gordon Linoff

抱歉，没意识到那个评论已经发送了。完美地运行了...谢谢！！ - user1610717

好的回答。似乎新的UPSERT功能适用于这个代码示例。因此，我发布了另一个答案，展示了替代语法。相同的结果，但更简单一些。 - Basil Bourque

Answer 3

Basil Bourque发布的方法很好，但是存在轻微的语法错误。

根据文档，我对其进行了修改，以下是有效的：

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

- Tharaka