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

11–20 of 293 posts

Re: SQL nulls are weird

#11

Post by grahamlee »

datadrivenangel wrote: post #7

SQL 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.

Re: SQL nulls are weird

#12

Post by indeed30 »

jmyeet wrote: post #3

NULL 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…

That's interesting - I believe this is exactly how Sequelize implements soft-deletion.

Re: SQL nulls are weird

#13

Post by duncan-donuts »

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…

Introducing “unknown” feels like another kind of hell like undefined in JavaScript.

Re: SQL nulls are weird

#14

Post by lcnPylGDnU4H9OF »

stronglikedan wrote: post #9
NoMoreNicksLeft wrote: post #6

Earlier quoted context omitted.

>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.

Why would you say MySQL/Maria/et al are easier to maintain for simple apps than PG?

Re: SQL nulls are weird

#15

Post by demurgos »

lcnPylGDnU4H9OF wrote: post #14
stronglikedan wrote: post #9

Earlier quoted context omitted.

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.

Why would you say MySQL/Maria/et al are easier to maintain for simple apps than PG?

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.

Re: SQL nulls are weird

#16

Post by phplovesong »

lcnPylGDnU4H9OF wrote: post #14
stronglikedan wrote: post #9

Earlier quoted context omitted.

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.

Why would you say MySQL/Maria/et al are easier to maintain for simple apps than PG?

Usually its a skill issue

Re: SQL nulls are weird

#17

Post by password4321 »

NoMoreNicksLeft wrote: post #6
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…

>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?

Replication

Re: SQL nulls are weird

#19

Post by demurgos »

duncan-donuts wrote: post #13
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…

Introducing “unknown” feels like another kind of hell like undefined in JavaScript.

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.

Re: SQL nulls are weird

#20

Post by dunham »

zokier wrote: post #4

SQL 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.

Also similar to the bottom value in haskell and exceptions in other languages.
Post reply on HN