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

[David G. Johnston]

On Thursday, January 27, 2022, Anand Sowmithiran 
wrote:
>
> However, the MS SQL server MERGE command also does 'delete' using the
> 'when not matched' clause, is there an equivalent ?
>

PostgreSQL does not have a merge command feature.  Just the subset of
behavior that is INSERT…on conflict

David J.

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

[Anand Sowmithiran]

Thanks , that was helpful. I was not framing the right key words during my
searches in SO!
Using the xmax internal column, able to detect if the upsert did an Insert
or Update.
However, the MS SQL server MERGE command also does 'delete' using the 'when
not matched' clause, is there an equivalent ?

thanks,
Anand.

On Thu, Jan 27, 2022 at 11:29 AM Justin Pryzby  wrote:

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

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

[Justin Pryzby]

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
> 
> [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

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

[Thomas Munro]

On Thu, Jan 27, 2022 at 5:54 PM Anand Sowmithiran  wrote:
> Is there any workaround to get this info ?

There's an undocumented trick:

https://stackoverflow.com/questions/34762732/how-to-find-out-if-an-upsert-was-an-update-with-postgresql-9-5-upsert

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

[David G. Johnston]

On Wednesday, January 26, 2022, 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
> 
> [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 ?
>

There is not.  But I’d presume the correct trigger is fired for whichever
DML is ultimately applied so maybe you have a way through that.


> Or is there a way this enhancement can be requested in future PG versions ?
>
>
You just did.  There is nothing formal.  But presently there isn’t anyone
championing improvements to this feature (just my unresearched impression,
searching our public mailing lists and commitfest would let you form a
researched impression).

David J.

Output clause for Upsert aka INSERT...ON CONFLICT

[Anand Sowmithiran]

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

[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 ?
Or is there a way this enhancement can be requested in future PG versions ?

thanks,
Anand.