Another nice feature was just committed for PostgreSQL 15: Add UNIQUE null treatment option (discussion here, commit details here). What is it about? The answer is quite easy if we do a little example which shows the effect of this new feature.

Consider the following table and unique index:

postgres=# create table t ( a int );
CREATE TABLE
postgres=# create unique index i on t ( a );
CREATE INDEX

This obviously prevents us from adding the same value more than once:

postgres=# insert into t values (1);
INSERT 0 1
postgres=# insert into t values (1);
ERROR:  duplicate key value violates unique constraint "i"
DETAIL:  Key (a)=(1) already exists.

But what about NULLs? NULL means undefined, so what should the unique index do when you try to insert more than one row with the value of NULL? Are two NULL values the same or not? If you ask PostgreSQL if NULL equals NULL, you get undefined (or NULL):

postgres=# select null = null;
 ?column? 
----------
 
(1 row)

Comparing something which is not defined against something which is not defined does not make much sense. The unique index we’ve created above behaves exactly like this:

postgres=# insert into t values(null);
INSERT 0 1
postgres=# insert into t values(null);
INSERT 0 1
postgres=# insert into t values(null);
INSERT 0 1
postgres=# insert into t values(null);
INSERT 0 1
postgres=# insert into t values(null);
INSERT 0 1

PostgreSQL 15 will give you choice here:

postgres=# create table tt ( a int );
CREATE TABLE
postgres=# create unique index ii on tt (a) nulls not distinct;
CREATE INDEX
postgres=# insert into tt values(null);
INSERT 0 1
postgres=# insert into tt values(null);
ERROR:  duplicate key value violates unique constraint "ii"
DETAIL:  Key (a)=(null) already exists.

The default, of course, is the behavior as we have it now, but this can also be requested explicitly:

postgres=# create unique index iii on tt (a) nulls distinct;
CREATE INDEX

Nice.


Thumbnail [60x60]
by
Daniel Westermann