On 17th of March 2024, Dean Rasheed committed patch:
Add RETURNING support to MERGE. This allows a RETURNING clause to be appended to a MERGE query, to return values based on each row inserted, updated, or deleted. As with plain INSERT, UPDATE, and DELETE commands, the returned values are based on the new contents of the target table for INSERT and UPDATE actions, and on its old contents for DELETE actions. Values from the source relation may also be returned. As with INSERT/UPDATE/DELETE, the output of MERGE ... RETURNING may be used as the source relation for other operations such as WITH queries and COPY commands. Additionally, a special function merge_action() is provided, which returns 'INSERT', 'UPDATE', or 'DELETE', depending on the action executed for each row. The merge_action() function can be used anywhere in the RETURNING list, including in arbitrary expressions and subqueries, but it is an error to use it anywhere outside of a MERGE query's RETURNING list. Dean Rasheed, reviewed by Isaac Morland, Vik Fearing, Alvaro Herrera, Gurjeet Singh, Jian He, Jeff Davis, Merlin Moncure, Peter Eisentraut, and Wolfgang Walther. Discussion: http://postgr.es/m/CAEZATCWePEGQR5LBn-vD6SfeLZafzEm2Qy_L_Oky2=qw2w3Pzg@mail.gmail.com
This is really cool. We got MERGE back in Pg 15, and while it was really cool, it was missing ability to return inserted/updated data using standard RETURNING clause, like in INSERT, UPDATE, or DELETE.
But now, we got it.
So, let's see how that would work.
First, obviously, some test table:
=$ CREATE TABLE test ( id int8 generated always AS IDENTITY, username text NOT NULL UNIQUE, touch_count int4 NOT NULL DEFAULT 0, PRIMARY KEY (id) ); CREATE TABLE
So, let's see the returning with merge that does insert:
=$ MERGE INTO test t USING (VALUES ('depesz')) AS i(un) ON t.username = i.un WHEN matched THEN UPDATE SET touch_count = touch_count + 1 WHEN NOT matched THEN INSERT (username, touch_count) VALUES (i.un, 1) RETURNING *; un | id | username | touch_count --------+----+----------+------------- depesz | 1 | depesz | 1 (1 ROW) MERGE 1
Nice. Interestingly returning * adds column un, I guess with value that caused the match.
Let's see merge that does update:
=$ MERGE INTO test t USING (VALUES ('depesz')) AS i(un) ON t.username = i.un WHEN matched THEN UPDATE SET touch_count = touch_count + 1 WHEN NOT matched THEN INSERT (username, touch_count) VALUES (i.un, 1) RETURNING *; un | id | username | touch_count --------+----+----------+------------- depesz | 1 | depesz | 2 (1 ROW) MERGE 1
Awesome. I could do returning t.* to get just data from the table:
=$ MERGE INTO test t USING (VALUES ('depesz')) AS i(un) ON t.username = i.un WHEN matched THEN UPDATE SET touch_count = touch_count + 1 WHEN NOT matched THEN INSERT (username, touch_count) VALUES (i.un, 1) RETURNING t.*; id | username | touch_count ----+----------+------------- 1 | depesz | 3 (1 ROW) MERGE 1
What's cool is that there is helper function merge_action() that returns information whether given row was inserted or updated:
=$ MERGE INTO test t USING (VALUES ('friend')) AS i(un) ON t.username = i.un WHEN matched THEN UPDATE SET touch_count = touch_count + 1 WHEN NOT matched THEN INSERT (username, touch_count) VALUES (i.un, 1) RETURNING t.*, merge_action(); id | username | touch_count | merge_action ----+----------+-------------+-------------- 2 | friend | 1 | INSERT (1 ROW) MERGE 1 =$ MERGE INTO test t USING (VALUES ('friend')) AS i(un) ON t.username = i.un WHEN matched THEN UPDATE SET touch_count = touch_count + 1 WHEN NOT matched THEN INSERT (username, touch_count) VALUES (i.un, 1) RETURNING t.*, merge_action(); id | username | touch_count | merge_action ----+----------+-------------+-------------- 2 | friend | 2 | UPDATE (1 ROW) MERGE 1
Absolutely awesome. Thanks a lot to everyone involved.