Re:Postgres 15 improves UNIQUE and NULL
by masklinn on Monday July 11, 2022 @06:19AM
(#32054281)
> Cool but this was trivially solved previously by excluding NULL from the index using a partial index.
That seems like the opposite solution, treating NULLs as different in database systems which treat them as identical by default (which I think only includes MSSQL).
In postgres, and most DBMS, using a partial index excluding NULL allows any number of rows to have a NULL value… which is the same behaviour as if you’re not excluding NULLs.
The new opt-in behaviour is treating all nulls as the same value, and thus allowing a UNIQUE index to constraint them.
This required two different partial indexes, or for constrained data subset in hacks like COALESCE-ing nulls to a sentinel.
That seems like the opposite solution, treating NULLs as different in database systems which treat them as identical by default (which I think only includes MSSQL).
In postgres, and most DBMS, using a partial index excluding NULL allows any number of rows to have a NULL value… which is the same behaviour as if you’re not excluding NULLs.
The new opt-in behaviour is treating all nulls as the same value, and thus allowing a UNIQUE index to constraint them.
This required two different partial indexes, or for constrained data subset in hacks like COALESCE-ing nulls to a sentinel.