Finding and Removing Duplicates

Define the key, choose the survivor

First define what makes rows duplicates (here, the email ignoring case) and inspect them with GROUP BY ... HAVING count(*) > 1. Then keep one survivor per key, usually the earliest or most complete row, using ROW_NUMBER() OVER (PARTITION BY key ORDER BY preference) and delete rows with rn > 1. Add a unique constraint (here a unique index on lower(email)) afterwards so duplicates cannot return.

Deduplicating sign-ups by email, run

I ran this with psql on PostgreSQL 16.2 against the course sample data (shown in the first section). Two keys are duplicated; the later rows 3 and 5 are deleted (RETURNING shows them), leaving one row per email.

SELECT lower(email) AS email, count(*) FROM signups
GROUP BY lower(email) HAVING count(*) > 1 ORDER BY 1;

DELETE FROM signups WHERE id IN (
  SELECT id FROM (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY lower(email) ORDER BY created, id) AS rn
    FROM signups) t
  WHERE rn > 1)
RETURNING id, email;

SELECT id, email FROM signups ORDER BY id;

Output:

  email  | count 
---------+-------
 a@x.com |     2
 b@x.com |     2
(2 rows)

 id |  email  
----+---------
  3 | A@x.com
  5 | b@x.com
(2 rows)

 id |  email  
----+---------
  1 | a@x.com
  2 | b@x.com
  4 | c@x.com
(3 rows)

Preview in a transaction

Run the DELETE inside BEGIN, check RETURNING and counts, then COMMIT or ROLLBACK.

Quick check: What prevents duplicates from reappearing after cleanup?

• A unique constraint or unique index on the key
• An ORDER BY clause
• A view
• Running the cleanup weekly only