4 ms·
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
by vasachi 4y ago
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.
- jhugo 4y agoHonestly 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.
- vasachi 4y agoOh yeah, totally. But not always. I'm very glad that we now have an option to have both behaviours.