MERGE语法可以参考如下：
https://www.postgresql.org/docs/devel/sql-merge.html
[ WITH with_query [, ...] ]
MERGE INTO target_table_name [ [ AS ] target_alias ]
USING data_source ON join_condition
when_clause [...]
where data_source is
{ 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 [ 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 [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...]
and merge_delete is
DELETE
实例如下：
创建表并插入数据
postgres=# CREATE TABLE t_test (
id serial PRIMARY KEY,
val int
);
CREATE TABLE
postgres=# INSERT INTO t_test (val)
SELECT x * 10 FROM generate_series(1, 10) AS x;
INSERT 0 10
postgres=# SELECT * FROM t_test;
id | val
---+-----
1 | 10
2 | 20
3 | 30
4 | 40
5 | 50
6 | 60
7 | 70
8 | 80
9 | 90
10 | 100
(10 rows)
使用merge into语法
postgres=# MERGE INTO t_test
USING (SELECT x, random() * 1000 AS z
FROM generate_series(1, 16, 2) AS x
) AS y
ON t_test.id = y.x
WHEN MATCHED THEN
UPDATE SET val = z
WHEN NOT MATCHED THEN
INSERT (val) VALUES (z);
MERGE 8
解释一下：t_test是操作的目标表，我们将USING子句的结果“y”合并到表中。WHEN子句定义MERGE操作期间使用的规则。当匹配（id为奇数）时，我们更新现有的值。当没有匹配的值时，我们使用INSERT插入新值。而且update的时候，不需要指定目标表，insert的时候也是如此，不需要写表名。
执行后的结果如下：
postgres=# SELECT * FROM t_test ORDER BY id;
id | val
---+-----
1 | 101
2 | 20
3 | 656
4 | 40
5 | 309
6 | 60
7 | 897
8 | 80
9 | 195
10 | 100
11 | 634
12 | 625
13 | 50
(13 rows)
使用delete的效果如下：
postgres=# MERGE INTO t_test
USING (SELECT x, random() * 1000 AS z
FROM generate_series(1, 16, 2) AS x) AS y
ON t_test.id = y.x
WHEN MATCHED THEN
DELETE
WHEN NOT MATCHED THEN
INSERT (val) VALUES (z);
MERGE 8
postgres=# SELECT * FROM t_test ORDER BY id;
id | val
---+-----
2 | 20
4 | 40
6 | 60
8 | 80
10 | 100
12 | 625
14 | 648
(7 rows)
也就是把匹配的行（id为奇数的行）删除掉，不匹配的则插入。这里delete不需要写其他的条件，因为前面通过USING已经进行了判断，知道需要删除的行。
参考：
https://www.cybertec-postgresql.com/en/postgresql-15-using-merge-in-sql/
