Adding unique constraint for null column returns error

Question

I am trying to add a unique constraint to an existing table in Postgres on a null column named personnel_id. Most of the records have null personnel_id. Here's how I do it:

ALTER TABLE "myschema"."mytable" ADD UNIQUE NULLS not distinct ("personnel_id");

And I get this error:

ERROR:  could not create unique index "mytable_personnel_id_key"
DETAIL:  Key (personnel_id)=() is duplicated.

My Postgres version is:

psql (PostgreSQL) 15.3 (Debian 15.3-1.pgdg120+1)

Answer 1

The error message actually reports a conflict for empty strings, i.e. two or more instances of '' - not the same as null! For conflicting null values you would see:

ERROR: Key (personnel_id)=(null) is duplicated.

But you get:

ERROR: Key (personnel_id)=() is duplicated.

fiddle

You need version Postgres 15 or newer to use the NULLS [NOT] DISTINCT feature. See:

• Create unique constraint with null columns

Also:

psql (PostgreSQL) 15.3 (Debian 15.3-1.pgdg120+1)

psql is not PostgreSQL. It's the default interactive terminal and is not necessarily in line with the Postgres version. Check your Postgres version with SELECT version(); while being connected.

Modern versions of psql also show the Postgres version on connection if it disagrees.

Example:
When starting psql 16.1 connecting to a Postgres 16.1 DB:

psql (16.1 (Ubuntu 16.1-1.pgdg20.04+1))

When connecting to a DB cluster with different Postgres version:

psql (16.1 (Ubuntu 16.1-1.pgdg20.04+1), server 14.10 (Ubuntu 14.10-1.pgdg20.04+1))

The second part of the display is relevant.

Answer 2

Make sure that it is NULL values you are seeing, not empty strings. Two empty strings are always equal.

If you specify NULLS NOT DISTINCT, PostgreSQL treats two NULL values as not distinct, so you will get an error.

If you want NULLs to be distinct, that is, if you want several NULL values to be present in the unique column, use NULLS DISTINCT:

ALTER TABLE myschema.mytable ADD UNIQUE NULLS DISTINCT (personnel_id);