sqlpostgresql

Delete duplicate rows from small table

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

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