Thanks for the explanation David. I had missed the distinction between =
the SQL null and the JSON null. It would be great if this could be =
mentioned more prominently in the JSON functions documentation.
You could also still consider making this function non strict and =
storing a JSON null if the value is a either a SQL/JSON null.=20
> On 15-Feb-2016, at 11:33 PM, David G. Johnston =
<david.g.johnston@gmail.com> wrote:
>=20
> On Mon, Feb 15, 2016 at 1:02 AM, <pravin@gida.in =
<mailto:pravin@gida.in>> wrote:
> The following bug has been logged on the website:
>=20
> Bug reference: 13961
> Logged by: Pravin Carvalho
> Email address: pravin@gida.in <mailto:pravin@gida.in>
> PostgreSQL version: 9.5.1
> Operating system: All
> Description:
>=20
> 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);
>=20
> I was using JSONB_SET to update a JSONB column where the new_value was =
the
> result of the query and this could have led to loss of data.
>=20
>=20
> =E2=80=8BWorking as designed - though in retrospect I don't see why =
this particular function had to be defined "STRICT".
>=20
> 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.
>=20
> 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 NULL the executor replaces the function call with an SQL NULL =
without ever attempting to execute the function.
>=20
> 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 =
<http://www.postgresql.org/docs/9.5/interactive/functions-json.html>) =
but that is a bit detached from the situation you encountered.
>=20
> David J.
>=20
>=20