Re: BUG #13961: Unexpected behaviour of JSONB_SET if the new_value is null

On Mon, Feb 15, 2016 at 1:02 AM, <pravin@gida.in> wrote:

> The following bug has been logged on the website:
>
> Bug reference:      13961
> Logged by:          Pravin Carvalho
> Email address:      pravin@gida.in
> PostgreSQL version: 9.5.1
> Operating system:   All
> Description:
>
> If the new_value is null, JSONB_SET evaluates to null. This is not
> mentioned
> in the documentation and intuitively I would expect this set the value of
> the key at the specified path as null.
> eg. SELECT jsonb_set('{"f1":1,"f2":2}', '{f2}',null);
>
> I was using JSONB_SET to update a JSONB column where the new_value was th=
e
> result of the query and this could have led to loss of data.
>
>
=E2=80=8BWorking as designed - though in retrospect I don't see why this pa=
rticular
function had to be defined "STRICT".

You will need to use:
COALESCE((SELECT ...), 'null') if you want to store a JSON null when the
subquery results in an SQL being returned.  The two are not the same thing.

The technical answer is that with jsonb_set defined as being "NULL ON NULL
INPUT (a.k.a., STRICT)" =E2=80=8Bas soon as any of its arguments are SQL NU=
LL the
executor replaces the function call with an SQL NULL without ever
attempting to execute the function.

I think that this point could be better made in the documentation=E2=80=8B =
for
these functions.  It is alluded to in the note for json_typeof (
http://www.postgresql.org/docs/9.5/interactive/functions-json.html) but
that is a bit detached from the situation you encountered.

David J.