Removing duplicate rows in PostgreSQL and Oracle

Oracle trick:

DELETE FROM your_table
WHERE rowid not in
(SELECT MIN(rowid)
FROM your_table
GROUP BY column1, column2, column3);

Postgresql equivalent is ctid, so you can try something like

delete from mytable
    where exists (select 1
                  from mytable t2
                  where t2.name = mytable.name and
                        t2.address = mytable.address and
                        t2.zip = mytable.zip and
                        t2.ctid > mytable.ctid
                 );

Also take a look at this old post on postgresql mailing list, if you incur in an older PostgreSQL version 

Leave a Reply