How to remove duplicate rows in SQL (works with Postgres)

Note to self more than anything, this snippet was handy for to remove duplicate rows in SQL, that crept into a Postgres database as a result of a dodgy join.

There are probably more than a couple of ways of doing this but the best example I came up with after reading around is:

DELETE FROM table_name
SELECT min(ctid)
FROM table_name
GROUP BY field1, field2);

The reason this works revolves around the ctid field. This is a default field you don’t have to specify that exists everywhere, so you can use it to remove duplicate rows (which are identified here by duplicates/groups created with the fields field1 and field2).

Useful references:

File:PostgreSQL logo.3colors.120x120.png
The excellent PostgreSQL logo!

By Tom Bush

Hi, my name is Tom Bush and this is my site. Welcome :)

Leave a Reply

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.