Hi
What I am talking about is that jsonb_set(..., ..., NULL) returns SQL NULL.
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.
on second hand - PostgreSQL design is one possible that returns additional information if value was changed or not.
Unfortunately It is very low probably so the design of this function will be changed - just it is not a bug (although I fully agree, it has different behave than has other databases and for some usages it is not practical). Probably there will be some applications that needs NULL result in situations when value was not changed or when input value has not expected format. Design using in Postgres allows later customization - you can implement with COALESCE very simply behave that you want (sure, you have to know what you do). If Postgres implement design used by MySQL, then there is not any possibility to react on situation when update is not processed.
Is not hard to implement second function with different name that has behave that you need and you expect - although it is just
CREATE OR REPLACE FUNCTION jsonb_modify(jsonb, text[], jsonb)
RETURNS jsonb AS $$
SELECT jsonb_set($1, $2, COALESCE($3, "null"::jsonb), true);
$$ LANGUAGE sql;
It is important to understand so JSON NULL is not PostgreSQL NULL. In this case is not problem in PostgreSQL design because it is consistent with everything in PG, but in bad expectations. Unfortunately, there are lot of wrong expectations, and these cannot be covered by Postgres design because then Postgres will be very not consistent software. You can see - my function jsonb_modify is what you are expect, and can works for you perfectly, but from system perspective is not consistent, and very strong not consistent. Users should not to learn where NULL has different behave or where NULL is JSON__NULL. Buildin functions should be consistent in Postgres. It is Postgres, not other databases.
Pavel
Ariadne
