Delete duplicate rows from small table

sql postgresql duplicates
André Morujão picture André Morujão · Jul 5, 2011 · Viewed 90.9k times · Source

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

rapimo picture rapimo · Oct 18, 2012

A faster solution is

DELETE FROM dups a USING (
      SELECT MIN(ctid) as ctid, key
        FROM dups 
        GROUP BY key HAVING COUNT(*) > 1
      ) b
      WHERE a.key = b.key 
      AND a.ctid <> b.ctid

Related questions

How do I (or can I) SELECT DISTINCT on multiple columns?

I need to retrieve all rows from a table where 2 columns combined are all different. So I want all the sales that do not have any other sales that happened on the same day for the same price. The sales …

Read More
How to find duplicate records in PostgreSQL

I have a PostgreSQL database table called "user_links" which currently allows the following duplicate fields: year, user_id, sid, cid The unique constraint is currently the first field called "id", however I am now looking to add a constraint …

Read More
How to delete duplicate rows without unique identifier

I have duplicate rows in my table and I want to delete duplicates in the most efficient way since the table is big. After some research, I have come up with this query: WITH TempEmp AS ( SELECT name, ROW_NUMBER() …

Read More