Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

SQL nulls are weird

jirevwe.github.io

Post reply on HN
Print view View original

51–60 of 293 posts

Re: SQL nulls are weird

#51

Post by bunderbunder »

foobarchu wrote: post #43
mrkeen wrote: post #34

Earlier quoted context omitted.

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.

The distinction is that not all formal logic systems are Boolean. Meaning that it is nonsensical and confusing to use "Boolean" as a generic synonym for "truth value" in the same way that it's nonsensical to use "Pantone" as a generic synonym for "color value", including when the specific kind of color value you're talking about is CMYK or HSV and definitely not Pantone.

Re: SQL nulls are weird

#52

Post by Volundr »

throwaway10235 wrote: post #21
demurgos wrote: post #15

Earlier 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

It's not client side, it's server side. The binary format between Postgres major versions is generally not compatible so you need to do a pg_dump from the old database and do a pg_restore into the new one. pg_upgrade [1] can let you bypass this by migrating the binary data directly, but still requires having both the new and old postgres versions installed. There's also things you can do with replication, but since we're talking simplicity I don't think that really applies.

Personally I think upgrades are the one thing MySQL has on Postgres at this point.

[1] https://www.postgresql.org/docs/current/pgupgrade.html

Re: SQL nulls are weird

#53

Post by demurgos »

wvenable wrote: post #26
demurgos wrote: post #19

Earlier quoted context omitted.

Just to clarify, I'm not advocating to introduce a new `unknown` keyword. I'm saying that the existing `null` in SQL was not named properly and that the name `unknown` would have been more fitting. SQL's `null` already has the semantics of `unknown` as explained in the part of the article that I quoted.

SQL's use of "null" is probably one of the oldest instances of that concept in computing. It's exactly equivalent to unknown. That is its definition.

Really? I know that SQL is old but I would have expected `null` to refer to pointers at first.

Going by Wikipedia, I see that SQL is from 1974 and C from 1972. Were there earlier uses/drafts where `null` is "unknown" instead of "unset"?

Re: SQL nulls are weird

#54

Post by hyperman1 »

irrational wrote: post #5

I expected the article to mention how in Oracle NULLs are equal to empty strings. Now that is weird.

Oh man. I had a talk with a DBA about how oracle could not deal with an adress with no street name - literally a tiny village with 10 houses on 1 nameless town square. It was unsearchable in parts of the app because street='' was interpreted as street is null. DBA kept claiming oracle was right and the town should adapt their naming to our software.

This attitude was so prevalent at the time, I sometimes wonder if the rise of noSQL was simply people sick of dealing with Oracle DBAs

Re: SQL nulls are weird

#55

Post by wvenable »

demurgos wrote: post #53
wvenable wrote: post #26

Earlier quoted context omitted.

SQL's use of "null" is probably one of the oldest instances of that concept in computing. It's exactly equivalent to unknown. That is its definition.

Really? I know that SQL is old but I would have expected `null` to refer to pointers at first. Going by Wikipedia, I see that SQL is from 1974 and C from 1972. Were there earlier uses/drafts where `null` is "unknown" instead of "unset"?

I wouldn't necessarily define `null` as "unknown" -- it's just "no value" -- which is really the same thing and also somewhat equivalent to "unset". But null pointers aren't unset as pointers aren't initialized to null in C and you can explicitly set a pointer to null.

E.F. Codd added nulls to relational model in 1970 so that does pre-date C. The concept is even older than that I imagine.

Re: SQL nulls are weird

#56

Post by mardifoufs »

kstrauser wrote: post #45
badlibrarian wrote: post #22

Earlier quoted context omitted.

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.

Sure, it's never going to be plug and play, but it doesn't mean that all the issues will be equivalent. Vacuuming doesn't really have an equivalent in say, MySQL. It's something you don't have to worry about if you use the latter.

For example, HA and clustering will always be challenging to deploy/maintain, but you will still have a harder time doing that with postgres than with MySQL. Postgres also has a lot of benefits obviously, though.

Re: SQL nulls are weird

#57

Post by blast »

kurtbuilds wrote: post #41

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

Having that is much better than not having it, but man is it verbose and confusing.

Re: SQL nulls are weird

#58

Post by otteromkram »

magicalhippo wrote: post #25
demurgos wrote: post #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, y…

The result of comparisons involving NULL values can result[1][2] in UNKNOWN, and in PostgreSQL for example you can test[3] for this using IS UNKNOWN. That said, as someone self-taught in SQL, I agree NULL was not a good choice. Replacing NULL with UNKNOWN and the third boolean value as INDETERMINATE for example would have been better. [1]: https://stackoverflow.com/a/79270181 [2]: https://learn.microsoft.com/en-us/sq…

Also self-taught SQLer and I don't have an issue with NULL.

I also don't use UNIQUE constraints, so maybe that has something to do with it.

Post reply on HN