On Thu, Jul 4, 2019 at 6:18 AM Thomas Kellerer <spam_eater(at)gmx(dot)net> wrote:
> Why does
>
> select jsonb_set('{"foo": 1}'::jsonb, '{bar}', to_jsonb(null::int),
> true)
>
> return NULL when all it should do is to add a second key?
>
Both functions involved are defined as being STRICT (null on null input).
You need to COALSESCE SQL NULL to JSON 'null' to obtain the result you
desire. This is a general truth when dealing with the JSON type in
PostgreSQL.
select jsonb_set('{"foo": 1}'::jsonb, '{bar}',
coalesce(to_jsonb(null::int), 'null'), true)
David J.