PostgreSQL删除重复项 [英] PostgreSQL Removing duplicates

问题描述

我正在研究postgres查询，以从表中删除重复项。下表是动态生成的，我想编写一个选择查询，如果第一行具有重复值，它将删除记录。

I am working on postgres query to remove duplicates from a table. The following table is dynamically generated and I want to write a select query which will remove the record if the first row has duplicate values.

该表看起来像这样

Ist col  2nd col
 4        62
 6        34
 5        26
 5        12

我想编写一个选择查询，删除第3行或第4行。

I want to write a select query which remove either row 3 or 4.

推荐答案

不需要中间表：

delete from df1
where ctid not in (select min(ctid)
                   from df1
                   group by first_column
                   having count(*) > 1);

如果要从大表中删除许多行，则使用中间表的方法可能会更快。

If you are deleting many rows from a large table, the approach with an intermediate table is probably faster.

如果只想获取一列的唯一值，则可以使用：

If you just want to get unique values for one column, you can use:

select distinct on (first_column) *
from the_table
order by the_table;

或者简单地

select first_column, min(second_column)
from the_table
group by first_column;

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