PostgreSQL将在15版本支持MERGE INTO语法

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/