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 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.