How to distinguish conflict in sqlalchemy INSERT…ON CONFLICT (Upsert)

Question

I was reading the documentation of SqlAlchemy regarding the upsert operation on postgres dialect at http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#insert-on-conflict-upsert

Is there a way to know if the upsert has been an insert or an update?

The documentation seems to omit this detail.

Answer 1

Just add a final RETURNING clause with:

...
RETURNING (tbl.xmax = 0) AS inserted

Returns true for inserted rows and false for updated rows. This relies on an undocumented implementation detail, though. For a detailed explanation see:

• Detect if the row was updated or inserted
• PostgreSQL Upsert differentiate inserted and updated rows using system columns XMIN, XMAX and others

Adding OIDs (like suggested in another answer) adds cost, bloats the table and burns OIDs (if your table isn't trivially small). That's why the general setting of default_with_oids has been changed to off a long time ago (Postgres 8.1). Quoting the manual:

The use of OIDs in user tables is considered deprecated, so most installations should leave this variable disabled. Applications that require OIDs for a particular table should specify WITH OIDS when creating the table.