Your UNIQUE constraint in PostgreSQL lets duplicates through. Here is how to find every one

The table has a UNIQUE constraint, and identical rows sit in it in pairs. If at least one column of the key is nullable, this is expected behaviour: following the SQL standard, PostgreSQL treats two NULLs as distinct, so a row with an empty field never matches any other row, not even its own copy. No error, nothing in the log, the constraint is in place.

We hit this in a service table next to a 1C:Enterprise database, caught by the final test of a migration. Below is the procedure we now run on every such table: find the keys, measure how many rows are actually protected, decide what the data means, fix it, and prove the fix. All queries were run on PostgreSQL 17.10, MS SQL Server behaviour was checked on 2022.

Reproduce it in a minute

CREATE TABLE integration_event (
  id        bigserial PRIMARY KEY,
  entity_id uuid NOT NULL,
  state     text NOT NULL,
  kind      text,                       -- empty on early states
  UNIQUE (entity_id, state, kind)
);

INSERT INTO integration_event (entity_id, state, kind)
VALUES ('11111111-1111-1111-1111-111111111111', 'CREATED', NULL);
INSERT INTO integration_event (entity_id, state, kind)
VALUES ('11111111-1111-1111-1111-111111111111', 'CREATED', NULL);

SELECT count(*) FROM integration_event;  -- 2

Both inserts succeed. The same key on SQL Server behaves the opposite way:

CREATE TABLE #t (a int NOT NULL, b int NULL, UNIQUE (a, b));
INSERT INTO #t (a, b) VALUES (1, NULL);
INSERT INTO #t (a, b) VALUES (1, NULL);  -- error 2627, UNIQUE KEY violation

DBMS	Two rows (1, NULL) under UNIQUE (a, b)
PostgreSQL before 15	both are stored, no way to switch it
PostgreSQL 15+	both are stored unless NULLS NOT DISTINCT is declared
MS SQL Server	the second one fails with error 2627

This matters most if you are migrating. A key that held off duplicates on SQL Server for years gets created on PostgreSQL without a single warning and silently stops protecting rows with an empty field. The migration report is clean, the schema is the same.

Step 1. Find every such key with one query

The symptom is simple: a unique index with a nullable column in its key. Search pg_index, not pg_constraint. A UNIQUE constraint is always backed by an index, so pg_index shows both declared constraints and indexes created directly with CREATE UNIQUE INDEX.

PostgreSQL 15 and later:

SELECT n.nspname                                   AS schema_name,
       c.relname                                   AS table_name,
       i.relname                                   AS index_name,
       coalesce(con.conname, '-')                  AS constraint_name,
       string_agg(a.attname, ', ' ORDER BY k.pos)  AS nullable_key_columns,
       pg_get_expr(ix.indpred, ix.indrelid)        AS index_predicate
FROM pg_index ix
JOIN pg_class      i ON i.oid = ix.indexrelid
JOIN pg_class      c ON c.oid = ix.indrelid
JOIN pg_namespace  n ON n.oid = c.relnamespace
CROSS JOIN LATERAL unnest(ix.indkey::int2[]) WITH ORDINALITY AS k(attnum, pos)
JOIN pg_attribute  a ON a.attrelid = ix.indrelid AND a.attnum = k.attnum
LEFT JOIN pg_constraint con ON con.conindid = ix.indexrelid AND con.contype = 'u'
WHERE ix.indisunique
  AND NOT ix.indisprimary
  AND NOT ix.indnullsnotdistinct        -- already switched, no hole
  AND k.pos <= ix.indnkeyatts           -- INCLUDE columns are not part of the key
  AND NOT a.attnotnull
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
  AND n.nspname NOT LIKE 'pg_toast%'
GROUP BY n.nspname, c.relname, i.relname, con.conname, ix.indpred, ix.indrelid
ORDER BY 1, 2, 3;

On PostgreSQL 11-14 the indnullsnotdistinct column does not exist yet, so drop that line. Everything else works: indnkeyatts arrived in 11 together with INCLUDE. The query only reads the catalog and needs no superuser rights.

