If you want equality testing with nulls, you want to use `is (not) distinct from` instead of `=` and `` / `!=`.
`1 is not distinct from NULL` => false
`NULL is not distinct from NULL` => true
`0 is not distinct from 1` => false
SQL nulls are weird
41–50 of 293 posts
Re: SQL nulls are weird
#42> select null = null; returns NULL, because each NULL is basically a placeholder representing any “unknown value”. Two unknown values are not necessarily the same value; we can’t say that they are equal, because we don’t know the value of either of them. Agreed with all of this, it would probably have been better if they were named `unknown` instead of reusing the `null` keyword. Note also that since Postgresql 15, y…
SQL NULL doesn’t behave like “unknown” in all contexts. That’s one issue of NULL, that it doesn’t really have consistent semantics.
Furthermore if null only means unknown then we need a value for “known absent”, there’s a reason why null is so often used as that.
Re: SQL nulls are weird
#43SQL NULLs aren't weird , they're just based off of Kleene's TRUE-FALSE-UNKNOWN logic! If you want you can read NULL as UNKNOWN and suddenly a whole bunch of operations involving them become a lot more intuitive: 1. TRUE OR UNKNOWN = TRUE, because you know you have at least one TRUE already. 2. TRUE AND UNKNOWN = UNKNOWN, because you don't know whether you have two TRUEs or not. It's just out there. 3. UNKNOWN XOR UNK…
It's unfortunate to namesquat on 'boolean' if your elements have three possible values. Just call it 'ternary logic' and let individuals decide which system to use.
Who's name squatting boolean? The bool column is exactly what it claims to be, you just have the option of introducing unknowability if you define it allow nulls.
Re: SQL nulls are weird
#44NULL is the absence of a value. If you try and treat it as a value, you're going to have a bad time. So an attempted UNIQUE(email_address, deleted_at) constraint is fundamentally flawed. If you treated NULL as a value that could be unique, you're going to break foreign keys. But let's continue the logic of deleted_at being NULL indicating an active account, which seems to the intent here. You end up doing things like…
> Overloading deleted_at to have a semantic meaning for an active account is just fundamentally bad design.
Then don't do that. It's kind of a leap to say soft deletes are categorically bad because someone might confuse "deleted" with "inactive". My users table does the super-advanced thing of having both columns. The ORM also doesn't forget to add the not-null criterion. There's also zero databases in active use where it poses a problem to indexing.
Soft deletes suck in their own way, but none of the alternatives are perfect either.
Re: SQL nulls are weird
#45Earlier quoted context omitted.
Why would you say MySQL/Maria/et al are easier to maintain for simple apps than PG?
VACUUM
If your data's large and changing enough that you have to care about vacuuming, any reasonable database is going to require some tuning, tending and management.
I'd posit that only a tiny fraction of PostgreSQL uses have to know or care that vacuuming is a thing because the autovacuum default handle it for them.
Re: SQL nulls are weird
#46SQL nulls in some ways behave in similar to floating point nans. Of course nans are also weird in their own way, but it is a bit comforting that its not so completely singularly weird.
NaN is cool because it's almost like a type that constrains uncertainty. What do we know about this entity? not much! but it's definitely not a number. Calling it anything else would have been a mistake. Null is more confusing because it means different things in different languages. Sometimes it's a more constrained uncertainty, eg this definitely doesn't exist. But in sql it's a less constrained uncertainty, like "…
There's another comment in here that talks about thinking of NULL as UNKNOWN, and I quite like that. It makes a lot more sense, and I think it would've been a better choice to standardize on. UNDEFINED would also be an improvement.
Re: SQL nulls are weird
#47SQL nulls in some ways behave in similar to floating point nans. Of course nans are also weird in their own way, but it is a bit comforting that its not so completely singularly weird.
NaN is cool because it's almost like a type that constrains uncertainty. What do we know about this entity? not much! but it's definitely not a number. Calling it anything else would have been a mistake. Null is more confusing because it means different things in different languages. Sometimes it's a more constrained uncertainty, eg this definitely doesn't exist. But in sql it's a less constrained uncertainty, like "…
Except it is of type number, at least in JavaScript.
Re: SQL nulls are weird
#48> ... and this is even less obvious if you’re used to using ORMs. Which is why I continue to be such an ORM skeptic. I agree that they're convenient. But I do worry that we've now got an entire generation of engineers who regularly interact with relational databases, but have largely been spared the effort of learning how they actually work. As another commenter pointed out, if you've learned basic relational algebra…
My experience with ORMs is that most of the time you end up needing to write some queries by hand, in raw SQL. Usually these are the most complex, that you can't express in your ORM's DSL. My point being, I don't think using an ORM really shields you from having to learn how it works behind the scenes.
Re: SQL nulls are weird
#49Earlier quoted context omitted.
The main pain point for smaller apps is that every major Postgres version requires an explicit migration of the underlying data representation. I get why it's there, but for simpler apps I would appreciate a flag to do it transparently.
I'm not sure what you mean. I have migrated versions without having to update any applications that connects to it? Maybe it is a driver specific issue? I have used Python/Java, and haven't updated any of my code or dependencies because of a major Postgre update
You need to dump the database on the old PostgreSQL version and then load the dump on the new PostgreSQL version. Some other database servers can just use the old table data or migrate the data files on the fly transparently.
Re: SQL nulls are weird
#50SQL NULLs are not weird once you consider how you want relational logic to work when they is a record with non-existent values.
Exactly this. SQL is based on the relational algebra and that's well-defined, NULL along with other features of SQL work in an entirely regular and predictable way. The only time it's weird is when a developer decides that it should work the way Javascript (or whatever) NULLs work because that's the last time they saw the same word used in a programming language, in which case it's the assumption that's weird.
That's not the only time it is weird. There's even a whole book by one of the pioneers of the relational DB model, Date's "Database Technology: Nulls Considered Harmful" [1], covering many of the ways it is weird.
[1] https://www.amazon.com/Database-Technology-Nulls-Considered-...