André Morujão André Morujão - 2 months ago 6
SQL Question

Delete completely duplicate rows in PostgreSQL (and keep only 1)

I have a table in a PostgreSQL 8.3.8 database, which has no keys/constraints on it, and has multiple rows with exactly the same values.

I would like to remove all duplicates and keep only 1 copy of each row.

There is one column in particular (named "key") which may be used to identify duplicates (i.e. there should only exist one entry for each distinct "key").

How can I do this? (ideally with a single SQL command)
Speed is not a problem in this case (there are only a few rows).

Answer
DELETE FROM dupes a
WHERE a.ctid <> (SELECT min(b.ctid)
                 FROM   dupes b
                 WHERE  a.key = b.key);
Comments