Thread: Unexpected behavior of jsonb_set() with a `null` value
Dear Support Team,
postgres=# SELECT jsonb_set('{"something":1}'::jsonb, '{language}', null, true);
jsonb_set
-----------
(1 row)
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
Марк 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 ;)
Thomas Kellerer <spam_eater@gmx.net> 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