Very cool 9.5 feature
| От | Michael Moore |
|---|---|
| Тема | Very cool 9.5 feature |
| Дата | |
| Msg-id | CACpWLjN_DouuG11hL8gNtLYZu+uLNOQxPpKWEzk5w2rOrHB_qA@mail.gmail.com обсуждение исходный текст |
| Список | pgsql-sql |
I was impressed by the ability of PostgresSQL to do this so I though I'd share it with the group.
--CREATE TABLE db2 (a INT PRIMARY KEY, b TEXT,c text);
INSERT into db2 as current
SELECT * FROM json_populate_record(null::db2,
(SELECT '{"a":3,"b":"test3.2","c":"ctest3.2"}'::json))
on conflict (a) do update set b = coalesce(EXCLUDED.b,current.b),
c = coalesce(EXCLUDED.c,current.c)
;
INSERT into db2 as current
SELECT * FROM json_populate_record(null::db2,
(SELECT '{"a":3,"b":"test99"}'::json))
on conflict (a) do update set b = coalesce(EXCLUDED.b,current.b),
c = coalesce(EXCLUDED.c,current.c)
;
Note that the second insert will not UPDATE the value of column C to NULL due to the "coalesce".
Very cool; maybe the next release will let us do:
"on conflict (a) DO UPDATE set ROW from NEW-VALUES".
Mike
В списке pgsql-sql по дате отправления: