MERGE — 有条件地插入、更新或删除表中的行
[ WITHwith_query
[, ...] ] MERGE INTO [ ONLY ]target_table_name
[ * ] [ [ AS ]target_alias
] USINGdata_source
ONjoin_condition
when_clause
[...] [ RETURNING { * |output_expression
[ [ AS ]output_name
] } [, ...] ] 其中data_source
是: { [ ONLY ]source_table_name
[ * ] | (source_query
) } [ [ AS ]source_alias
] 并且when_clause
是: { WHEN MATCHED [ ANDcondition
] THEN {merge_update
|merge_delete
| DO NOTHING } | WHEN NOT MATCHED BY SOURCE [ ANDcondition
] THEN {merge_update
|merge_delete
| DO NOTHING } | WHEN NOT MATCHED [ BY TARGET ] [ ANDcondition
] 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
查询中通过名称引用。
有关详细信息,请参见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
中的哪些行与目标表中的行匹配。
只有来自目标表的列,尝试匹配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_expression
一个表达式,用于在每行被更改(无论是插入、更新还是删除)后由
MERGE
命令计算并返回。该表达式可以使用源表或目标表的任意列,
或者使用merge_action()
函数来返回
有关执行操作的附加信息。
写*
将返回源表中的所有列,随后是目标表中的所有列。通常这会导致大量重复,
因为源表和目标表通常有许多相同的列。可以通过使用源表或目标表的名称或别名来限定
*
,从而避免这种情况。
output_name
被返回列的名称。
在成功完成后,MERGE
命令返回一个形式为
MERGE total_count
total_count
是更改的总行数(无论是插入、更新还是删除)。
如果total_count
为0,则没有任何行发生更改。
如果MERGE
命令包含一个RETURNING
子句,结果将类似于包含SELECT
语句中定义的列和值的
RETURNING
列表,这些值是基于命令插入、更新或删除的行计算得出。
在执行MERGE
期间,将发生以下步骤。
对所有指定的操作执行任何BEFORE STATEMENT
触发器,
无论其WHEN
子句是否匹配。
从源表到目标表执行连接。生成的查询将正常优化,并产生一组候选更改行。 对于每个候选更改行,
评估每行是否为MATCHED
、
NOT MATCHED BY SOURCE
,或
NOT MATCHED [BY TARGET]
。
按指定顺序测试每个WHEN
条件,直到其中一个返回真。
当条件返回真时,执行以下操作:
执行针对该操作事件类型触发的任何BEFORE ROW
触发器。
执行指定的操作,调用目标表上的任何检查约束。
执行针对该操作事件类型触发的任何AFTER ROW
触发器。
如果目标关系是带有针对该操作事件类型的INSTEAD OF ROW
触发器的视图,
则使用它们来执行该操作。
对指定的操作执行任何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
的能力。
这两种语句类型之间存在各种差异和限制,它们不能互换使用。
对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 RETURNING merge_action(), w.*;
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标准的扩展。