Unexpected behavior of jsonb_set() with a `null` value

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Unexpected behavior of jsonb_set() with a `null` value

Марк
Dear Support Team,

With `jsonb_set()`, when `new_value` is `NULL`, the function would *erase the whole object*:

postgres=# SELECT jsonb_set('{"something":1}'::jsonb, '{language}', null, true);         
jsonb_set  
-----------
 
(1 row)

This is really dangerous for it may lead to data loss. Imaging the `new_value` argument being a function which returns `NULL` values in some cases.
The expected behavior is, perhaps, to convert an SQL `NULL` value to JSONB null value: 'null'::jsonb
This way no data would be lost.

Or at the very least, this behavior should be documented.

Thank you in advance,
Mark
Reply | Threaded
Open this post in threaded view
|

Re: Unexpected behavior of jsonb_set() with a `null` value

Thomas Kellerer
Марк schrieb am 12.09.2019 um 12:55:

> With `jsonb_set()`, when `new_value` is `NULL`, the function would **erase the whole object**:
>
> postgres=# SELECT jsonb_set('{"something":1}'::jsonb, '{language}', null, true);         
> jsonb_set  
> -----------
>  
> (1 row)
>
> This is really dangerous for it may lead to data loss. Imaging the `new_value` argument being a function which returns `NULL` values in some cases.
> The expected behavior is, perhaps, to convert an SQL `NULL` value to JSONB null value: 'null'::jsonb
> This way no data would be lost.
>
> Or at the very least, this behavior should be documented.

I have asked the same: https://www.postgresql.org/message-id/flat/qfkua9%242q0e%241%40blaine.gmane.org

In short: not a bug ;)



Reply | Threaded
Open this post in threaded view
|

Re: Unexpected behavior of jsonb_set() with a `null` value

Tom Lane-2
Thomas Kellerer <[hidden email]> writes:
> Марк schrieb am 12.09.2019 um 12:55:
>> The expected behavior is, perhaps, to convert an SQL `NULL` value to JSONB null value: 'null'::jsonb

> I have asked the same: https://www.postgresql.org/message-id/flat/qfkua9%242q0e%241%40blaine.gmane.org

There are other more-apropos threads in the archives, though I'm too
lazy to go find them right now.  IMO the problem here is an assumption
that SQL NULL should be interchangeable with a JSON NULL.  It's not,
and we don't intend to make it so, because (a) the definitional quagmire
that would result is bottomless; (b) people asking for this are generally
trying to use SQL NULL as a live data value, which is almost always just
a bad idea.

                        regards, tom lane