PostgreSQL 17 merge语法增强，支持 RETURNING 子句，可以返回新增、更新或者删除的数据行
参考官方文档
https://www.postgresql.org/docs/17/sql-merge.html
1、merge语法
[ WITH with_query [, ...] ]
MERGE INTO [ ONLY ] target_table_name [ * ] [ [ AS ] target_alias ]
USING data_source ON join_condition
when_clause [...]
[ RETURNING { * | output_expression [ [ AS ] output_name ] } [, ...] ]where data_source is:{ [ ONLY ] source_table_name [ * ] | ( source_query ) } [ [ AS ] source_alias ]and when_clause is:{ WHEN MATCHED [ AND condition ] THEN { merge_update | merge_delete | DO NOTHING } |WHEN NOT MATCHED BY SOURCE [ AND condition ] THEN { merge_update | merge_delete | DO NOTHING } |WHEN NOT MATCHED [ BY TARGET ] [ AND condition ] THEN { merge_insert | DO NOTHING } }and merge_insert is:INSERT [( column_name [, ...] )]
[ OVERRIDING { SYSTEM | USER } VALUE ]
{ VALUES ( { expression | DEFAULT } [, ...] ) | DEFAULT VALUES }and merge_update is:UPDATE SET { column_name = { expression | DEFAULT } |( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |( column_name [, ...] ) = ( sub-SELECT )} [, ...]and merge_delete is:DELETE
2、示例
superdb=# create table t_testpg17(id int primary key,nameinfo varchar(32));
CREATE TABLEsuperdb=# \dList of relationsSchema | Name | Type | Owner
--------+------------+-------+-------public | t_testpg17 | table | super
(1 row)superdb=# insert into t_testpg17 values(1,'rc1');
INSERT 0 1
superdb=# insert into t_testpg17 values(2,'pg17');
INSERT 0 1
superdb=# select * from t_testpg17;id | nameinfo
----+----------1 | rc12 | pg17
(2 rows)merge into t_testpg1
using (values(2,'postgresql 17 rc1'),(3,'merge'),(4,'rerurning')) t2 (id,nameinfo) on t1.id=t2.id
when matched then update set nameinfo=t2.nameinfo
when not matched then insert(id,nameinfo) values(t2.id,t2.nameinfo)
returning merge_action(), *;
-- run resultmerge_action | id | nameinfo | id | nameinfo
--------------+----+-------------------+----+-------------------UPDATE | 2 | postgresql 17 rc1 | 2 | postgresql 17 rc1INSERT | 4 | rerurning | 4 | rerurningINSERT | 3 | merge | 3 | merge
