Postgresql 中文操作指南

MERGE

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

Synopsis

[ 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

Description

MERGE 执行修改目标表中行的动作，该目标表由 target_table_name 识别。 MERGE 提供了单一的 SQL 语句，它有条件地 INSERT 、 UPDATE 或 DELETE 行，这一任务通常需要多条过程语言语句。

首先， MERGE 命令从 data_source 执行连接至目标表，产生零个或多个候选更改行。对于每行候选更改行， MATCHED 或 NOT MATCHED 的状态仅设置一次，之后按指定顺序评估 WHEN 子句。对于每行候选更改行，第一个评估为 true 的子句将被执行。对于任何候选更改行，最多只执行一个 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 。

Parameters

with_query
- WITH 子句允许你指定一个或多个子查询，可在 MERGE 查询中按名称引用。有关详细信息，请参阅 Section 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 中的行与目标表中的行相匹配。
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
- 目标表中一列的名称。如果需要，可以用子字段名称或数组下标限定列名称。（只插入复合列中的某些字段会使其他字段保留为 null。）请勿在目标列的规范中包含表的名称。
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 行中的值。

Warning

只应出现在 join_condition 中的目标表中的列，这些列尝试匹配 data_source 行。只引用目标表列的 join_condition 子表达式会影响执行何种操作，通常会以令人惊讶的方式。

Outputs

如果成功完成， MERGE 命令将返回以下形式的命令标记

MERGE total_count

total_count 是已更改的总行数（无论是否插入、更新或删除）。如果 total_count 为 0，则没有任何行以任何方式更改。

Notes

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

总之，每当我们 specify 这种类型的操作时，语句触发器将针对事件类型（比如， INSERT ）触发。相比之下，行级触发器仅会针对 executed 的特定事件类型触发。因此，一个 MERGE 命令可能会针对 UPDATE 和 INSERT 触发语句触发器，即使仅触发了 UPDATE 行触发器。

应确保该联接为每个目标行生成至多一个候选更改行。换句话说，一个目标行不应该联接到多个数据源行。如果是这样，则仅会使用一个候选更改行修改目标行；以后再尝试修改该行将会导致错误。如果行触发器对目标表进行更改，并且这么修改的行随后也遭到 MERGE 修改，那么这种情况也会发生。如果重复的操作是 INSERT ，这将导致唯一性冲突，而重复的 UPDATE 或 DELETE 将导致基数冲突；后者行为是 SQL 标准所必需的。这不同于 UPDATE 和 DELETE 语句中联接的历史 PostgreSQL 行为，其中会简单忽略第二次和后续尝试修改相同行的行为。

如果 WHEN 子句省略了 AND 子句，它将成为该类型（ MATCHED 或 NOT MATCHED ）的最终可达子句。如果指定了后续 WHEN 该类型子句，它将被证明不可达并引发错误。如果未指定任何类型的最终可达子句，则可能不会对候选更改行采取任何措施。

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

MERGE 中没有 RETURNING 子句。 INSERT 、 UPDATE 和 DELETE 的操作不能包含 RETURNING 或 WITH 子句。

当 MERGE 与修改目标表的其他命令同时运行时，将应用常规事务隔离规则；请参阅 Section 13.2 以了解每个隔离级别的行为说明。您还可以考虑将 INSERT … ON CONFLICT 用作备用语句，它提供在发生并发 INSERT 时运行 UPDATE 的能力。这两个语句类型之间存在各种差异和限制，并且它们不可互换。

Examples

基于新 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);

请注意，这将与以下语句完全等效，因为 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 表可能是最近加载到数据库中的临时表。

Compatibility

此命令符合 SQL 标准。

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