Question 302760 in Debian Question posted in
The oficial documentation can be found here.

Debian – 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)
Tags:

2

Answers


  1. 0

    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);
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search