Duplicate multiple postgresql column entries

Question

I have a bunch of data in a postgresql database. I think the two keys should make up a unique pair, so you want to provide this in the database. I'm trying to

create unique index key1_key2_idx on table(key1,key2) 

but it doesn’t help telling me that I have duplicate entries.

How to find these duplicate entries so that I can delete them?

Answer 1

Assuming you want to remove duplicates and keep the original, the accepted answer is inaccurate - it will also delete your originals and save records that have one record from the very beginning. This works on 9.x:

 SELECT * FROM tblname WHERE ctid IN (SELECT ctid FROM (SELECT ctid, ROW_NUMBER() OVER (partition BY col1, col2, col3 ORDER BY ctid) AS rnum FROM tblname) t WHERE t.rnum > 1); 

https://wiki.postgresql.org/wiki/Deleting_duplicates

Answer 2

 select key1,key2,count(*) from table group by key1,key2 having count(*) > 1 order by 3 desc; 

The critical part of the query for identifying duplicates is having count(*) > 1 .

There are a whole bunch of neat tricks at the following link, including some examples of removing duplicates: http://postgres.cz/wiki/PostgreSQL_SQL_Tricks