News for hackers. Stuff that matters.

A comment on Handling Null Values in Postgres.

Re:Handling Null Values in Postgres by ainar-g on Thursday March 05, 2020 @12:41PM (#22495912)
They forgot "IS NOT DISTINCT FROM", which is, IMHO, the best god damn operator ever invented. It works like "=", but adequately responds to NULL:

  postgres=# SELECT 1 IS NOT DISTINCT FROM 1;
   ?column? 
  ----------
   t
  (1 row)
  
  postgres=# SELECT NULL IS NOT DISTINCT FROM NULL;
   ?column? 
  ----------
   t
  (1 row)
[ Parent | on HN ]
Re:Handling Null Values in Postgres by oefrha on Thursday March 05, 2020 @02:02PM (#22496825)
Came here to say the same thing. An article about NULL without even a passing mention of IS DISTINCT FROM is embarrassing.

Truth table here: https://wiki.postgresql.org/wiki/Is_distinct_from#Truth_Tabl...

[ Parent | on HN ]
Re:Handling Null Values in Postgres by kingdomcome50 on Thursday March 05, 2020 @12:57PM (#22496099)
Wait... You don't like:

`ISNULL(NULLIF(A, B), NULLIF(B, A)) IS NULL`

What are you? human?

[ Parent | on HN ]
Re:Handling Null Values in Postgres by pavel_lishin on Thursday March 05, 2020 @04:58PM (#22498800)
Oh my god. We had a bug awhile back due to null equality comparison treating nulls as non-equal; I wish I'd known about mysql's equivalent ("<=>") back then!
[ Parent | on HN ]
Re:Handling Null Values in Postgres by ibrar74 on Friday March 06, 2020 @12:37PM (#22505518)
Actually this blog is not about how many ways you can use NULL in the database. It's all about the people who have programming experience (C/C++/JAVA) and try to write the SQL queries.
[ Parent | on HN ]
Re:Handling Null Values in Postgres by carterehsmith on Thursday March 05, 2020 @09:53PM (#22500630)
"SELECT NULL IS NOT DISTINCT FROM NULL" ?column? ---------- t

That bothers me. It is like saying that "infinity" (+inf) equals other infinity. Which is just wrong.

Some other SQL dialects will return "UNKNOWN" rather than "t" (true), which imho makes way more sense.

[ Parent | on HN ]
Re:Handling Null Values in Postgres by Tostino on Friday March 06, 2020 @12:03AM (#22501119)
...I think you are misunderstanding the whole reason for the IS NOT DISTINCT FROM operator, which is exactly that.

"SELECT NULL = NULL" will return NULL, use that if you want the standard way. There are many cases where you want it to return true though, and the boilerplate for that sucks, so IS NOT DISTINCT FROM has a very useful place in the dialect.

[ Parent | on HN ]