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
