Post AGFHbjKs16UqUIXfSy by cincodenada@cybre.space
 (DIR) More posts by cincodenada@cybre.space
 (DIR) Post #AGFHbjKs16UqUIXfSy by cincodenada@cybre.space
       2022-02-07T20:57:47Z
       
       1 likes, 1 repeats
       
       Your regular reminder that NULL in databases doesn't mean "an empty value", it means "I literally don't know what this value is, it could be 6 or 6000 or -23, who's to say?"This difference is important, because "tell me all the people where wearing_hat is not true" - you might think you'll get records where wearing_hat is NULL, but you won't! They could be wearing a hat or not, NULL means you don't _know_ whether they're wearing a hat. The fact represented by wearing_hat could be true, even if the database value is NULL.And of course, if you ask if NULL = NULL, you don't get true, you get get NULL. Is Jody's hat-wearing status the same as Olaf's? If you don't know whether either of them are wearing a hat, you can't say that their hat-wearing status is the same.Much of this is the case for null values in various languages, but this is pretty universally true for databases, and trying to work with them without understanding this will cause you much pain!
       
 (DIR) Post #AGFHbl7TNsy61KHzrU by cincodenada@cybre.space
       2022-02-07T21:08:29Z
       
       0 likes, 0 repeats
       
       ...a coworker telling me "nothing weird in the database, look I ran SELECT * FROM table WHERE field NOT IN('expected value', 'other expected value') and got nothing" and me saying "but what if you run SELECT * FROM table WHERE field IS NULL"Reader: there was something weird in the database, and it was 25 NULLs.Arguably, this state of the field is actually exactly the use case for NULL, but because this codebase was built from the perspective of "NULL is weird and breaks stuff", correctly setting it to NULL, well, breaks stuff. :blobpensiveleft:
       
 (DIR) Post #AGFHbmyKUqqJlY1it6 by cincodenada@cybre.space
       2022-02-07T21:11:31Z
       
       0 likes, 0 repeats
       
       I get very defensive of database designers and NULL because people are always like "NULL is the worst why would anyone ever make it work that way" and like, it's there for a reason, it fulfills a purpose, and it's solving a problem that's not easy to solve any other way.But also, yes it's a sharp edge that requires you to be aware of it, and that ties into learning curves and onboarding folks and "well if you just _know_ stuff you won't have this issue" is not a great answer. I don't know what the answer is.The obvious answer is "make NULL opt-in", but then you have to decide on an empty value besides NULL for every datatype, and that's not always obvious. What's the empty value for an image column? FALSE isn't always a safe bet for booleans. And if you try to pick a default value for a point on the earth's surface (which is a valid column type in PostGIS!) you end up with https://en.wikipedia.org/wiki/Null_Island🤷‍♂️