PostgreSQL 中文参考文档
🌐 英文原版 官方 ↗
MERGE
上一页 上一级SQL 命令首页 下一页

MERGE

MERGE — 有条件地插入、更新或删除一个表的行

Synopsis

[ WITH with_query [, ...] ]
MERGE INTO [ ONLY ] target_table_name [ * ] [ [ AS ] target_alias ]
    USING data_source ON join_condition
    when_clause [...]
    [ RETURNING [ WITH ( { OLD | NEW } AS output_alias [, ...] ) ]
                { * | 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

描述

MERGE 执行一些操作来修改标识为 target_table_name 的目标表中的行，并使用 data_source。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 特权。此外，你还需要 SELECT 特权，范围涵盖 data_source 的任意列，以及目标表中被任何 condition（包括 join_condition）或 expression 引用的任意列。特权在语句开始时测试一次，并且无论特定的 WHEN 子句是否被执行都会被检查。

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

参数

with_query

WITH 子句允许指定一个或多个子查询，它们可以在 MERGE 查询中通过名称被引用。详见 Section 7.8 和 SELECT。注意 WITH RECURSIVE 不被 MERGE 支持。

target_table_name

要合并入的目标表或视图的名称（可以选择用模式限定）。如果在表名之前指定了 ONLY，则只会更新或删除指定表中的匹配行。如果没有指定 ONLY，则也会更新或删除任何从该表继承的表中的匹配行。也可以选择在表名之后指定 *，以显式地表示包括后代表。ONLY 关键字和 * 选项不影响插入动作，插入动作总是只插入到指定的表中。

如果 target_table_name 是一个视图，它要么必须是自动可更新的且没有 INSTEAD OF 触发器，要么必须为 WHEN 子句中指定的每一种动作类型（INSERT、UPDATE 和 DELETE）都有 INSTEAD OF 触发器。不支持带有规则的视图。

target_alias

目标表的替代名称。提供了别名之后，它会完全隐藏表的实际名称。例如，给定 MERGE INTO foo AS f，MERGE 语句的其余部分必须用 f 而不是 foo 来引用这个表。

source_table_name

源表、源视图或转换表的名称（可以选择用模式限定）。如果在表名之前指定了 ONLY，则只会从指定表中包括匹配行。如果没有指定 ONLY，则也会从任何从该表继承的表中包括匹配行。也可以选择在表名之后指定 *，以显式地表示包括后代表。

source_query

提供要合并入目标表的行的查询（SELECT 语句或 VALUES 语句）。语法说明请参考 SELECT 语句或 VALUES 语句。

source_alias

数据源的替代名称。提供了别名之后，它会完全隐藏表的实际名称，或者隐藏发出了一个查询这一事实。

join_condition

join_condition 是一个结果为 boolean 类型值的表达式（类似于 WHERE 子句），它指定 data_source 中的哪些行与目标表中的行匹配。

Warning

join_condition 中应该只出现试图匹配 data_source 行的目标表列。只引用目标表列的 join_condition 子表达式会影响采取哪种动作，其方式常常出人意料。

如果同时指定了 WHEN NOT MATCHED BY SOURCE 和 WHEN NOT MATCHED [BY TARGET] 子句，MERGE 命令将执行一次 FULL 连接，连接发生在 data_source 与目标表之间。要使其生效，至少一个 join_condition 子表达式必须使用能支持哈希连接的操作符，或者所有子表达式都必须使用能支持归并连接的操作符。

when_clause

至少需要一个 WHEN 子句。

WHEN 子句可以指定 WHEN MATCHED、WHEN NOT MATCHED BY SOURCE 或 WHEN NOT MATCHED [BY TARGET]。注意 SQL 标准只定义了 WHEN MATCHED 和 WHEN NOT MATCHED（其定义的含义是没有匹配的目标行）。WHEN NOT MATCHED BY SOURCE 是对 SQL 标准的扩展，在 WHEN NOT MATCHED 后附加 BY TARGET 以使其含义更加明确的选项也是扩展。

如果 WHEN 子句指定了 WHEN MATCHED，并且候选更改行将 data_source 中的一行与目标表中的一行相匹配，那么该 WHEN 子句在 condition 不存在或者求值为 true 时就会被执行。

如果 WHEN 子句指定了 WHEN NOT MATCHED BY SOURCE，并且候选更改行表示目标表中不与 data_source 中任何行相匹配的一行，那么该 WHEN 子句在 condition 不存在或者求值为 true 时就会被执行。

如果 WHEN 子句指定了 WHEN NOT MATCHED [BY TARGET]，并且候选更改行表示 data_source 中不与目标表中任何行相匹配的一行，那么该 WHEN 子句在 condition 不存在或者求值为 true 时就会被执行。

condition

一个返回 boolean 类型值的表达式。如果某个 WHEN 子句的该表达式返回 true，则该子句的动作就会针对该行执行。

WHEN MATCHED 子句上的条件可以引用源关系和目标关系两者的列。WHEN NOT MATCHED BY SOURCE 子句上的条件只能引用目标关系的列，因为根据定义不存在匹配的源行。WHEN NOT MATCHED [BY TARGET] 子句上的条件只能引用源关系的列，因为根据定义不存在匹配的目标行。只有目标表的系统属性是可访问的。

merge_insert

指定一个向目标表插入一行的 INSERT 动作。目标列名可以按任意顺序列出。如果完全没有给出列名列表，默认为该表按声明顺序排列的所有列。

显式或隐式列列表中未出现的每一列都将用默认值填充，即其声明的默认值，如果没有则为空值。

如果目标表是一个分区表，则每一行都会被路由到适当的分区并插入其中。如果目标表是一个分区，则任何输入行违反分区约束时都会发生错误。

列名不能被指定超过一次。INSERT 动作不能包含子选择。

只能指定一个 VALUES 子句。VALUES 子句只能引用源关系中的列，因为根据定义不存在匹配的目标行。

merge_update

指定一个更新目标表当前行的 UPDATE 动作。列名不能被指定超过一次。

既不允许表名，也不允许 WHERE 子句。

merge_delete

指定一个删除目标表当前行的 DELETE 动作。不要像通常使用 DELETE 命令那样包含表名或任何其他子句。

column_name

目标表中一个列的名称。如有需要，列名可以用子字段名或数组下标来限定。（只向一个组合列的部分字段中插入值会使其他字段为空值。）在指定目标列时不要包含表的名称。

OVERRIDING SYSTEM VALUE

没有这个子句时，为定义为 GENERATED ALWAYS 的标识列指定显式值（DEFAULT 除外）是一种错误。这个子句会覆盖该限制。

OVERRIDING USER VALUE

如果指定了这个子句，那么为定义为 GENERATED BY DEFAULT 的标识列提供的任何值都会被忽略，并应用由序列生成的默认值。

DEFAULT VALUES

所有列都将用它们的默认值填充。（这种形式不允许 OVERRIDING 子句。）

expression

赋给该列的一个表达式。如果用在 WHEN MATCHED 子句中，该表达式可以使用目标表中原始行的值以及 data_source 行的值。如果用在 WHEN NOT MATCHED BY SOURCE 子句中，该表达式只能使用目标表中原始行的值。如果用在 WHEN NOT MATCHED [BY TARGET] 子句中，该表达式只能使用 data_source 行的值。

DEFAULT

将该列设置为它的默认值（如果没有为它指定特定的默认值表达式，则默认值为 NULL）。

sub-SELECT

一个 SELECT 子查询，它产生的输出列与它前面圆括号列列表中列出的列一样多。该子查询在执行时最多只能产出一行。如果产出一行，其列值将被赋给目标列；如果没有产出任何行，则将空值（NULL）赋给目标列。如果用在 WHEN MATCHED 子句中，该子查询可以引用目标表中原始行的值以及 data_source 行的值。如果用在 WHEN NOT MATCHED BY SOURCE 子句中，该子查询只能引用目标表中原始行的值。

output_alias

RETURNING 列表中 OLD 或 NEW 行的可选替代名称。

默认情况下，可以写 OLD.column_name 或 OLD.* 来返回目标表的旧值，写 NEW.column_name 或 NEW.* 来返回新值。提供了别名之后，这些名称将被隐藏，必须使用别名来引用旧行或新行。例如 RETURNING WITH (OLD AS o, NEW AS n) o.*, n.*。

output_expression

每一行被更改（无论是插入、更新还是删除）之后由 MERGE 命令计算并返回的一个表达式。该表达式可以使用源表或目标表的任意列，或者 merge_action() 函数，以返回关于所执行动作的更多信息。

写 * 将返回源表的所有列，后面跟着目标表的所有列。这常常会导致大量重复，因为源表和目标表拥有很多相同列的情况很常见。可以用源表或目标表的名称或别名来限定 * 以避免这种情况。

列名或 * 也可以用 OLD 或 NEW 来限定，或者用相应的 output_alias 代替 OLD 或 NEW 来限定，从而使目标表的旧值或新值被返回。不带限定的目标表列名，或者用目标表名或别名限定的列名或 *，对于 INSERT 和 UPDATE 动作将返回新值，对于 DELETE 动作将返回旧值。

output_name

用于返回列的名称。

输出

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

MERGE total_count

total_count 是被更改行（无论是插入、更新还是删除）的总数。如果 total_count 为 0，则没有任何行被更改。

如果 MERGE 命令包含 RETURNING 子句，其结果将类似于一条 SELECT 语句的结果，该语句包含 RETURNING 列表中定义的列和值，并在被该命令插入、更新或删除的行上计算。

注记

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

  1. 对所有指定的动作执行任意 BEFORE STATEMENT 触发器，无论它们的 WHEN 子句是否匹配。

  2. 执行从源表到目标表的连接。产生的查询会被正常优化，并生成一组候选更改行。对于每一个候选更改行：

    1. 评估每一行是 MATCHED、NOT MATCHED BY SOURCE 还是 NOT MATCHED [BY TARGET]。

    2. 按指定的顺序测试每一个 WHEN 条件，直到有一个返回真为止。

    3. 当一个条件返回真时，执行以下动作：

      1. 执行为该动作的事件类型触发的任意 BEFORE ROW 触发器。

      2. 执行指定的动作，并调用目标表上的任何检查约束。

      3. 执行为该动作的事件类型触发的任何 AFTER ROW 触发器。

      如果目标关系是一个针对该动作的事件类型带有 INSTEAD OF ROW 触发器的视图，则将使用这些触发器来执行该动作。

  3. 为指定的动作执行任何 AFTER STATEMENT 触发器，无论它们是否真的发生。这类似于不修改任何行的 UPDATE 语句的行为。

总之，只要我们指定了某一种类的动作，该事件类型（比如说 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 标准的扩展。

上一页 上一级 下一页
LOCK 首页 MOVE