André Morujão André Morujão - 1 year ago 71
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 Source
WHERE a.ctid <> (SELECT min(b.ctid)
                 FROM   dupes b
                 WHERE  a.key = b.key);
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download