News for hackers. Stuff that matters.

A comment on Postgres 15 improves UNIQUE and NULL. Parent comment

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.

[ Parent | on HN ]
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.
[ Parent | on HN ]
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.

[ Parent | on HN ]
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.
[ Parent | on HN ]
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.
[ Parent | on HN ]
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.
[ Parent | on HN ]