2 ms·
> 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 a
by masklinn 4y ago
> 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.