PostgreSQL ON CONFLICT DO UPDATE で差分を知る

PostgreSQL で以下のようなことをやりたい

INSERT ... ON CONFLICT ... DO UPDATE ... RETURNING といったクエリで:

新規挿入されたレコード

更新されたレコード

PostgreSQL には以下のようなシステム列がある

xmax には更新・削除がされていない場合にゼロとなる

(ただし内部的には少しだけ違っていて「元の行を無効化（xmax に更新トランザクションIDが入る）」し、新しい行を挿入する操作になる、これは MVCC の仕組み内の話らしい)

code:create_table.sql

CREATE TABLE t (

id int primary key,

val text

);

code:insert.sql

INSERT INTO t (id, val) VALUES (1, 'old')

code:onconflict.sql

INSERT INTO t (id, val)

VALUES (1, 'new'), (2, 'foo')

ON CONFLICT (id) DO UPDATE

SET val = EXCLUDED.val

RETURNING id, val, xmax = 0 AS inserted;

システム列である xmax = 0 を見て新規挿入か調べることができる

code:result

id | val | inserted

----+-----+----------

1 | foo | f

2 | new | t

(2 行)