9.3 9.4 9.5 9.6 10 11 12 13 14 Current(15)
阿里云PostgreSQL 问题报告 纠错本页面

MERGE

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

大纲

[ WITH with_query [, ...] ]
MERGE INTO [ ONLY ] target_table_name [ * ] [ [ AS ] target_alias ]
USING data_source ON join_condition
when_clause [...]

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 [ 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_sourceMERGE提供了一个单一的SQL语句,可以有条件地INSERTUPDATEDELETE行,这是一项原本需要多个过程语言语句的任务。

首先,MERGE命令执行从data_source到目标表的连接, 生成零个或多个候选更改行。对于每个候选更改行,MATCHEDNOT MATCHED的状态 仅设置一次,之后按指定顺序评估WHEN子句。对于每个候选更改行,第一个评估为真的子句将被执行。 对于任何候选更改行,不会执行多于一个WHEN子句。

MERGE 操作与常规的 UPDATEINSERTDELETE 命令具有相同的效果。 这些命令的语法不同,特别是没有 WHERE 子句,也没有指定表名。 所有操作都是针对目标表的,尽管可以使用触发器对其他表进行修改。

当指定DO NOTHING时,源行将被跳过。由于操作按照它们指定的顺序进行评估, DO NOTHING可以很方便地跳过不感兴趣的源行,然后再进行更精细的处理。

没有单独的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_alias

目标表的替代名称。当提供别名时,它完全隐藏了表的实际名称。例如,给定MERGE INTO foo AS fMERGE语句的其余部分必须将此表称为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_clause

至少需要一个WHEN子句。

如果WHEN子句指定为WHEN MATCHED, 并且候选更改行匹配目标表中的一行, 如果condition不存在或评估为true, 则执行WHEN子句。

相反,如果WHEN子句指定WHEN NOT MATCHED, 并且候选更改行与目标表中的行不匹配, 则如果condition不存在或评估为true, 则执行WHEN子句。

condition

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

WHEN MATCHED子句上的条件可以引用源关系和目标关系中的列。 在WHEN NOT MATCHED子句上的条件只能引用源关系中的列,因为根据定义, 没有匹配的目标行。只有目标表中的系统属性是可访问的。

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子句中使用,表达式可以使用 data_source行中的值。

DEFAULT

将列设置为其默认值(如果没有为其分配特定的默认表达式,则将为NULL)。

sub-SELECT

一个SELECT子查询,产生与其前面括号中列出的列数相同的输出列。 当执行时,子查询必须产生不超过一行。如果产生一行,则将其列值分配给目标列; 如果没有产生行,则将NULL值分配给目标列。子查询可以引用目标表中原始行的值, 以及data_source行的值。

输出

在成功完成后,MERGE命令返回一个形式为

MERGE total_count

total_count是更改的总行数(无论是插入、更新还是删除)。 如果total_count为0,则没有任何行发生更改。

注解

在执行MERGE期间,以下步骤将发生。

  1. 为所有指定的操作执行任何BEFORE STATEMENT触发器,无论其WHEN子句是否匹配。

  2. 从源表到目标表执行连接。生成的查询将按正常方式优化,并将产生一组候选更改行。对于每个候选更改行,

    1. 评估每行是否MATCHEDNOT MATCHED

    2. 按指定顺序测试每个WHEN条件,直到有一个返回true。

    3. 当条件返回true时,执行以下操作:

      1. 为事件类型触发的操作执行任何BEFORE ROW触发器。

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

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

  3. 为指定的操作执行任何AFTER STATEMENT触发器,无论它们是否实际发生。这类似于修改零行的UPDATE语句的行为。

总之,事件类型(比如,INSERT)的语句触发器将在我们指定该类型的操作时触发。 相反,行级触发器仅对正在执行的特定事件类型触发。 因此,MERGE命令可能会触发UPDATEINSERT的语句触发器,即使只有UPDATE行触发器被触发。

您应确保连接最多为每个目标行生成一个候选更改行。换句话说,目标行不应连接到多个数据源行。 如果连接到多个数据源行,则只会使用一个候选更改行来修改目标行;稍后尝试修改该行将导致错误。 如果行触发器对目标表进行更改,然后对所修改的行进行MERGE的修改,也会发生这种情况。 如果重复的操作是INSERT,这将导致唯一性违规,而重复的UPDATEDELETE将导致基数违规;后者是SQL标准所要求的行为。 这与历史上PostgreSQLUPDATEDELETE语句中对连接的行为不同,后续尝试修改相同行将被简单地忽略。

如果WHEN子句省略了一个AND子子句,它将成为该种类 (MATCHEDNOT MATCHED)的最终可达子句。 如果稍后指定了该种类的WHEN子句,它将被证明是不可达的,并引发错误。 如果没有指定任何一种最终可达子句,可能不会对候选更改行执行任何操作。

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

MERGE中没有RETURNING子句。 INSERTUPDATEDELETE的操作不能包含RETURNINGWITH子句。

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);

注意,这与以下语句完全等效,因为MATCHED结果在执行过程中不会改变。

MERGE INTO customer_account ca
USING (SELECT customer_id, transaction_value FROM recent_transactions) AS 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;

wine_stock_changes表可能是最近加载到数据库中的临时表。

兼容性

这个命令符合SQL标准。

WITH子句和DO NOTHING操作是SQL标准的扩展。