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;

fiddle

This is building on an undocumented implementation detail that might change in future releases (even if highly unlikely). It works for Postgres 9.5 all the way up to Postgres 15.

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