Null is still null, sql null is always “weird” wherever it exists.

Why is null weird? Null means unknown. It's perfectly valid.

SQL uses [0] 3 valued logic, and most people seem to reason using 2-valued logic. So most people fund the behaviour of NULL, the 3rd logical value, to be weird.

> It's perfectly valid.

:o You'll get jumped on, saying this to a forum of software people. Lots of things that are valid are also weird.

[0] https://www.postgresql.org/docs/14/functions-logical.html

“Valid” and “weird” are orthogonal concepts.

That sql null is valid doesn’t make its behaviour of not being equal to itself any less weird, because it works that way essentially nowhere else, and thus is highly unintuitive to developers who don’t live and breathe sql.

The closest thing in most langages is nan and developers also find nan weird.

> The closest thing in most langages is nan and developers also find nan weird.

I'd argue the closest thing in most languages is the null propagation operator (usually using a ? symbol) that at least JavaScript and C# have.

YMMV, but personally the unique index behaviour was the main thing I found weird about NULLs. So now that I can turn that off I'm pretty happy with them.

> I'd argue the closest thing in most languages is the null propagation operator (usually using a ? symbol) that at least JavaScript and C# have.

In what sense? null-coalescing operators don’t change how nulls behave or relate to one another, they only provide convenience operators for conditioning operation upon them, like sql’s COALESCE.

Think of nulls like different infinities. Two infinities may not be equal.

I find it quite useful to know when something is null though I have a hard time explaining it.

It is kinda weird, if you don't know how it works. For instance:

Say you have a unique constraint on two columns (column_a, column_b). column_a is not nullable, column_b IS nullable. Obviously these values are unique:

(1, 2) (1, 3) (2, 2)

But these values ARE ALSO UNIQUE:

(1, NULL) (1, NULL)

It is obvious in hindsight (NULL isn't equal to NULL), but can be quite a surprise.

...

Oh wait, postgres 15 deals with just this situation. Huh.

Honestly it's what you want way more often than not. A unique index on a nullable column is usually representing some optional identifier. You wouldn't usually want only one row to be able to omit the identifier.

Oh yeah, totally. But not always. I'm very glad that we now have an option to have both behaviours.

It makes lots of queries (like find duplicate queries) much more complex without any benefit. Also it introduces completely unnecessary three value logic in SQL.

That’s a bold claim. There are constructs to handle nulls for such queries.

With keys it very often means "definitively not present" in practice, and there's no other value that you can use to represent that if you want referential integrity.