News for hackers. Stuff that matters.
Re:Postgres 15 improves UNIQUE and NULL by SnowHill9902 on Monday July 11, 2022 @08:17AM (#32055132)
NULL means that the value is unknown, not that the data is unknown. You indeed have no data but its value may exist. You just don’t know it yet. NULL is very powerful when you understand that.
Re:Postgres 15 improves UNIQUE and NULL by ryanbrunner on Monday July 11, 2022 @08:33AM (#32055284)
That seems like a fairly big assumption on how I'm modelling the data though. Particularly in the LEFT JOIN example, I'm not sure if I agree with the idea of "a row not being present means that it's unknown whether it exists in the real world" depending on the underlying content being modelled.

For a string column, you do have blanks vs. NULLs, so it's fairly easy to distinguish between "John definitely doesn't have a middle name" vs "It's unknown whether John has a middle name", but that doesn't hold true with most other datatypes.

Re:Postgres 15 improves UNIQUE and NULL by SnowHill9902 on Monday July 11, 2022 @08:40AM (#32055380)
The string ‘’ is as arbitrarily blank as 0 is a blank integer.
Re:Postgres 15 improves UNIQUE and NULL by wongarsu on Monday July 11, 2022 @09:43AM (#32056021)
There are two "blank" integers: 0 is the identity element under addition, 1 is the identity element under multiplication. Or worded differently 0 is a blank addend, 1 is a blank multiplier.

‘’ would be the identity element for string concatenation, or any other string manipulation method I can think of, so it has a stronger claim on being blank than a 0 has.

Unless your string is later parsed as something else. A blank JSON value would look differently from a blank phone number.

Re:Postgres 15 improves UNIQUE and NULL by SnowHill9902 on Monday July 11, 2022 @10:02AM (#32056271)
Sure, but it’s arbitrary from a data structure standpoint and sloppy design.
Re:Postgres 15 improves UNIQUE and NULL by ryanbrunner on Monday July 11, 2022 @11:16AM (#32057266)
SQL gives you no other options though, there's no other construct that means "not present", since NULL is unknown vs. not present.
Re:Postgres 15 improves UNIQUE and NULL by SnowHill9902 on Monday July 11, 2022 @11:38AM (#32057555)
It depends on your column. If you have a products table and a certain handmade product has no upc, it’s correct to use ‘’ as its value rather than NULL because you know for a fact that its non-existent. Some people respect NULL too much.
Re:Postgres 15 improves UNIQUE and NULL by woevdbz on Monday July 11, 2022 @10:16AM (#32056453)
It is mostly true but not always, eg in the output of GROUP BY ROLLUP, a NULL value is used to indicate rows representing subtotals / partial aggregates