Postgres 15 adds new ways to handle UNIQUE and NULL
(1) By Simon Slavin (slavin) on 2022-07-11 14:26:45 [link] [source]
https://blog.rustprooflabs.com/2022/07/postgres-15-unique-improvement-with-null
"Allow unique constraints and indexes to treat NULL values as not distinct (Peter Eisentraut)
Previously NULL values were always indexed as distinct values, but this can now be changed by creating constraints and indexes using UNIQUE NULLS NOT DISTINCT."
Why am I posted stuff about Postgres here ? Well the issue of how to handle NULLs is left ambiguous by the SQL specification. A dedicated page
https://www.sqlite.org/nulls.html
was written about it. And when in doubt, the devs of SQLite often decide to make SQLite behave however PostgreSQL behaves. So I thought a change to it was relevant. I'm not saying any change needs to be made in SQLite.
(2) By Gwendal Roué (groue) on 2022-07-11 15:00:32 in reply to 1 [link] [source]
I would love UNIQUE NULLS NOT DISTINCT to be supported by SQLite :-)
There are valid use cases, such as this one:
Consider a "docs" table where each doc has a unique name, under a given parent doc. A null parent would be a top-level doc, and top-level docs just have a unique name. This didn't work before, and would hopefully be addressed by PG15.
(3) By anonymous on 2022-07-11 18:57:25 in reply to 2 [source]
Unless I am missing something, your use case can be handled by having root documents having themselves as parent. No need for NULLs at all.
However you choose to treat NULLs for the purpose of determining duplicates and check unique constraints (I think that this is the purpose of the new clause—please correct me if I am wrong), the fact remains that there are many other inconsistencies that make NULLs hopeless.
What is the value of a clause such as where x=x under the new semantics—when x is NULL, of course? Does it change to true?