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.