Remove duplicate PostgreSQL rows—and stop them returning

A duplicate report is not yet a deletion rule. Before touching rows, define which columns make two records equivalent, which record should survive, what related data must move, and how concurrent writes will be prevented from recreating the problem.

Measure duplicate groups without hiding NULL or case behavior

SELECT lower(email) AS email_key,
       count(*) AS row_count,
       array_agg(id ORDER BY created_at, id) AS ids
FROM public.customers
GROUP BY lower(email)
HAVING count(*) > 1
ORDER BY row_count DESC, email_key;

The accepted Stack Overflow answer correctly uses GROUP BY ... HAVING count(*) > 1 to find groups. Production work must go further: A@example.com and a@example.com are duplicates only if the product says so; whitespace and Unicode normalization also require explicit policy. PostgreSQL unique indexes ordinarily allow multiple nulls, so decide whether missing values participate.

Rank every row with a deterministic survivor

WITH ranked AS (
  SELECT id,
         email,
         created_at,
         row_number() OVER (
           PARTITION BY lower(email)
           ORDER BY verified_at DESC NULLS LAST,
                    created_at,
                    id
         ) AS duplicate_rank
  FROM public.customers
)
SELECT *
FROM ranked
WHERE duplicate_rank > 1
ORDER BY lower(email), duplicate_rank;

PostgreSQL documents that row_number() assigns sequence within each partition according to the window order. Include a unique final tie-breaker such as id; tied ordering is otherwise unspecified. Here a verified account wins, then the earliest record, then the smallest ID.

Inspect references before deleting parents

SELECT
  tc.table_schema,
  tc.table_name,
  kcu.column_name
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
  ON kcu.constraint_name = tc.constraint_name
 AND kcu.constraint_schema = tc.constraint_schema
WHERE tc.constraint_type = 'FOREIGN KEY'
  AND tc.constraint_schema = 'public';

Map duplicate IDs to survivor IDs in a temporary or migration table. Update child references, merge business state where required, and preserve an audit record. Cascading deletion is not data reconciliation.

Create and review a deletion manifest

CREATE TEMP TABLE customer_dedup_manifest AS
WITH ranked AS (
  SELECT id,
         first_value(id) OVER (
           PARTITION BY lower(email)
           ORDER BY verified_at DESC NULLS LAST, created_at, id
         ) AS survivor_id,
         row_number() OVER (
           PARTITION BY lower(email)
           ORDER BY verified_at DESC NULLS LAST, created_at, id
         ) AS rn
  FROM public.customers
)
SELECT id AS duplicate_id, survivor_id
FROM ranked
WHERE rn > 1;

SELECT * FROM customer_dedup_manifest
ORDER BY survivor_id, duplicate_id;

Export or persist the manifest according to the change policy. It is the bridge for updating references and the evidence of what was selected.

Move child records before deleting duplicates

BEGIN;

UPDATE public.orders o
SET customer_id = m.survivor_id
FROM customer_dedup_manifest m
WHERE o.customer_id = m.duplicate_id;

UPDATE public.support_tickets t
SET customer_id = m.survivor_id
FROM customer_dedup_manifest m
WHERE t.customer_id = m.duplicate_id;

SELECT count(*) FROM customer_dedup_manifest;
ROLLBACK;

Rehearse with rollback and inspect affected counts. Unique constraints in child tables may reveal business conflicts—for example, both duplicate customers may own one subscription for the same plan. Resolve those rules explicitly.

Archive and delete through the reviewed manifest

BEGIN;

CREATE TABLE audit.removed_customers_20260727 AS
SELECT c.*, m.survivor_id, clock_timestamp() AS archived_at
FROM public.customers c
JOIN customer_dedup_manifest m
  ON m.duplicate_id = c.id;

DELETE FROM public.customers c
USING customer_dedup_manifest m
WHERE c.id = m.duplicate_id;

SELECT lower(email), count(*)
FROM public.customers
GROUP BY lower(email)
HAVING count(*) > 1;

COMMIT;

For a large table, a single transaction can create lock pressure, replica lag and extensive WAL. Process bounded key ranges or manifest batches, monitor impact, and retain the uniqueness race in mind until enforcement exists.

Prevent the same duplicates after cleanup

CREATE UNIQUE INDEX CONCURRENTLY customers_email_unique
ON public.customers (lower(email))
WHERE email IS NOT NULL;

The partial expression index enforces case-insensitive uniqueness for non-null email values. It must match product semantics. CREATE INDEX CONCURRENTLY permits normal writes during most of the build but takes longer, waits for transactions and can leave an invalid index after failure.

SELECT indexrelid::regclass, indisvalid, indisready
FROM pg_index
WHERE indexrelid =
  'public.customers_email_unique'::regclass;

If concurrent writes can introduce a duplicate between cleanup and index creation, coordinate a brief write restriction, deploy application-side conflict handling first, or build an operational sequence that retries cleanup after the failed uniqueness build.

Make ingestion idempotent

INSERT INTO public.customers (email, display_name)
VALUES (:email, :display_name)
ON CONFLICT (lower(email))
WHERE email IS NOT NULL
DO UPDATE SET
  display_name = EXCLUDED.display_name
RETURNING id;

Use conflict behavior only if an update is actually correct. For security-sensitive identities, silently merging accounts can be dangerous; returning a conflict for an explicit verification flow may be safer.

Verify data, constraint and application behavior

SELECT count(*) AS remaining_duplicate_groups
FROM (
  SELECT lower(email)
  FROM public.customers
  WHERE email IS NOT NULL
  GROUP BY lower(email)
  HAVING count(*) > 1
) d;

Also verify orphan counts, child totals, login or customer lookup paths, replication health, index validity and a deliberate duplicate insert. Compare business totals before and after rather than trusting the number of deleted rows alone.

Failure mode	Prevention
Arbitrary survivor	Complete deterministic ordering ending in unique ID
Orphaned or lost child data	Manifest and explicit reference migration
Duplicates return during cleanup	Coordinated write control and uniqueness enforcement
Case/NULL behavior surprises	Encode exact identity semantics in index and tests
Cleanup overwhelms production	Rehearsal, bounded batches and operational monitoring

Deletion repairs today’s rows. A constraint repairs the data model that allowed them.

Sources: the solved Stack Overflow question (question by Alex; accepted answer by gbn, CC BY-SA), PostgreSQL’s official window-function tutorial and CREATE INDEX reference.