Detect if the row was updated or inserted

Question

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?

Answer 1

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:

• PostgreSQL Upsert differentiate inserted and updated rows using system columns XMIN, XMAX and others