Thread: jsonb merge with update ... on conflict do
Hi guys!
Is it a bug or do I miss something?
$ psql xbox -X
psql (13.2 (Ubuntu 13.2-1.pgdg20.04+1))
Type "help" for help.
xbox=> create temp table test(id int primary key, price jsonb);
CREATE TABLE
xbox=> insert into test values(1,'{"RU":10}');
INSERT 0 1
xbox=> select * from test;
id | price
----+------------
1 | {"RU": 10}
(1 row)
xbox=> insert into test values(1,'{"CA":20}') on conflict(id) do update set price=EXCLUDED.price || '{"CA":20}'::jsonb;
INSERT 0 1
xbox=> select * from test;
id | price
----+------------
1 | {"CA": 20}
(1 row)
xbox=> \q
$
Type "help" for help.
xbox=> create temp table test(id int primary key, price jsonb);
CREATE TABLE
xbox=> insert into test values(1,'{"RU":10}');
INSERT 0 1
xbox=> select * from test;
id | price
----+------------
1 | {"RU": 10}
(1 row)
xbox=> insert into test values(1,'{"CA":20}') on conflict(id) do update set price=EXCLUDED.price || '{"CA":20}'::jsonb;
INSERT 0 1
xbox=> select * from test;
id | price
----+------------
1 | {"CA": 20}
(1 row)
xbox=> \q
$
Expected:
{"CA":20,"RU":10}
--
Evgeny Pazhitnov
Hello
You meant
... do update set price=test.price || '{"CA":20}'::jsonb;
? Or possibly
... do update set price=test.price || EXCLUDED.price;
?
In other words, why did you expect the result {"CA":20,"RU":10} ?
regards, Sergei
Thanks a lot, Sergei! My fault. Works like a charm now.
вт, 1 июн. 2021 г. в 14:58, Sergei Kornilov <sk@zsrv.org>:
Hello
You meant
... do update set price=test.price || '{"CA":20}'::jsonb;
? Or possibly
... do update set price=test.price || EXCLUDED.price;
?
In other words, why did you expect the result {"CA":20,"RU":10} ?
regards, Sergei
--
Evgeny Pazhitnov