Re:Postgres 15 improves UNIQUE and NULL
by vasachi on Monday July 11, 2022 @05:38AM
(#32054018)
It is kinda weird, if you don't know how it works. For instance:
Say you have a unique constraint on two columns (column_a, column_b). column_a is not nullable, column_b IS nullable. Obviously these values are unique:
(1, 2)
(1, 3)
(2, 2)
But these values ARE ALSO UNIQUE:
(1, NULL)
(1, NULL)
It is obvious in hindsight (NULL isn't equal to NULL), but can be quite a surprise.
...
Oh wait, postgres 15 deals with just this situation. Huh.
Re:Postgres 15 improves UNIQUE and NULL
by jhugo on Monday July 11, 2022 @06:02AM
(#32054179)
Honestly it's what you want way more often than not. A unique index on a nullable column is usually representing some optional identifier. You wouldn't usually want only one row to be able to omit the identifier.
Say you have a unique constraint on two columns (column_a, column_b). column_a is not nullable, column_b IS nullable. Obviously these values are unique:
(1, 2) (1, 3) (2, 2)
But these values ARE ALSO UNIQUE:
(1, NULL) (1, NULL)
It is obvious in hindsight (NULL isn't equal to NULL), but can be quite a surprise.
...
Oh wait, postgres 15 deals with just this situation. Huh.