如何删除两列中完全匹配的重复行

Question

PostgreSQL 12中的表text_table只有两列：text1和text2
text1列上有一个idx_text1索引。

例如：

text1    text2
----------------------
1   str1     one
2   str2     two
3   str3     three
4   str2     two
5   str1     seven
6   str4     seven
7   str3     three

如何删除所有重复的行(两列中的值完全匹配(？

在上面的示例中，需要从表中删除行4和7(或2与3(
我想找到SQL查询的最快方法。

Answer 1

如果没有主键，这样的任务是乏味的。我认为最简单的方法可能是备份/恢复，就像这样：

create table tmptable as select distinct text1, text2 from mytable;
truncate table mytable;  -- back it up first!!
insert into mytable select * from tmptable;
drop table tmptable;

Answer 2

最佳性能取决于表的大小、并发活动、行数和平均大小，最重要的是要删除的行数。

对于只有少数重复项，没有NULL值(或者你不考虑这些重复项(，也没有PRIMARY KEY，这就可以了：

DELETE FROM text_table t
WHERE  EXISTS (
SELECT FROM  text_table 
WHERE (text1, text2) = (t.text1, t.text2)
AND   ctid < t.ctid  -- exclude self
-- AND   pk < t.pk      -- alternative with PK
);

在重复项中，这将保留ctid最小的行(物理上是第一个(。(或者使用alt.语法的PK值最小的一个。(

关于ctid:

• Postgresql分组方式为多行
• 如何将ctid分解为页码和行号

(text1)上的索引应该会有所帮助。(text1, text2)上的索引通常会有更多帮助，除非字符串很长。(OTOH，当删除相当大比例的所有行时，索引可能产生的成本大于收益。(

并考虑在删除重复项后添加UNIQUE索引或约束，以防止再次引入更多索引或约束。

相关：

• 如何(或可以(在多列上选择DISTINCT
• 如何删除没有唯一标识符的重复行
• 消除PostgreSQL SELECT语句中的重复行