描述

MERGE使用data_source 对由target_table_name标识的目标表中的行执行修改动作。 MERGE提供了一条单独的SQL语句， 可按条件对行执行INSERT、UPDATE或DELETE； 否则，这项工作通常需要多条过程语言语句。

首先，MERGE命令会将data_source 与目标表执行连接，生成零个或多个候选变更行。对于每个候选变更行， 系统只会确定一次其状态是MATCHED、 NOT MATCHED BY SOURCE还是 NOT MATCHED [BY TARGET]，然后按指定顺序对 WHEN子句求值。对于每个候选变更行， 只执行第一个求值为真的子句。任何候选变更行都不会执行多个 WHEN子句。

MERGE中的各个动作，与常规的 UPDATE、INSERT或 DELETE同名命令具有相同效果。不过这些动作的语法有所不同， 尤其是没有WHERE子句，也不指定表名。 所有动作都针对目标表，不过也可以借助触发器修改其他表。

当指定DO NOTHING时，源行会被跳过。由于动作会按其指定顺序求值， DO NOTHING有助于在进行更细粒度处理之前跳过不需要关注的源行。

可选的RETURNING子句会让MERGE 针对每个被插入、更新或删除的行计算并返回值。可以计算任何使用源表或目标表列的表达式， 也可以调用merge_action()函数。 默认情况下，执行INSERT或UPDATE动作时， 使用目标表各列的新值；执行DELETE动作时，使用目标表各列的旧值， 但也可以显式请求旧值和新值。RETURNING列表的语法与 SELECT的输出列表完全相同。

没有单独的MERGE权限。 如果指定更新动作，则必须对目标表中SET子句所引用的列具有UPDATE权限。 如果指定插入动作，则必须对目标表具有INSERT权限。 如果指定删除动作，则必须对目标表具有DELETE权限。 如果指定DO NOTHING动作，则必须对目标表中至少一列具有SELECT权限。 对于data_source以及目标表中在任何 condition（包括join_condition）或 expression中引用的列，你还必须具有SELECT权限。 权限只会在语句开始时测试一次，而且无论特定的WHEN子句是否执行，都会进行检查。

如果目标表是物化视图、外部表，或者其上定义了任何规则，则不支持MERGE。

参数

输出

成功完成时，MERGE命令返回如下形式的命令标签：

MERGE total_count

total_count是发生变化的总行数 （无论是插入、更新还是删除）。 如果total_count为 0， 则没有任何行以任何方式发生变化。

如果MERGE命令包含RETURNING子句， 则其结果将类似于一条SELECT语句， 包含RETURNING列表中定义的列和值， 这些值是针对该命令插入、更新或删除的行计算得到的。

注解

在执行MERGE期间会发生以下步骤：

总之，只要我们指定了某种动作， 对应事件类型（例如INSERT）的语句级触发器就会被触发。 相反，行级触发器只会针对实际被执行的特定事件类型触发。 因此，一条MERGE命令可能同时触发 UPDATE和INSERT的语句级触发器， 即使实际上只触发了UPDATE的行级触发器。

你应确保该连接对每个目标行最多只生成一个候选变更行。 换句话说，一个目标行不应连接到多于一个数据源行。 如果发生这种情况，则只会使用其中一个候选变更行来修改目标行； 随后再次尝试修改该行会导致错误。 如果行级触发器对目标表进行了修改，而随后这些已被修改的行又被 MERGE再次修改，也可能发生这种情况。 如果重复的动作是INSERT，则会导致唯一性违例错误； 而重复的UPDATE或DELETE则会导致基数违规； 后者是SQL标准要求的行为。 这不同于PostgreSQL历史上在 UPDATE和DELETE语句中处理连接的行为： 对同一行的第二次及后续修改尝试会被直接忽略。

如果某个WHEN子句省略了AND子句， 它就会成为该类型（MATCHED、 NOT MATCHED BY SOURCE或 NOT MATCHED [BY TARGET]）最后一个可达的子句。 如果之后又指定了同一类型的WHEN子句， 则可证明它不可达，并会引发错误。 如果这三类中都没有指定最后一个可达子句，则候选变更行可能不会执行任何动作。

默认情况下，从数据源生成行的顺序是不确定的。 如果需要，可以使用source_query指定一致的排序， 这可能有助于避免并发事务之间的死锁。

当MERGE与其他修改目标表的命令并发运行时， 适用通常的事务隔离规则；请参见Section 13.2， 了解各个隔离级别下的行为说明。 你也可以考虑使用INSERT ... ON CONFLICT作为替代语句， 它提供了在发生并发INSERT时执行UPDATE的能力。 这两种语句类型之间存在各种差异和限制，因此不能互换。

示例

根据新的recent_transactions对 customer_accounts执行维护。

MERGE INTO customer_account ca
USING recent_transactions t
ON t.customer_id = ca.customer_id
WHEN MATCHED THEN
  UPDATE SET balance = balance + transaction_value
WHEN NOT MATCHED THEN
  INSERT (customer_id, balance)
  VALUES (t.customer_id, t.transaction_value);

尝试插入一个新的库存项及其库存数量。如果该项已存在， 则改为更新现有项的库存数量。不允许库存为零的条目。 返回所有已发生更改的详细信息。

MERGE INTO wines w
USING wine_stock_changes s
ON s.winename = w.winename
WHEN NOT MATCHED AND s.stock_delta > 0 THEN
  INSERT VALUES(s.winename, s.stock_delta)
WHEN MATCHED AND w.stock + s.stock_delta > 0 THEN
  UPDATE SET stock = w.stock + s.stock_delta
WHEN MATCHED THEN
  DELETE
RETURNING merge_action(), w.winename, old.stock AS old_stock, new.stock AS new_stock;

例如，wine_stock_changes表可能是一个刚装载到数据库中的临时表。

根据替换后的酒单更新wines：为任何新库存插入行， 更新已修改的库存条目，并删除新列表中不存在的任何葡萄酒。

MERGE INTO wines w
USING new_wine_list s
ON s.winename = w.winename
WHEN NOT MATCHED BY TARGET THEN
  INSERT VALUES(s.winename, s.stock)
WHEN MATCHED AND w.stock != s.stock THEN
  UPDATE SET stock = s.stock
WHEN NOT MATCHED BY SOURCE THEN
  DELETE;
 

兼容性

这个命令符合SQL标准。

WITH子句、WHEN NOT MATCHED上的 BY SOURCE和BY TARGET限定词、 DO NOTHING动作以及RETURNING子句， 都是对SQL标准的扩展。