9.3 9.4 9.5 9.6 10 11 12 13 14 15 16 17 Current(18)
PostgreSQL中文社区 问题报告 纠错本页面
MERGE
上一页 上一级SQL 命令起始页 下一页

MERGE

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

大纲

[ 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 ] } [, ...] ]

其中 data_source 是：

    { [ ONLY ] source_table_name [ * ] | ( source_query ) } [ [ AS ] source_alias ]

并且 when_clause 是：

    { 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 } }

并且 merge_insert 是：

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

并且 merge_update 是：

    UPDATE SET { column_name = { expression | DEFAULT } |
                 ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
                 ( column_name [, ...] ) = ( sub-SELECT )
               } [, ...]

并且 merge_delete 是：

    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 权限。 您还需要对 data_source 和目标表中在任何 condition （包括 join_condition）或 expression 中引用的任何列拥有 SELECT 权限。 权限在语句开始时进行一次测试，并在执行特定 WHEN 子句时进行检查。

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

参数

with_query

WITH 子句允许您指定一个或多个 子查询，这些子查询可以在 MERGE 查询中通过名称引用。有关详细信息，请参见 第 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 中的哪些行与目标表中的行匹配。

警告

只有来自目标表的列，尝试匹配data_source 行的列应出现在join_condition中。 只引用目标表列的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标准的扩展，附加 BY TARGET到WHEN NOT MATCHED的选项， 使其含义更加明确。

如果WHEN子句指定了WHEN MATCHED，且候选更改行与 data_source中的某行匹配目标表中的某行， 则当condition缺失或其计算结果为 true时，执行该WHEN子句。

如果WHEN子句指定了 WHEN NOT MATCHED BY SOURCE，且候选更改行表示目标表中不匹配 data_source中的行，则当 condition缺失或其值为 true时，执行该WHEN子句。

如果WHEN子句指定了 WHEN NOT MATCHED [BY TARGET]，且候选更改 行表示data_source中的一行， 该行与目标表中的行不匹配， 则当condition缺失或其值为 true时，执行WHEN子句。

condition

返回一个boolean类型值的表达式。 如果WHEN子句的表达式返回true， 那么该子句的操作将对该行执行。

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

merge_insert

INSERT操作的规范是将一行插入目标表中。 目标列名可以以任何顺序列出。如果根本没有列名列表， 则默认为表中所有列按其声明的顺序。

每个未在显式或隐式列列表中出现的列都将填充默认值，要么是其声明的默认值，要么是null（如果没有默认值）。

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

列名不能被指定超过一次。 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 来 返回目标表的旧值或新值。来自目标表的未限定列名， 或使用目标表名或别名限定的列名或 * 将返回 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与修改目标表的其他命令同时运行时，通常的事务隔离规则适用； 请参阅第 13.2 节以了解每个隔离级别的行为。 您可能还希望考虑使用INSERT ... ON CONFLICT作为一种替代语句， 它提供了在并发INSERT发生时运行UPDATE的能力。 这两种语句类型之间存在各种差异和限制，它们不能互换使用。

示例

对customer_accounts进行基于新recent_transactions的维护。 

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子句，BY SOURCE和 BY TARGET限定符用于 WHEN NOT MATCHED，DO NOTHING操作， 以及RETURNING子句是对 SQL标准的扩展。

上一页 上一级 下一页
LOCK 起始页 MOVE