Re: jsonb_set() strictness considered harmful to data

On 19/10/2019 07:52, Ariadne Conill wrote:
>
> 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.

steve=# create table foo (bar jsonb not null);
CREATE TABLE
steve=# insert into foo (bar) values ('{"a":"b"}');
INSERT 0 1
steve=# update foo set bar = jsonb_set(bar, '{foo}', NULL);
ERROR:  null value in column "bar" violates not-null constraint
DETAIL:  Failing row contains (null).
steve=# update foo set bar = jsonb_set(bar, '{foo}', 'null'::jsonb);
UPDATE 1

I don't see any behaviour that's particularly surprising there? Though I
understand how an app developer who's light on SQL might get it wrong -
and I've made similar mistakes in schema upgrade scripts without
involving jsonb.

Cheers,
  Steve