Why not two simple queries

Our first version was two queries: one over pg_constraint with contype = 'u', one over pg_index with partial indexes filtered out. On a test schema of seven tables it went wrong in five different ways, and you will meet each of them:

What the query had	What the run showed	Fix
no pg_toast filter	88 extra rows about chunk_id and chunk_seq on an empty cluster	nspname NOT LIKE 'pg_toast%'
attnum = ANY (indkey) takes every index column	a column from INCLUDE (note) reported as a hole	key position not greater than indnkeyatts
pg_constraint knows nothing about NULLS NOT DISTINCT	an already fixed constraint listed	indnullsnotdistinct flag from pg_index
partial indexes dropped entirely	an index with WHERE active vanished, with the same hole	predicate shown as a column, a human decides
constraint and its index in two queries	one hole reported twice	a single pass over pg_index

Two false positives remain. A generated column like coalesce(kind, '__NULL__') is formally nullable but never empty, so you can cross it off. An expression index such as (a, lower(b)) has a zero in indkey for the expression and the join loses it, so check those by eye with \d table_name.

Step 2. Measure the share of unprotected rows

The list does not tell you how bad it is. A key with two empty values per thousand rows can wait. A key that is empty in most rows protects almost nothing.

SELECT count(*)                                                AS total,
       count(*) FILTER (WHERE kind IS NULL)                    AS unprotected,
       round(100.0 * count(*) FILTER (WHERE kind IS NULL)
             / nullif(count(*), 0), 1)                         AS unprotected_pct
FROM integration_event;

With several nullable key columns, write kind IS NULL OR other_column IS NULL in the FILTER: one empty field is enough for a row to fall outside the check.

Our case for scale: a service table of external operation history, a three-column key where the third column is filled only on later states. Over 66 days it received about 2,200 events, and the third column was empty in 79 % of rows. About 460 rows were protected, about 1,740 were not. It was caught by the final migration test, which replayed the same events and expected zero new rows. Your share will differ, so copy the query, not the number.

Accumulated duplicates are found by grouping on the same key:

SELECT entity_id, state, kind, count(*) AS row_count
FROM integration_event
GROUP BY entity_id, state, kind
HAVING count(*) > 1;

Note the inconsistency that keeps this bug alive for years: GROUP BY puts all NULLs into one group, the unique index treats them as different. Same database, two answers to "are there duplicates". The grouping is the honest one.

Step 3. Decide whether it is one entity or two

Before fixing, answer a question about the data: do two rows with the same key and an empty field describe the same thing?

For an integration state table the answer is yes: a replayed event after a handler restart is the same record and must be rejected. Same for exchange buffers and job queues, where handlers must be safe to rerun.

Sometimes it is the other way round. In reference and archive data an empty field may mean "unknown" in one row and "not applicable" in another, and those rows really are different. Gluing them together with a uniqueness rule corrupts data silently. Put an explicit value instead of the empty one first, then add the constraint if you still need it.

Step 4. Fix it, depending on the version

PostgreSQL 15 and later

Remove what has already piled up, otherwise the new index fails with could not create unique index ... Key (...)=(..., null) is duplicated. This keeps the row with the smallest id in each group and compares empty values with IS NOT DISTINCT FROM:

DELETE FROM integration_event e
USING integration_event d
WHERE e.entity_id = d.entity_id
  AND e.state     = d.state
  AND e.kind IS NOT DISTINCT FROM d.kind
  AND e.id > d.id;

Which row to keep is a business decision, not this query's. If duplicates differ in date or in the external system's response, look at them with the Step 2 query first.

Then build the new index without blocking writes and swap the constraint:

CREATE UNIQUE INDEX CONCURRENTLY integration_event_uq_new
  ON integration_event (entity_id, state, kind) NULLS NOT DISTINCT;

ALTER TABLE integration_event
  DROP CONSTRAINT integration_event_entity_id_state_kind_key,
  ADD CONSTRAINT integration_event_uq UNIQUE USING INDEX integration_event_uq_new;

