MERGE — 有条件地插入、更新或删除表中的行
[ WITHwith_query
[, ...] ] MERGE INTO [ ONLY ]target_table_name
[ * ] [ [ AS ]target_alias
] USINGdata_source
ONjoin_condition
when_clause
[...] wheredata_source
is: { [ ONLY ]source_table_name
[ * ] | (source_query
) } [ [ AS ]source_alias
] andwhen_clause
is: { WHEN MATCHED [ ANDcondition
] THEN {merge_update
|merge_delete
| DO NOTHING } | WHEN NOT MATCHED [ ANDcondition
] THEN {merge_insert
| DO NOTHING } } andmerge_insert
is: INSERT [(column_name
[, ...] )] [ OVERRIDING { SYSTEM | USER } VALUE ] { VALUES ( {expression
| DEFAULT } [, ...] ) | DEFAULT VALUES } andmerge_update
is: UPDATE SET {column_name
= {expression
| DEFAULT } | (column_name
[, ...] ) = [ ROW ] ( {expression
| DEFAULT } [, ...] ) | (column_name
[, ...] ) = (sub-SELECT
) } [, ...] andmerge_delete
is: DELETE
MERGE
执行修改目标表中被标识为target_table_name
的行的操作,
使用data_source
。
MERGE
提供了一个单一的SQL语句,可以有条件地INSERT
、
UPDATE
或DELETE
行,这是一项原本需要多个过程语言语句的任务。
首先,MERGE
命令执行从data_source
到目标表的连接,
生成零个或多个候选更改行。对于每个候选更改行,MATCHED
或NOT MATCHED
的状态
仅设置一次,之后按指定顺序评估WHEN
子句。对于每个候选更改行,第一个评估为真的子句将被执行。
对于任何候选更改行,不会执行多于一个WHEN
子句。
MERGE
操作与常规的 UPDATE
、INSERT
或 DELETE
命令具有相同的效果。
这些命令的语法不同,特别是没有 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 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_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
期间,以下步骤将发生。
为所有指定的操作执行任何BEFORE STATEMENT
触发器,无论其WHEN
子句是否匹配。
从源表到目标表执行连接。生成的查询将按正常方式优化,并将产生一组候选更改行。对于每个候选更改行,
评估每行是否MATCHED
或NOT MATCHED
。
按指定顺序测试每个WHEN
条件,直到有一个返回true。
当条件返回true时,执行以下操作:
为事件类型触发的操作执行任何BEFORE ROW
触发器。
执行指定的操作,调用目标表上的任何检查约束。
为事件类型触发的操作执行任何AFTER ROW
触发器。
为指定的操作执行任何AFTER STATEMENT
触发器,无论它们是否实际发生。这类似于修改零行的UPDATE
语句的行为。
总之,事件类型(比如,INSERT
)的语句触发器将在我们指定该类型的操作时触发。
相反,行级触发器仅对正在执行的特定事件类型触发。
因此,MERGE
命令可能会触发UPDATE
和INSERT
的语句触发器,即使只有UPDATE
行触发器被触发。
您应确保连接最多为每个目标行生成一个候选更改行。换句话说,目标行不应连接到多个数据源行。
如果连接到多个数据源行,则只会使用一个候选更改行来修改目标行;稍后尝试修改该行将导致错误。
如果行触发器对目标表进行更改,然后对所修改的行进行MERGE
的修改,也会发生这种情况。
如果重复的操作是INSERT
,这将导致唯一性违规,而重复的UPDATE
或DELETE
将导致基数违规;后者是SQL标准所要求的行为。
这与历史上PostgreSQL在UPDATE
和DELETE
语句中对连接的行为不同,后续尝试修改相同行将被简单地忽略。
如果WHEN
子句省略了一个AND
子子句,它将成为该种类
(MATCHED
或NOT MATCHED
)的最终可达子句。
如果稍后指定了该种类的WHEN
子句,它将被证明是不可达的,并引发错误。
如果没有指定任何一种最终可达子句,可能不会对候选更改行执行任何操作。
从数据源生成行的顺序默认情况下是不确定的。
如果需要,可以使用source_query
来指定一致的排序,
这可能需要避免并发事务之间的死锁。
在MERGE
中没有RETURNING
子句。
INSERT
,UPDATE
和DELETE
的操作不能包含RETURNING
或WITH
子句。
当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标准的扩展。