A UNIQUE constraint in PostgreSQL still lets duplicate rows in when a column is NULL
The second identical row with a NULL is accepted instead of failing with a duplicate key errorShort 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.
Fix it fast
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_table2Match what you see, then jump to its fix
- 1
The 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
SHOW server_version reports a number below 15, and NULLS NOT DISTINCT is rejected as invalid syntax.The server is older than PostgreSQL 15Fix - 3
You 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
- 1
What does the error look like?
The second identical row with a NULL is accepted instead of failing with a duplicate key errorA 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
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
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
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
Databases
INSERT ... ON CONFLICT fails because no unique constraint matches the conflict target
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
PostgreSQL must find a unique index that covers exactly the columns in your ON CONFLICT list.
4 causes, each with a fixDatabases
DBeaver cannot run pg_restore: the utility is not found in the client home
Utility 'pg_restore' not found in client home '/usr/lib' (/usr/lib)
DBeaver runs pg_restore from a local PostgreSQL client folder; it does not bundle one.
3 causes, each with a fixDatabases
DBeaver cannot download the PostgreSQL driver from Maven
The PostgreSQL driver files are missing and the download does not complete
DBeaver downloads the PostgreSQL JDBC driver from a Maven repository the first time you connect.
3 causes, each with a fixDatabases
python manage.py migrate finishes but the tables for my app are missing
relation "myapp_book" does not exist
migrate only applies migration files that exist.
3 causes, each with a fixDatabases
Django save fails on PostgreSQL: null value in a column violates a not-null constraint
django.db.utils.IntegrityError: null value in column "price" of relation "shop_product" violates not-null constraint
A model field is NOT NULL in the database unless it has null=True.
3 causes, each with a fixDatabases
Django migration on PostgreSQL stops with 'pending trigger events'
django.db.utils.OperationalError: cannot ALTER TABLE "mytable" because it has pending trigger events
PostgreSQL runs a Django migration in one transaction.
2 causes, each with a fix