Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

A UNIQUE constraint in PostgreSQL still lets duplicate rows in when a column is NULL

ErrorThe second identical row with a NULL is accepted instead of failing with a duplicate key error

Short answer

PostgreSQL treats two NULLs as different, so a UNIQUE constraint cannot reject rows that repeat a NULL. On PostgreSQL 15 or newer, declare the constraint with NULLS NOT DISTINCT. On older versions, or when only some rows should be unique, use a unique index on an expression or a partial unique index.

  • Applies to PostgreSQL 15, PostgreSQL 16, PostgreSQL 17
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Find the rows that repeat, treating NULL as a normal value, and see how the unique index was declared. In psql, \d shows the index definition.

    SELECT col_a, col_b, count(*) FROM my_table GROUP BY col_a, col_b HAVING count(*) > 1;
    \d my_table
  2. 2Match what you see, then jump to its fix

    1. 1The index shows UNIQUE (col_a, col_b) with no NULLS NOT DISTINCT, and the duplicates all have NULL in one of the columns.Default behaviour: NULLs are not equal to each otherFix
    2. 2SHOW server_version reports a number below 15, and NULLS NOT DISTINCT is rejected as invalid syntax.The server is older than PostgreSQL 15Fix
    3. 3You want one row per col_a when col_b is NULL, and one row per (col_a, col_b) when it is not.Only rows where a column is NULL, or is not NULL, must be uniqueFix

What does the error look like?

  • The second identical row with a NULL is accepted instead of failing with a duplicate key error
  • A table with UNIQUE (a, b) holds several rows where a is the same and b is NULL

Cause 1 of 3

Default behaviour: NULLs are not equal to each other

1Check

By default, two null values are not considered equal in a uniqueness check, so a row with NULL in a constrained column can be stored many times.

SELECT conname, pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid = 'my_table'::regclass AND contype = 'u';

2Fix

On PostgreSQL 15 or newer, drop the constraint and add it again with NULLS NOT DISTINCT. Remove existing duplicates first, or the ADD fails.

ALTER TABLE my_table DROP CONSTRAINT my_table_col_a_col_b_key;
ALTER TABLE my_table ADD CONSTRAINT my_table_col_a_col_b_key
    UNIQUE NULLS NOT DISTINCT (col_a, col_b);

3Verify

Insert the same row twice. The second insert must now fail with a duplicate key error.

INSERT INTO my_table (col_a, col_b) VALUES (1, NULL);
INSERT INTO my_table (col_a, col_b) VALUES (1, NULL);

Confirmed by postgresql.org

Did this fix it?

Cause 2 of 3

The server is older than PostgreSQL 15

1Check

The NULLS NOT DISTINCT option arrived in PostgreSQL 15. Check the server version you are really connected to.

SHOW server_version;

2Fix

Build a unique index on an expression that replaces NULL with a value that can never be real data. Pick the placeholder to match the column type; an empty string works for text. Note that the expression index must wrap its expression in double parentheses.

CREATE UNIQUE INDEX my_table_col_a_col_b_uniq
    ON my_table (col_a, (COALESCE(col_b, '')));

3Verify

Insert two rows with the same col_a and NULL in col_b. The second insert fails.

INSERT INTO my_table (col_a, col_b) VALUES (1, NULL);
INSERT INTO my_table (col_a, col_b) VALUES (1, NULL);

Confirmed by postgresql.org

Did this fix it?

Cause 3 of 3

Only rows where a column is NULL, or is not NULL, must be unique

1Check

A single constraint cannot express two rules. A partial unique index enforces uniqueness over a subset of the table, chosen with a WHERE clause.

SELECT indexdef FROM pg_indexes WHERE tablename = 'my_table';

2Fix

Create one unique index for each case.

CREATE UNIQUE INDEX my_table_a_when_b_null ON my_table (col_a) WHERE col_b IS NULL;
CREATE UNIQUE INDEX my_table_a_b_when_b_set ON my_table (col_a, col_b) WHERE col_b IS NOT NULL;

3Verify

Duplicate pairs fail in both cases.

INSERT INTO my_table (col_a, col_b) VALUES (2, NULL);
INSERT INTO my_table (col_a, col_b) VALUES (2, NULL);

Confirmed by postgresql.org

Did this fix it?

How do I stop it coming back?

  • Decide for each nullable column in a UNIQUE rule whether NULL counts as a value, and write NULLS NOT DISTINCT or NOT NULL to match.
  • Prefer NOT NULL on columns that take part in a business key.
  • Add a duplicate check query to your migration tests before you add a unique rule to existing data.
  • Remember that the default null treatment is implementation-defined in the SQL standard, so do not assume other databases behave the same.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.