---
title: "Find the duplicates a PostgreSQL unique constraint will let through | Proofline"
description: "A unique constraint can be added successfully to a table that still holds duplicates, when the duplicates differ in case or spacing or contain NULL."
url: https://proofline.sh/learn/postgres-unique-constraint-existing-duplicates/
---

[Learning center](/learn/)

# Find the duplicates a PostgreSQL unique constraint will let through

A unique constraint can be added successfully to a table that still holds duplicates, when the duplicates differ in case or spacing or contain NULL. Before you add one, group the rows by the application's meaning of "the same", and decide how NULL should behave.

Breaking migrations The Proofline team November 20, 2026 3 min read

## A team membership table with two kinds of duplicate

A product lets each team invite a member once by email, and once per single sign-on identity. A migration adds two unique constraints. We ran this on PostgreSQL 16.15.

```
CREATE TABLE memberships (
    id integer PRIMARY KEY,
    team_id integer NOT NULL,
    email text NOT NULL,
    sso_subject text
);
INSERT INTO memberships VALUES
  (1, 10, 'ana@example.com', NULL),
  (2, 10, 'Ana@Example.com', NULL),
  (3, 10, 'ben@example.com ', 'okta|81'),
  (4, 10, 'ben@example.com', 'okta|82'),
  (5, 11, 'ana@example.com', NULL);

ALTER TABLE memberships
    ADD CONSTRAINT memberships_team_email_key UNIQUE (team_id, email);
ALTER TABLE memberships
    ADD CONSTRAINT memberships_team_subject_key UNIQUE (team_id, sso_subject);
```

Both statements succeed. The migration passes in CI and in staging. Team 10 still has Ana twice and Ben twice.

## Group by what the application means

The application lowercases and trims addresses when a person signs in, so `Ana@Example.com` and `ana@example.com` are the same member. Write that rule as the grouping key:

```
SELECT team_id, lower(btrim(email)) AS member,
       array_agg(id ORDER BY id) AS rows
FROM memberships
GROUP BY 1, 2
HAVING count(*) > 1;
```

| team\_id | member | rows |
| --- | --- | --- |
| 10 | [ana@example.com](mailto:ana@example.com) | {1,2} |
| 10 | [ben@example.com](mailto:ben@example.com) | {3,4} |

An expression index enforces the same rule, and fails on the current data:

```
CREATE UNIQUE INDEX memberships_team_member_key
    ON memberships (team_id, lower(btrim(email)));
```

```
ERROR:  could not create unique index "memberships_team_member_key"
DETAIL:  Key (team_id, lower(btrim(email)))=(10, ana@example.com) is duplicated.
```

That failure is the useful result. Rows 3 and 4 also show that merging is a product decision: they carry different sign-on subjects, so they may be two people who share an inbox, or one person whose identity provider changed. Decide which row survives, and what happens to the data attached to the other, before the migration runs.

## Decide what `NULL` means

By default PostgreSQL treats `NULL` values as distinct from each other, so `UNIQUE (team_id, sso_subject)` accepts any number of members without a sign-on subject. That is usually what the application wants. If instead a team may have at most one member without one, PostgreSQL 15 and later can say so with `NULLS NOT DISTINCT`, as the [constraints documentation](https://www.postgresql.org/docs/16/ddl-constraints.html#DDL-CONSTRAINTS-UNIQUE-CONSTRAINTS) describes:

```
ALTER TABLE memberships DROP CONSTRAINT memberships_team_subject_key;
ALTER TABLE memberships
    ADD CONSTRAINT memberships_team_subject_key
    UNIQUE NULLS NOT DISTINCT (team_id, sso_subject);
```

```
ERROR:  could not create unique index "memberships_team_subject_key"
DETAIL:  Key (team_id, sso_subject)=(10, null) is duplicated.
```

The plain grouping query finds the same rows, because `GROUP BY` puts `NULL` values in one group even though a default unique constraint does not:

```
SELECT team_id, sso_subject, count(*)
FROM memberships GROUP BY 1, 2 HAVING count(*) > 1;
-- team_id 10, sso_subject NULL, count 2
```

## What to rehearse

Run the grouping query against a replica or a restored snapshot with the production data, with the key the application actually uses, and record the duplicate count in the pull request. A count of zero tells you the constraint will mean what the application expects. Then build the index with `CREATE UNIQUE INDEX CONCURRENTLY` so the build does not block writes, and check it afterwards, because a duplicate written during the build leaves an invalid index. [Retrying a failed CREATE INDEX CONCURRENTLY](/learn/postgres-create-index-concurrently-retry/) shows that failure. If the application upserts on the old key, read [changing the unique index behind an ON CONFLICT upsert](/learn/postgres-on-conflict-unique-index-change/) before you replace it.

Find the other changes that need a rehearsal in [which PostgreSQL migration needs a rehearsal](/learn/postgres-migration-rehearsal/).

## One month of free code review.

New organizations get one calendar month of hosted code review for private and public GitHub repositories. No card or cloud connection required. [Eligibility and fair-use terms](/free-code-review/).

[Start free with GitHub](https://proofline.sh/orgs/plan?offer=free_month)

## Related reading

-   ### [Retrying a failed PostgreSQL CREATE INDEX CONCURRENTLY](/learn/postgres-create-index-concurrently-retry/)
    
    Breaking migrations The Proofline team November 20, 2026 3 min read
    
-   ### [Changing the unique index behind a PostgreSQL ON CONFLICT upsert](/learn/postgres-on-conflict-unique-index-change/)
    
    Schema conflicts The Proofline team November 20, 2026 3 min read
