The Definitive Guide to Handling Duplicates in PostgreSQL

The Scenario: A Problematic `users` Table

Imagine a users table containing duplicate entries based on id and email. Crucially, it also includes NULL values, which are a common source of errors in duplication logic.

-- Meticulously aligned for clarity
 id   | email           | name    | updated_at
------+-----------------+---------+------------
 1    | a@example.com   | Alice   | 2023-01-01
 2    | b@example.com   | Bob     | 2023-01-03
 1    | a@example.com   | Alicia  | 2023-01-04
 3    | c@example.com   | Charlie | 2023-01-01
 NULL | x@example.com   | Xavier  | 2023-01-01
 NULL | x@example.com   | Xav     | 2023-01-02

The Flawed Approach: Why `GROUP BY` and `IN` Fails

A common first attempt is to find duplicate keys with GROUP BY and then select them with an IN clause. This approach has a critical flaw.

SELECT * FROM users WHERE (id, email) IN (
  SELECT id, email FROM users
  GROUP BY id, email
  HAVING COUNT(*) > 1
);

-- Notice the rows with id = NULL are missing!
 id   | email         | name   | updated_at
------+---------------+--------+------------
 1    | a@example.com | Alice  | 2023-01-01
 1    | a@example.com | Alicia | 2023-01-04

The Correct Fix: Find Duplicates with `IS NOT DISTINCT FROM`

To correctly handle NULL values, use PostgreSQL’s NULL-safe comparison operator: IS NOT DISTINCT FROM. This treats two NULLs as equal.

WITH duplicate_keys AS (
  SELECT id, email FROM users
  GROUP BY id, email
  HAVING COUNT(*) > 1
)
SELECT u.* FROM users u
JOIN duplicate_keys d
  ON u.id IS NOT DISTINCT FROM d.id
 AND u.email IS NOT DISTINCT FROM d.email;

-- Success! All duplicate rows are correctly identified.
 id   | email         | name   | updated_at
------+---------------+--------+------------
 1    | a@example.com | Alice  | 2023-01-01
 1    | a@example.com | Alicia | 2023-01-04
 NULL | x@example.com | Xavier | 2023-01-01
 NULL | x@example.com | Xav    | 2023-01-02

Strategy 1: Keep the Latest Record with `ROW_NUMBER()`

This is the most flexible strategy for de-duplication. Use the ROW_NUMBER() window function to rank rows within each duplicate group and select only the one you want to keep.

WITH ranked_users AS (
  SELECT *, ROW_NUMBER() OVER (
    PARTITION BY id, email ORDER BY updated_at DESC
  ) AS rn
  FROM users
)
SELECT id, email, name, updated_at FROM ranked_users WHERE rn = 1;

-- A clean, de-duplicated set of data.
 id   | email         | name    | updated_at
------+---------------+---------+------------
 1    | a@example.com | Alicia  | 2023-01-04
 2    | b@example.com | Bob     | 2023-01-03
 3    | c@example.com | Charlie | 2023-01-01
 NULL | x@example.com | Xav     | 2023-01-02

Strategy 2: Prevent Duplicates with `ON CONFLICT`

The best long-term solution is to prevent duplicates from ever being inserted. This is achieved by adding a UNIQUE constraint and then using the PostgreSQL-specific ON CONFLICT clause.

-- Step 1: Enforce uniqueness at the database level.
ALTER TABLE users ADD CONSTRAINT users_id_email_key UNIQUE (id, email);

-- Step 2: Safely ignore any new rows that violate the constraint.
INSERT INTO users (...) VALUES (...)
ON CONFLICT (id, email) DO NOTHING;

-- Or, update the conflicting row (an "upsert").
INSERT INTO users (...) VALUES (...)
ON CONFLICT (id, email)
DO UPDATE SET name = EXCLUDED.name, updated_at = EXCLUDED.updated_at;

Strategy 3: The Standard SQL `MERGE` Statement

For more complex logic and better cross-database compatibility, the standard SQL MERGE statement (available in Postgres 15+) is the ultimate tool. It can perform inserts, updates, and even deletes in one atomic operation.

-- Assumes a staging_users table with new/updated data.
MERGE INTO users AS target
USING staging_users AS source
  ON target.id IS NOT DISTINCT FROM source.id
 AND target.email IS NOT DISTINCT FROM source.email
-- If a user exists and the new record is more recent, update it.
WHEN MATCHED AND source.updated_at > target.updated_at THEN
  UPDATE SET name = source.name, updated_at = source.updated_at
-- If the user does not exist, insert it.
WHEN NOT MATCHED THEN
  INSERT (id, email, name, updated_at)
  VALUES (source.id, source.email, source.name, source.updated_at);

Tricky Interview Questions

Frequently Asked Questions (FAQ)