Delete Duplicate Rows and Keep One

You found duplicates and you want one copy of each to survive. The risk is deleting the wrong copy, or all of them. This page numbers the copies, shows you what would be removed, deletes inside a transaction you can undo, and then adds the rule that stops it happening again. It keeps the oldest row. If you want the newest, the change is one word, and it is pointed out below.

Step 1: a table with duplicates

CREATE TABLE signups (
  signup_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email     text NOT NULL,
  name      text NOT NULL
);
INSERT INTO signups (email, name) VALUES
  ('ana@example.com',   'Ana Reyes'),
  ('ben@example.com',   'Ben Cruz'),
  ('Ana@example.com',   'Ana R.'),
  ('carla@example.com', 'Carla Diaz'),
  ('ben@example.com',   'Ben Cruz'),
  ('ANA@example.com',   'Ana Reyes');

CREATE TABLE
INSERT 0 6

Two people appear more than once, and Ana appears three times, with different capital letters. Decide first what "the same" means. Here an email address is the same regardless of capitals, so the rule is lower(email).

Step 2: number the copies and preview

row_number() numbers the rows inside each group. The group is the rule you chose, and the order inside it decides which copy is number 1, the one that stays.

SELECT signup_id, email,
       row_number() OVER (PARTITION BY lower(email) ORDER BY signup_id) AS copy_number
FROM signups
ORDER BY lower(email), signup_id;

 signup_id |       email       | copy_number
-----------+-------------------+-------------
         1 | ana@example.com   |           1
         3 | Ana@example.com   |           2
         6 | ANA@example.com   |           3
         2 | ben@example.com   |           1
         5 | ben@example.com   |           2
         4 | carla@example.com |           1
(6 rows)

Every row with a copy_number above 1 is an extra. To keep the newest copy instead, order by signup_id DESC. Now list only the rows that would go.

SELECT signup_id, email, name
FROM (
  SELECT signup_id, email, name,
         row_number() OVER (PARTITION BY lower(email) ORDER BY signup_id) AS copy_number
  FROM signups
) AS numbered
WHERE copy_number > 1
ORDER BY signup_id;

 signup_id |      email      |   name
-----------+-----------------+-----------
         3 | Ana@example.com | Ana R.
         5 | ben@example.com | Ben Cruz
         6 | ANA@example.com | Ana Reyes
(3 rows)

Three rows would be removed: two copies of Ana and one of Ben. Read this list before you delete anything. If a row is on it that should not be, fix the rule here.

Step 3: delete inside a transaction

Use the same subquery to pick the ids, and wrap the delete in a transaction so you can check the result first.

BEGIN;
DELETE FROM signups
WHERE signup_id IN (
  SELECT signup_id FROM (
    SELECT signup_id,
           row_number() OVER (PARTITION BY lower(email) ORDER BY signup_id) AS copy_number
    FROM signups
  ) AS numbered
  WHERE copy_number > 1
);
SELECT signup_id, email FROM signups ORDER BY signup_id;

BEGIN
DELETE 3
 signup_id |       email
-----------+-------------------
         1 | ana@example.com
         2 | ben@example.com
         4 | carla@example.com
(3 rows)

DELETE 3 matches the preview, and three rows are left, one for each person. If the count or the rows were not what you expected, run ROLLBACK; and nothing has changed. If they are right, finish it.

COMMIT;

COMMIT

Step 4: stop it coming back

A unique index on the same rule makes the database refuse the next duplicate.

CREATE UNIQUE INDEX signups_email_lower_key ON signups (lower(email));
INSERT INTO signups (email, name) VALUES ('BEN@example.com', 'Ben C.');

CREATE INDEX
ERROR:  duplicate key value violates unique constraint "signups_email_lower_key"
DETAIL:  Key (lower(email))=(ben@example.com) already exists.

The index must be created after the clean-up. It would fail if duplicates were still there.

If the table has no id column

Without a key there is nothing to point at. PostgreSQL has a hidden physical row address, ctid, that you can use for a one-time clean-up. This is PostgreSQL-specific, and the address can change when the table is rewritten, so use it only inside one transaction.

CREATE TABLE raw_import (email text, name text);
INSERT INTO raw_import VALUES ('a@example.com', 'A'), ('b@example.com', 'B'), ('a@example.com', 'A');
DELETE FROM raw_import
WHERE ctid IN (
  SELECT ctid FROM (
    SELECT ctid, row_number() OVER (PARTITION BY email, name) AS copy_number FROM raw_import
  ) AS numbered
  WHERE copy_number > 1
);
SELECT * FROM raw_import ORDER BY email;

CREATE TABLE
INSERT 0 3
DELETE 1
     email     | name
---------------+------
 a@example.com | A
 b@example.com | B
(2 rows)

Related course: SQL Intermediate.