[译文] PostgreSQL 15 新特性 – MERGE 语句

PostgreSQL 15 beta 版新增了令人期待已久的 MERGE 语句 功能。MERGE 语句主要用于根据来自源的信息和条件，在单个事务中并使用单个语句从目标表中添加、更新或删除行。最显着的用途是当基于某些连接条件发现差异时，通过更新、删除或插入新行来同步两个表。

MERGE 语句 解决了什么问题？

MERGE 语句用于基于一个或多个源表在指定表中插入、更新或删除行。它使用条件来确定是否更新、删除或插入目标表。

让我们考虑一个维护客户及其订单信息的小规模设置。在此设置中，也许有人想对特定客户下了多少订单或自上次下订单等多长时间进行分析。

必须执行单独的 INSERT 和 UPDATE 操作，以将此信息从每日订单表同步到合并的客户信息表。

句法

[WITH with_query [, ...]]
MERGE INTO [schema.]table [ [ AS ] alias ]
   USING   {
               [schema.]table |
               [sub-query]
           } [ [ AS ] alias ]
   ON join_condition
   when_clause [...];

when_clause:

{
   WHEN MATCHED [ AND condition ] THEN { merge_update | DELETE | DO NOTHING } |
   WHEN NOT MATCHED [ AND condition ] THEN { merge_insert | DO NOTHING }
}

merge_update:
   UPDATE SET { column_name = { expression | DEFAULT } |
            ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...]

merge_insert:
   INSERT [( column_name [, ...] )]
           [ OVERRIDING { SYSTEM | USER } VALUE ]
           { VALUES ( { expression | DEFAULT } [, ...] ) | DEFAULT VALUES }

示例

CREATE TABLE daily_orders
(
   order_id        INT,
   customer_id     INT,
   order_center    VARCHAR,
   order_time timestamp
);

CREATE TABLE customer_history
(
   customer_id     INT,
   last_order_id   INT,
   order_center    VARCHAR,
   order_count     INT,
   last_order      timestamp
);

INSERT INTO daily_orders (order_id, customer_id, order_center, order_time)
   VALUES (101, 30030, 'WX', now());
INSERT INTO daily_orders (order_id, customer_id, order_center, order_time)
   VALUES (102, 20045, 'CX', now());
INSERT INTO daily_orders (order_id, customer_id, order_center, order_time)
   VALUES (103, 20090, 'JX', now());
INSERT INTO daily_orders (order_id, customer_id, order_center, order_time)
   VALUES (104, 20010, 'AX', now());

INSERT INTO customer_history (customer_id, last_order_id, order_center, order_count, last_order)
   VALUES (40100, 097, 'MK', 10, '2019-09-15 08:13:00');
INSERT INTO customer_history (customer_id, last_order_id, order_center, order_count, last_order)
   VALUES (20453, 098, 'NU', 17, '2020-07-12 01:05:00');
INSERT INTO customer_history (customer_id, last_order_id, order_center, order_count, last_order)
   VALUES (20090, 099, 'JX', 03, '2020-09-11 03:23:00');
INSERT INTO customer_history (customer_id, last_order_id, order_center, order_count, last_order)
   VALUES (21495, 100, 'DS', 11, '2022-05-21 04:12:00');
INSERT INTO customer_history (customer_id, last_order_id, order_center, order_count, last_order)
   VALUES (20010, 103, 'AX', 05, '2021-01-17 19:53:00');

select * from customer_history;
postgres=# select * from customer_history;
 customer_id | last_order_id | order_center | order_count |     last_order      
-------------+---------------+--------------+-------------+--------------------
       40100 |            97 | MK           |          10 | 2019-09-15 08:13:00
       20453 |            98 | NU           |          17 | 2020-07-12 01:05:00
       20090 |            99 | JX           |           3 | 2020-09-11 03:23:00
       21495 |           100 | DS           |          11 | 2022-05-21 04:12:00
       20010 |           103 | AX           |           5 | 2021-01-17 19:53:00
(5 rows)


MERGE INTO customer_history c
   USING daily_orders d
   ON (c.customer_id = d.customer_id)

   WHEN MATCHED THEN
       UPDATE SET     -- Existing customer, update the order count and the timestamp of order.
           order_count = c.order_count + 1,
           last_order_id = d.order_id

   WHEN NOT MATCHED THEN       -- New entry, record it.
       INSERT (customer_id, last_order_id, order_center, order_count, last_order)
           VALUES (customer_id, d.order_id, d.order_center, 1, d.order_time);

select * from customer_history;

postgres=# select * from customer_history;
 customer_id | last_order_id | order_center | order_count |         last_order         
-------------+---------------+--------------+-------------+--------------------
       40100 |            97 | MK           |          10 | 2019-09-15 08:13:00
       20453 |            98 | NU           |          17 | 2020-07-12 01:05:00
       21495 |           100 | DS           |          11 | 2022-05-21 04:12:00
       30030 |           101 | WX           |           1 | 2022-04-30 18:20:33
       20045 |           102 | CX           |           1 | 2022-04-30 18:20:33
       20090 |           103 | JX           |           4 | 2020-09-11 03:23:00
       20010 |           104 | AX           |           6 | 2021-01-17 19:53:00
(7 rows)

可以看到，目标表 customer_history 中添加了两行 customer_id 为 30030 和 20045。而另外两行 customer_id 为 20090 和 20010 已使用新信息进行了更新。

MERGE 语句功能已提交，并且是开发分支的一部分。其文档可在 https://www.postgresql.org/docs/devel/sql-merge.html获得

作者：阿西夫·雷曼
Asif Rehman 是 HighGo Software 的高级软件工程师。他于 2005 年加入 EnterpriseDB，一家 Enterprise PostgreSQL 公司，开始了他的开源开发职业生涯，尤其是 PostgreSQL。Asif 的贡献范围从开发与 Oracle 兼容性相关的内部功能到围绕 PostgreSQL 开发工具。他于 2018 年 9 月加入 HighGo Software。