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 Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.