Re: Output clause for Upsert aka INSERT...ON CONFLICT

On Thu, Jan 27, 2022 at 10:24:14AM +0530, Anand Sowmithiran wrote:
> The INSERT...ON CONFLICT is used for doing upserts in one of our app.
> Our app works with both MS SQL and Postgresql, based on customer needs.
>
> Unlike the MS SQL MERGE command's OUTPUT clause that gives the $action
> <https://docs.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-ver15#output_clause>
> [INSERT / UPDATE  /DELETE] that was done during the upsert, the RETURNING
> clause of the pgsql does not return the action done.
> We need this so that the application can use that for auditing and UI
> purposes.
> Is there any workaround to get this info ?

Thomas already answered about the xmax hack, but I dug these up in the
meantime.

https://www.postgresql.org/message-id/flat/CAA-aLv4d%3DzHnx%2BzFKqoszT8xRFpdeRNph1Z2uhEYA33bzmgtaA%40mail.gmail.com#899e15b8b357c6b29c51d94a0767a601
https://www.postgresql.org/message-id/flat/1565486215.7551.0%40finefun.com.au
https://www.postgresql.org/message-id/flat/20190724232439.lpxzjw2jg3ukgcqn%40alap3.anarazel.de
https://www.postgresql.org/message-id/flat/DE57F14C-DB96-4F17-9254-AD0AABB3F81F%40mackerron.co.uk
https://www.postgresql.org/message-id/CAM3SWZRmkVqmRCs34YtZPOCn%2BHmHqtcdEmo6%3D%3Dnqz1kNA43DVw%40mail.gmail.com

https://stackoverflow.com/questions/39058213/postgresql-upsert-differentiate-inserted-and-updated-rows-using-system-columns-x/39204667
https://stackoverflow.com/questions/40878027/detect-if-the-row-was-updated-or-inserted/40880200#40880200