postgresqlnullunique-constraint

Adding unique constraint for null column returns error

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)
Solution

  • 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:

    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.