Delete duplicate rows from table with no unique key

Single SQL statement

Here is a solution that deletes duplicates in place:

DELETE FROM releases_labels r
WHERE  EXISTS (
   SELECT releases_labels r1
   WHERE  r1 = r
   AND    r1.ctid < r.ctid
   );

Since there is no unique key, I am (ab)using the tuple ID ctid for the purpose. The physically first row survives in each set of dupes.

• In-order sequence generation
• How do I (or can I) SELECT DISTINCT on multiple columns?

ctid is a system column that is not part of the associated row type, so when referencing the whole row with table aliases in the expression r1 = r, only visible columns are compared (not the ctid or others). That's why the whole row can be equal and one ctid is still smaller than the other.

With only few duplicates, this is also the fastest of all solutions.
With lots of duplicates, other solutions are faster.

Then I suggest:

ALTER TABLE discogs.releases_labels
ADD COLUMN releases_labels_id serial PRIMARY KEY;

Why does it work with NULL values?

The reason is explained in the chapter Composite Type Comparison in the manual:

The SQL specification requires row-wise comparison to return NULL if the result depends on comparing two NULL values or a NULL and a non-NULL. PostgreSQL does this only when comparing the results of two row constructors (as in Section 9.24.5) or comparing a row constructor to the output of a subquery (as in Section 9.23). In other contexts where two composite-type values are compared, two NULL field values are considered equal, and a NULL is considered larger than a non-NULL. This is necessary in order to have consistent sorting and indexing behavior for composite types.Bold emphasis mine.

You can try like this:

CREATE TABLE temp 
INSERT INTO temp SELECT DISTINCT * FROM discogs.releases_labels;
DROP TABLE discogs.releases_labels;
ALTER TABLE temp RENAME TO discogs.releases_labels;

As you have no primary key, there is no easy way to distinguish one duplicated line from any other one. That's one of the reasons why it is highly recommended that any table have a primary key (*).

So you are left with only 2 solutions :

• use a temporary table as suggested by Rahul (IMHO the simpler and cleaner way) (**)

• use procedural SQL and a cursor either from a procedural language such as Python or [put here your prefered language] or with PL/pgSQL. Something like (beware untested) :

  CREATE OR REPLACE FUNCTION deduplicate() RETURNS integer AS $$
  DECLARE
   curs CURSOR FOR SELECT * FROM releases_labels ORDER BY label, release_id, catno;
   r releases_labels%ROWTYPE;
   old releases_labels%ROWTYPE;
   n integer;
  BEGIN
   n := 0;
   old := NULL;
   FOR rec IN curs LOOP
    r := rec;
    IF r = old THEN
     DELETE FROM releases_labels WHERE CURRENT OF curs;
     n := n + 1;
    END IF;
    old := rec;
   END LOOP;
   RETURN n;
  END;
  $$ LANGUAGE plpgsql;
  
  SELECT deduplicate();
  

  should delete duplicate lines and return the number of lines actually deleted. It is not necessarily the most efficient way, but you only touch rows that need to be deleted so you will not have to lock whole table.

(*) hopefully PostgreSQL offers the ctid pseudo column that you can use as a key. If you table contains an oid column, you can also use it as it will never change.

(**) PostgreSQL WITH allows you to do that in in single SQL statement

This two points from answer from Nick Barnes

Since you also need to avoid duplicates in the future, you could add a surrogate key and a unique constraint while dedupping:

-- add surrogate key
ALTER TABLE releases_labels
        ADD column id SERIAL NOT NULL PRIMARY KEY
        ;

-- verify
SELECT * FROM releases_labels;

DELETE FROM releases_labels dd
WHERE EXISTS (SELECT *
        FROM releases_labels x
        WHERE x.label = dd.label
        AND x.release_id = dd.release_id
        AND x.catno = dd.catno
        AND x.id < dd.id
        );

-- verify
SELECT * FROM releases_labels;

-- add unique constraint for the natural key
ALTER TABLE releases_labels
        ADD UNIQUE (label,release_id,catno)
        ;

-- verify
SELECT * FROM releases_labels;