CODEWITHSUNDEEP
sqlpostgresqlpostgresql-10
Alfred Balle
Alfred Balle

Reputation: 1195

Delete duplicates from PostgreSQL

I'm using the following query in PostgreSQL 10 to find dublicate entries:

select
  column1, column2, count(*)
from mytable
  where column3 in ('yes', 'no')
group by column1, column2 having count(*) > 2;

Is it possible to have PostgreSQL delete the dublicates except of course the first of each entry?

Upvotes: 0

Views: 33

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

Assuming your table has a primary key:

delete from mytable t
    where t.pk <> (select min(t2.pk)
                   from mytable t2
                   where t2.column1 = t.column1 and
                         t2.column2 = t.column2 and
                         t2.column3 in ('yes', 'no')
                  );

Upvotes: 1

Related Questions