Find Duplicate Rows

Duplicates get into tables when a form is submitted twice, a file is imported twice, or a rule was never written. Before you delete anything, find out what you have. This page finds the repeated values, shows the full rows, and catches the duplicates that differ only by capital letters. It does not delete. That is a separate step, and it needs more care.

Step 1: a table with duplicates

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

CREATE TABLE
INSERT 0 6

The table has no UNIQUE rule on email, so the database accepted every row.

Step 2: find the values that repeat

Group by the column that should be unique and keep the groups with more than one row.

SELECT email, count(*) AS copies
FROM subscribers
GROUP BY email
HAVING count(*) > 1;

      email      | copies
-----------------+--------
 ana@example.com |      3
(1 row)

ana@example.com appears three times. This is the list of values to investigate. Notice that Ben is not on it, and he is a duplicate too. That comes in Step 4.

Step 3: see the full rows

The ids and names are what you need to decide which copy to keep. Use the repeated values as a filter.

SELECT subscriber_id, name, email
FROM subscribers
WHERE email IN (
  SELECT email FROM subscribers GROUP BY email HAVING count(*) > 1
)
ORDER BY email, subscriber_id;

 subscriber_id |   name    |      email
---------------+-----------+-----------------
             1 | Ana Reyes | ana@example.com
             3 | Ana R.    | ana@example.com
             6 | Ana Reyes | ana@example.com
(3 rows)

The inner query finds the repeated emails, and the outer query lists every row that has one. Three rows share the address, and two of them have different names for the same person. Deciding which copy is right is a human question. The database can only tell you the rows are the same by the rule you chose.

Step 4: catch duplicates that differ by case

Email addresses are usually treated as equal regardless of capital letters. Group by the lower-case version to see them.

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

      email      | copies
-----------------+--------
 ana@example.com |      3
 ben@example.com |      2
(2 rows)

Now Ben appears too, because BEN@example.com and ben@example.com are the same address.

Step 5: a quick total

To see how many rows are "extra", compare the number of rows with the number of distinct values.

SELECT count(*) AS rows, count(DISTINCT lower(email)) AS distinct_emails FROM subscribers;

 rows | distinct_emails
------+-----------------
    6 |               3
(1 row)

Six rows and three distinct addresses means three rows are extra.

Related course: SQL for Beginners.