USING INDEX turns the ready index into a constraint and renames it (PostgreSQL prints a NOTICE about it). The NULLS NOT DISTINCT flag survives: \d integration_event shows UNIQUE CONSTRAINT ... NULLS NOT DISTINCT. For a new table write it in the declaration: UNIQUE NULLS NOT DISTINCT (entity_id, state, kind).

CREATE INDEX CONCURRENTLY cannot run inside a transaction. If it fails, an invalid index is left behind: drop it and build again.

PostgreSQL 11-14

There is no switch, so you have two workarounds, each with a cost.

Two partial indexes, one for the filled field and one for the empty one:

CREATE UNIQUE INDEX integration_event_uq_kind
  ON integration_event (entity_id, state, kind)
  WHERE kind IS NOT NULL;

CREATE UNIQUE INDEX integration_event_uq_no_kind
  ON integration_event (entity_id, state)
  WHERE kind IS NULL;

The common mistake is creating only the first one. It protects exactly the rows that were already protected. The other cost shows up with several nullable columns: every combination of empty fields needs its own index, two columns give four indexes, three give eight.

A generated column with a sentinel value:

ALTER TABLE integration_event
  ADD COLUMN kind_key text
  GENERATED ALWAYS AS (coalesce(kind, '__NULL__')) STORED;

CREATE UNIQUE INDEX ON integration_event (entity_id, state, kind_key);

Checked: the second insert now fails on Key (a, kind_key)=(1, __NULL__) already exists. The risk is that the sentinel shares the domain with real values. If the external system ever sends the literal text __NULL__, two different rows become equal. Pick a value that cannot physically appear in the input; for numbers, a value outside the allowed range pinned with CHECK. GENERATED ... STORED needs PostgreSQL 12, so on 11 only partial indexes remain.

Step 5. Prove the protection works

"The rerun finished without errors" proves nothing here: duplicates are written silently both before and after the fix. Count new rows, and the count must be zero.

The shortest way without a handler is to insert the table into itself inside a transaction and roll back:

BEGIN;
CREATE TEMP TABLE before_replay ON COMMIT DROP AS
  SELECT count(*) AS n FROM integration_event;

INSERT INTO integration_event (entity_id, state, kind)
SELECT entity_id, state, kind FROM integration_event
ON CONFLICT DO NOTHING;

SELECT (SELECT count(*) FROM integration_event)
     - (SELECT n FROM before_replay) AS new_rows;
ROLLBACK;

Every row the constraint lets through lands a second time, and new_rows shows how many. On a 100-row test table where the field was filled in every fifth row, we got 80 before the fix and 0 after. The id is left out of the select, it comes from the sequence; the sequence does not roll back, so expect a gap. Insert triggers will fire, so run this on a copy.

The copy should be fresh. The share of empty values on a test database restored half a year ago says nothing about production today, and synthetic data gives zero percent because whoever wrote the test filled every field.

If you have the handler, it is even simpler: run it a second time on the same data and compare row counts. In a migration runbook this is one line, "a repeated run creates zero new records", and it catches this whole class of bugs.

Where it shows up

Platform tables of an ERP or accounting system are usually not yours to touch. Everything built around them is: staging tables for data exchange, integration buffers, web service call logs, background job queues. Uniqueness there is held only by the schema, and that is exactly where it most often sits on a key with an optional field.

The most common road to this hole is a migration from SQL Server, where the same key used to work, so Step 1 deserves its own line in a migration checklist. Other things that break on that road are covered in our article on moving 1C databases to PostgreSQL (in Russian). The original, longer write-up with a section on 1C:Enterprise external data sources is on our site (in Russian).

Run the Step 1 query on your database and tell me in the comments how many unique keys with nullable columns it found, and what share of empty values the worst one had. I am curious whether 79 % is an outlier or the normal picture for tables around a business system.

Translated and adapted from our Russian article with AI assistance; queries and numbers checked against the original.