4 ms·

Cool but this was trivially solved previously by excluding NULL from the index using a partial index. I'm more interested in problems that have no great soluti

Cool but this was trivially solved previously by excluding NULL from the index using a partial index. I'm more interested in problems that have no great solution, like taking the first element of a set of partitioned data. Right now you have to partition & rank, and then select the rank 1 elements in a subsequent query.

jhugo 4y ago
Maybe it's what you meant be "subsequent query", but you don't actually need to do it in a separate query; you can do the partition & rank in a CTE and select from the resulting table, all in a single query. It's quite tidy actually.
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.
_flux 4y ago
I don't think you understood what this does (or then I misunderstood this) but if you do that, then how are you going to get an index violation from having two NULL values in the index? Because that is what this allows: normally indexing NULL values permitted multiple NULL entries, but with this NULL is comparable to other NULLs, therefore you can have only one NULL value in the index. Previously I had worked this around with functional index on e.g. COALESCE(value_with_null, -1), but not always you have a good sentinel value easily available. A more complicated index (..CASE WHEN..) would solve that, but it's nice to have this directly expressible, seems efficient as well.
silon42 4y ago
The new behavior is more useful because it allows a single unique null. For 2, have you looked at "LATERAL JOIN" ?

jhugo 4y ago

Maybe it's what you meant be "subsequent query", but you don't actually need to do it in a separate query; you can do the partition & rank in a CTE and select from the resulting table, all in a single query. It's quite tidy actually.

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.

_flux 4y ago

I don't think you understood what this does (or then I misunderstood this) but if you do that, then how are you going to get an index violation from having two NULL values in the index? Because that is what this allows: normally indexing NULL values permitted multiple NULL entries, but with this NULL is comparable to other NULLs, therefore you can have only one NULL value in the index. Previously I had worked this around with functional index on e.g. COALESCE(value_with_null, -1), but not always you have a good sentinel value easily available. A more complicated index (..CASE WHEN..) would solve that, but it's nice to have this directly expressible, seems efficient as well.

silon42 4y ago

The new behavior is more useful because it allows a single unique null. For 2, have you looked at "LATERAL JOIN" ?