Home > mailing lists

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

JSON array is not updated if updating with empty array

From

Radics Geza

Date:

06 March 2020, 21:52:43

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!

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

From

Tomas Vondra

Date:

06 March 2020, 22:18:14

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

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

From

Radics Geza

Date:

06 March 2020, 22:21:27

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 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

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

From

Jeff Janes

Date:

06 March 2020, 22:22:18

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

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

From

Christophe Pettus

Date:

06 March 2020, 22:24:16


> 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
updateas 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

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

From

"Radics, Geza"

Date:

06 March 2020, 22:33:23

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:

>
>
> > 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
updateas 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
asabove) 
>
> 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