Some experimentation:
> \pset null '((null))'
> select jsonb_set('{"foo":1}'::jsonb,'{bar}','null'::jsonb,true);
┌─────────────────────────┐
│ jsonb_set │
├─────────────────────────┤
│ {"bar": null, "foo": 1} │
└─────────────────────────┘
No SQL null, ok
> select jsonb_set('{"foo":1}'::jsonb,'{bar}',to_jsonb(null::text), true);
┌───────────┐
│ jsonb_set │
├───────────┤
│ ((null)) │
└───────────┘
Sql null poisons the _expression_ and so sql null is the result
That's a bit weird already. Also:
> select null::jsonb;
┌──────────┐
│ jsonb │
├──────────┤
│ ((null)) │
└──────────┘
Sql null
> select 'null'::jsonb;
┌───────┐
│ jsonb │
├───────┤
│ null │
└───────┘
Json null
> select to_jsonb(null::int);
┌──────────┐
│ to_jsonb │
├──────────┤
│ ((null)) │
└──────────┘
Sql null poisons the function call which immediately returns sql null
> select to_jsonb('null'::text);
┌──────────┐
│ to_jsonb │
├──────────┤
│ "null" │
└──────────┘
Json null
I'm sharing Thomas's confusion…
Sql null and json null are represented differently; strict functions with sql null inputs yield sql null output without even executing the function
David J.
