Removing duplicate rows in PostgreSQL and Oracle

Oracle trick:

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

Postgresql equivalent is ctid, so you can try something like

[sql]
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
);
[/sql]

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

Leave a Reply