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 = and
t2.address = mytable.address and = 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