Re: jsonb_set() strictness considered harmful to data

Hello,

On Sat, Oct 19, 2019 at 12:52 AM Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>
>
>
> so 19. 10. 2019 v 7:41 odesílatel David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> napsal:
>>
>> On Friday, October 18, 2019, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>>
>>>
>>> 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.
>>
>>
>> A CASE expression seems like it would work well for such detection in the rare case it is needed. Current behavior is unsafe with minimal or no redeeming qualities. Change it so passing in null raises an exception and make the user decide their own behavior if we don’t want to choose one for them.
>
>
> How you can do it? Buildn functions cannot to return more than one value. The NULL is one possible signal how to emit this informations.
>
> The NULL value can be problem everywhere - and is not consistent to raise exception somewhere and elsewhere not.
>
> I agree so the safe way is raising exception on NULL. Unfortunately, exception handling is pretty expensive in Postres (more in write transactions), so it should be used only when it is really necessary.

I would say that any thing like

update whatever set column=jsonb_set(column, '{foo}', NULL)

should throw an exception. It should do, literally, *anything* else
but blank that column.

Ariadne