Delete Duplicate Records in PostgreSQL

Question

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 1

Try the code given below:

DELETE FROM dupes a

WHERE a.ctid <> (SELECT min(b.ctid)

                 FROM   dupes b

                 WHERE  a.key = b.key);