SQL nulls are weird
jirevwe.github.io
SQL nulls are weird
1–10 of 293 posts
Re: SQL nulls are weird
#2> 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, you can use `NULLS NOT DISTINCT` when creating a unique index [0]. I'm less familiar with other databases.
Re: SQL nulls are weird
#3NULL 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:
SELECT /* ... */
FROM accounts
WHERE email_address = '...'
AND deleted_at IS NOT NULL
If anything, you want to reverse this so someone has to go out of their way to explicitly select deleted accounts. There are multiple strategies for this eg using an active_accounts view or table.
Lastly, there are lots of potential reasons for an account to be disabled or otherwise not visible/accessible. Takedowns, court orders, site safety, hacked accounts and so on.
Overloading deleted_at to have a semantic meaning for an active account is just fundamentally bad design.
Re: SQL nulls are weird
#4SQL 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.
Re: SQL nulls are weird
#5I expected the article to mention how in Oracle NULLs are equal to empty strings. Now that is weird.
Re: SQL nulls are weird
#6> 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…
>also that since Postgresql 15, you can use `NULLS NOT DISTINCT` when creating a unique index [0]. I'm less familiar with other databases.
Why would anyone want to use another database?
Re: SQL nulls are weird
#7SQL NULLs are not weird once you consider how you want relational logic to work when they is a record with non-existent values.
Re: SQL nulls are weird
#8I expected the article to mention how in Oracle NULLs are equal to empty strings. Now that is weird.
Domain-embedded nulls are the bane of my existence.
Re: SQL nulls are weird
#9> 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…
>also that since Postgresql 15, you can use `NULLS NOT DISTINCT` when creating a unique index [0]. I'm less familiar with other databases. Why would anyone want to use another database?
Simplicity. PG is often overkill for simple apps, where MySQL/Maria/et al is easier to maintain, or even SQLite for very simple apps where zero DB maintenance is preferable.
Re: SQL nulls are weird
#10The NULLs in unique constraints quirk actually works differently in ORACLE databases, which is infuriating to say the least. Apparently this comes from some ambiguity in some sql standard, anyone know more about this?