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
