Re: jsonb_set() strictness considered harmful to data

On Wed, Oct 23, 2019 at 4:42 AM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
wrote:

> David G. Johnston wrote:
> > Now if only the vast majority of users could have and keep this level of
> understanding
> > in mind while writing complex queries so that they remember to always
> add protections
> > to compensate for the unique design decision that SQL has taken here...
>
> You can only say that if you don't understand NULL (you wouldn't be alone).
> If I modify a JSON with an unknown value, the result is unknown.
> This seems very intuitive to me.
>
> One could argue that whoever uses SQL should understand SQL.
>
> But I believe that it is reasonable to suppose that many people who
> use JSON in the database are more savvy with JSON than with SQL
> (they might not have chosen JSON otherwise), so I agree that it makes
> sense to change this particular behavior.
>

I can and do understand SQL quite well and still likely would end up being
tripped up by this (though not surprised when it happened) because I can't
and don't want to think about what will happen if NULL appears in every
expression I write when a typical SQL query can contain tens of them. I'd
much rather assume that NULL inputs aren't going to happen and have the
system tell me when that assumption is wrong. Having to change my
expressions to: COALESCE(original_input, function(original_input,
something_that_could_be_null_in_future_but_cannot_right_now)) just adds
undesirable mental and typing overhead.

David J.