sql - 检查大表中的完整重复行

Question

我的原始问题和所有相关上下文可以在这里找到:
Adding a multi-column primary key to a table with 40 million records

我有一个包含 4000 万行且没有主键的表。在我添加主键之前，我想检查表是否有任何重复的条目。当我说重复条目时，我不仅仅指特定列上的重复项。我的意思是整行重复。

我在上一个问题中被告知我可以执行 EXISTS 查询来确定重复项。我该怎么做？

我正在运行 PostgreSQL 8.1.22。 (通过运行 select version() 获得此信息)。

Answer 1

要查找是否存在任何 完整重复项(在所有列上都相同)，这可能是最快的方法:

SELECT EXISTS (
    SELECT 1
    FROM   tbl t
    NATURAL JOIN tbl t1 
    WHERE  t.ctid <> t1.ctid
    )

NATURAL JOIN是这种情况的一种非常方便的速记，因为(引用 the manual here ):

NATURAL is shorthand for a USING list that mentions all columns in the two tables that have the same names.

EXISTS 可能是最快的，因为一旦找到第一个重复项，Postgres 就会停止搜索。由于您很可能没有覆盖整行的索引并且您的表很大，这将为您节省很多时间。

请注意 NULL 从不被认为与另一个相同NULL .如果你有 NULL值并认为它们相同，你必须做更多。

ctid is a system column可以(滥用)用作临时主键，但从长远来看不能替换实际的用户定义的主键。

---

过时的8.1版本好像没有<>为 ctid 定义的运算符.尝试转换到 text :

SELECT EXISTS (
    SELECT 1
    FROM   tbl t
    NATURAL JOIN tbl t1 
    WHERE  t.ctid::text <> t1.ctid::text
    )