Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE tbl_empty (personnel_id text);
INSERT INTO tbl_empty VALUES
  ('a')
, ('b')
, ('')
, ('')
;

CREATE TABLE



INSERT 0 4


CREATE TABLE tbl_null (personnel_id text);
INSERT INTO tbl_null VALUES
  ('a')
, ('b')
, (null)
, (null)
;

CREATE TABLE



INSERT 0 4


-- empty strings raise a unique violation either way!
ALTER TABLE tbl_empty ADD CONSTRAINT empty_u1 UNIQUE NULLS NOT DISTINCT (personnel_id);

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


-- empty strings raise a unique violation either way!
ALTER TABLE tbl_empty ADD CONSTRAINT empty_u2 UNIQUE NULLS DISTINCT (personnel_id);

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


-- null values only violate uniqueness with NULLS NOT DISTINCT (new in pg 15)
ALTER TABLE tbl_null ADD CONSTRAINT tbl_null_u1 UNIQUE NULLS NOT DISTINCT (personnel_id);

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


-- but not without the clause:
ALTER TABLE tbl_null ADD CONSTRAINT tbl_null_u2 UNIQUE NULLS DISTINCT (personnel_id);

ALTER TABLE


-- nor with the opposite (default) setting:
ALTER TABLE tbl_null ADD CONSTRAINT tbl_null_u3 UNIQUE (personnel_id);

ALTER TABLE