I am doing an INSERT
with ON CONFLICT
to postgres using java. Is there any way to find out if the executeUpdate
inserted the row or updated it?
I am doing an INSERT
with ON CONFLICT
to postgres using java. Is there any way to find out if the executeUpdate
inserted the row or updated it?
You can look at the system column xmax
to tell the difference. It's 0
for inserted rows in this case.
CREATE TABLE tbl(id int PRIMARY KEY, col int);
INSERT INTO tbl VALUES (1, 1);
INSERT INTO tbl(id, col)
VALUES (1,11), (2,22)
ON CONFLICT (id) DO UPDATE
SET col = EXCLUDED.col
RETURNING *, (xmax = 0) AS inserted;
This is building on an undocumented implementation detail that might change in future releases (even if unlikely). It works for Postgres 9.5 and 9.6.
The beauty of it: you do not need to introduce additional columns.
Detailed explanation: