News for hackers. Stuff that matters.
Re:Postgres 15 improves UNIQUE and NULL by masklinn on Monday July 11, 2022 @10:58AM (#32057023)
> SQL is old enough and this debate so unsettled

Is the debate really unsettled?

MSSQL considers nulls to be equal, everyone else considers them to be distinct (aside from a few DBs which just refuse to create UNIQUE indexes on nullable columns), and Oracle is apparently huffling glue in a corner.

> As an example of the irreconciliable weirdness of NULL, consider that "NULL = NULL" is false, and so is "NULL != NULL", while rows with NULL still group together in GROUP BY.

Group by is the exception here. And arguably it is so because the alternative is never useful, or close enough as to not be worth it.

Re:Postgres 15 improves UNIQUE and NULL by feanaro on Monday July 11, 2022 @11:28AM (#32057411)
So of the three major SQL implementations, you have one of them following the results of the settled debate, one of them being an exception and one of them huffling glue (whatever that means). In addition, there's a language exception even in the settled group, which you have to handwave away.

Sounds pretty unsettled to me.

Re:Postgres 15 improves UNIQUE and NULL by irrational on Monday July 11, 2022 @11:37AM (#32057539)
Oracle considers null to be equal to empty string.
Re:Postgres 15 improves UNIQUE and NULL by mike_hock on Monday July 11, 2022 @03:18PM (#32060489)
That is truly huffing glue.
Re:Postgres 15 improves UNIQUE and NULL by irrational on Monday July 11, 2022 @11:36AM (#32057532)
And oracle considers null to be equal to empty string. My understanding is oracle was created before the sql standard existed and they don’t want to change because of backwards comparability. I don’t understand why they can’t add a db parameter to allow you to set how nulls are handled.
Re:Postgres 15 improves UNIQUE and NULL by to11mtm on Monday July 11, 2022 @03:16PM (#32060467)
IDK if you ever saw the HN thread where they talked about oracle and change tests from a few years back, but if I had to guess, the pain just isn't worth the overall cost.

Overall I remember running into this but finding it fairly simple to add a couple specific checks around the condition where it came up in our code.

Re:Postgres 15 improves UNIQUE and NULL by layer8 on Monday July 11, 2022 @01:43PM (#32059237)
They would need to change their storage format to be able to distinguish between NULL and empty string, and probably a lot of in-memory structures and even more code logic as well. It would be hugely expensive to support both.

Oracle introduced VARCHAR2 in the 90s(?) to mean “Oracle semantics” as opposed to VARCHAR which is documented as “may change to standard SQL semantics in a future version”. However I don’t think they’ll ever materialize that distinction.