As you may probably know, I am a big fan of PostgreSQL. That doesn’t mean that it’s always easy for me to use or intuitive, though. Today I ran into something strange (which is not that strange when you think of it, but still).
Consider a table with a jsonb field, which sometimes has some key and sometimes not (which is the main point of having a jsonb field after all):
# create temp table jsonb_gotcha (content jsonb);
CREATE TABLE
# insert into jsonb_gotcha values ('{"key": "value"}'), ('{}');
INSERT 0 2
# table jsonb_gotcha ;
content
------------------
{"key": "value"}
{}
(2 rows)
Now, if you say select * from jsonb_gotcha where content->>'key' =
'value';, you will get one row, which is what you certainly expect. However, you might expect this query:
select * from jsonb_gotcha where content->>'key' != 'value';
to select the other row (I certainly did!). Well, no – it selects nothing. When you think about it, it makes sense (kind of), since for that second row, content->>'key' is null, and hence is neither equal, nor not equal to anything. (Indeed, select * from
jsonb_gotcha where content->>'key' is null; selects it all right.) For me, the confusion is here mainly because SQL null and JSON null have very different semantics (“unknown” vs. “nothing”). Well, at least now I know about this behavior…