PostgreSQL unique constraint null: Allowing only one Null

While the SQL standard allows multiple nulls in a unique column, and that is how Postgres behaves, some database systems (e.g. MS SQL) allow only a single null in such cases. Users migrating from other database systems sometimes want to emulate this behavior in Postgres. Fortunately, this can be done.

First, let me show the default Postgres behavior:

CREATE TABLE nulltest (x INTEGER UNIQUE);

INSERT INTO nulltest VALUES (NULL);
INSERT INTO nulltest VALUES (NULL);

A single-null constraint can be created with a partial expression index that indexes only null values (the partial part), and uses is null to store true in the unique index (the expression part).

DELETE FROM nulltest;

CREATE UNIQUE INDEX i_nulltest ON nulltest ((x IS NULL)) WHERE x IS NULL;

INSERT INTO nulltest VALUES (NULL);
INSERT INTO nulltest VALUES (NULL);
ERROR:  duplicate key value violates unique constraint "i_nulltest"
DETAIL:  Key ((x IS NULL))=(t) already exists.

This method can also be used to create a constraint that allows only a single null for each non-null composite indexed value:

CREATE TABLE nulltest2 (x INTEGER, y INTEGER);

CREATE UNIQUE INDEX i_nulltest2 ON nulltest2 (x, (y IS NULL)) WHERE y IS NULL;

INSERT INTO nulltest2 VALUES (1, NULL);
INSERT INTO nulltest2 VALUES (2, NULL);
INSERT INTO nulltest2 VALUES (2, NULL);
ERROR:  duplicate key value violates unique constraint "i_nulltest2"
DETAIL:  Key (x, (y IS NULL))=(2, t) already exists.

INSERT INTO nulltest2 VALUES (2, 3);
INSERT INTO nulltest2 VALUES (2, 3);

The i_nulltest2 index allows only one y null value for each x value. This can actually be useful in certain data models. This illustrates how expression and partial index features can be combined for some interesting effects.

This post originally appeared on Bruce's personal blog.

​