safyia safyia - 6 days ago 5
Java Question

Detect if the row was updated or inserted

I am doing an

INSERT
with
ON CONFLICT
to postgre using java. Is there any way to find out if the
executeUpdate
inserted the row or updated it?

Answer

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: