Categories
Databases

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
WHERE ctid NOT IN (
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.