Re: jsonb_set() strictness considered harmful to data

On Fri, 2019-10-18 at 21:18 -0500, Ariadne Conill wrote:
> postgres=# \pset null '(null)'
> Null display is "(null)".
> postgres=# select jsonb_set('{"a":1,"b":2,"c":3}'::jsonb, '{a}', NULL);
> jsonb_set
> -----------
> (null)
> (1 row)
>
> This behaviour is basically giving an application developer a loaded
> shotgun and pointing it at their feet. It is not a good design. It
> is a design which has likely lead to many users experiencing
> unintentional data loss.

I understand your sentiments, even if you voiced them too drastically for
my taste.

The basic problem is that SQL NULL and JSON null have different semantics,
and while it is surprising for you that a database function returns NULL
if an argument is NULL, many database people would be surprised by the
opposite. Please have some understanding.

That said, I think it is reasonable that a PostgreSQL JSON function
behaves in the way that JSON users would expect, so here is my +1 for
interpreting an SQL NULL as a JSON null in the above case, so that the
result of the above becomes
{"a": null, "b": 2, "c": 3}

-1 for backpatching such a change.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com