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

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