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

I was reading the documentation of SqlAlchemy regarding the upsert operation on postgres dialect at

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

The documentation seems to omit this detail.


  • 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:

    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). The manual for Postgres 11:

    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.

    Later versions even removed the setting default_with_oids completely.