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 | {1,2} |
| 10 | 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 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 shows that failure. If the application upserts on the old key, read changing the unique index behind an ON CONFLICT upsert before you replace it.
Find the other changes that need a rehearsal in which PostgreSQL migration needs a rehearsal.