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

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.vondra@2ndquadrant.com>
wrote:

On Fri, Mar 6, 2020 at 1:52 PM Radics Geza <radicsge@gmail.com> 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

Works for me. Please show a complete working example, with CREATE TABLE,
INSER, and an UPDATE statement with legal table name and with balanced
quotes.

Cheers,

Jeff

On Mar 6, 2020, at 11:21, Radics Geza <radicsge@gmail.com> wrote:

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)

I'm still not able to reproduce it on 11.7:

xof=# create table t (j jsonb);
CREATE TABLE
xof=# insert into t values('[{"b": 1}]');
INSERT 0 1
xof=# update t set j = '[{"a": 1}]';
UPDATE 1
xof=# table t;
j
------------
[{"a": 1}]
(1 row)

xof=# update t set j = '[]';
UPDATE 1
xof=# table t;
j
----
[]
(1 row)

--
-- Christophe Pettus
xof@thebuild.com

Sorry you are right it seems one of the triggers caused an issue on my table, then please ignore.
I will check it further.

Thanks a lot for your time!

‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
On Friday, March 6, 2020 7:24 PM, Christophe Pettus <xof@thebuild.com> wrote: