如何删除表中的重复项? [英] How to remove duplicates in a table?

问题描述

CREATE join_table {
  id1 integer,
  id2 integer
}

我想创建一个 UNIQ CONSTRAINT(id1, id2)，但是，我看到了一些不好的数据，例如:

I want to create a UNIQ CONSTRAINT(id1, id2), however, I am seeing some bad data such as this:

id1   | id2
------------
1     | 1
1     | 1
1     | 2

因此，记录 (1,1) 显然是重复的，并且会违反 uniq 约束.我如何编写一个 sql 查询来删除表中的所有重复记录.

So, the record (1,1) is clearly a duplicate, and will violate uniq constraint. How do I write a sql query which will remove all duplicate records from the table.

注意:我想删除其中一个重复项，以便我可以创建 uniq 约束

Note: I want to delete one of the duplicates so that I can create the uniq constraint

推荐答案

这将保留其中一个重复项:

This will keep one of the duplicates:

delete from join_table
where ctid not in (select min(ctid)
                   from join_table
                   group by id1, id2);

您的表没有可用于挑选一名幸存者"的唯一标识符.这就是 Postgres 的 ctid 派上用场的地方，因为它是每一行的内部唯一标识符.请注意，您不应将 ctid 用于多个语句.它不是一个普遍独特的东西，但对于单个语句的运行时来说就好了.

Your table doesn't have a unique identifier that could be used to "pick one survivor". That's where Postgres' ctid comes in handy, as it is an internal unique identifier for each row. Note that you should never use the ctid for more than just a single statement. It is not a universally unique things but for the runtime of a single statement it's just fine.

SQLFiddle 示例:http://sqlfiddle.com/#!15/dabfc/1

SQLFiddle example: http://sqlfiddle.com/#!15/dabfc/1

如果您想删除所有重复的行:

If you want to get rid of all rows that are duplicated:

delete from join_table
where (id1, id2) in (select id1, id2
                     from join_table
                     group by id1, id2
                     having count(*) > 1);

在一张大桌子上，这两种解决方案都不会很快.如果您需要大表中的大量行，创建一个没有重复项的新表(如 jjanes 所示)会更快.

Neither solution will be fast on a large table. Creating a new table without duplicates as jjanes has shown will be much faster if you need a substantial number of rows from a large table.

这篇关于如何删除表中的重复项?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！