Re: JSON array is not updated if updating with empty array

I should have written that the precondition is that the column should
contains a non-empty json array before the update as in the example.
(The insert works, the update also works if the previous content was not an
array before e.g. null or '{}' but not as above)

On Fri, Mar 6, 2020 at 7:18 PM Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>
wrote:

> On Fri, Mar 06, 2020 at 06:52:43PM +0000, Radics Geza wrote:
> >Json column is not updated if the update is an empty array '[]'.
> >
> >UPDATE table SET "jsonbcol" = '[{"a":1}]';
> >UPDATE table SET "jsonbcol = '[]' RETURNING "jsonbcol";
> >
> >jsonbcol
> >------------
> > [{"a": 1}]
> >(1 row)
> >
> >It worked in postgres 9.6, but not in 11.7 / 12.2
> >
> >thanks!
>
> I can't reproduce this (I've tried on 12.2 and 13devel):
>
> test=# create table t (x jsonb);
> CREATE TABLE
> test=# insert into t values ('[]');
> INSERT 0 1
> test=# select * from t;
> x
> ----
> []
> (1 row)
>
> test=# UPDATE t SET "x" = '[{"a":1}]';
> UPDATE 1
> test=# select * from t;
> x
> ------------
> [{"a": 1}]
> (1 row)
>
> test=# UPDATE t SET "x" = '[]';
> UPDATE 1
> test=# select * from t;
> x
> ----
> []
> (1 row)
>
> ISTM you have a typo in the second query - you're missing the closing "
> after the column name, so the command is not really complete/executed.
>
>
> regards
>
> --
> Tomas Vondra http://www.2ndQuadrant.com
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>