Postgresql 中文操作指南

MERGE

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

MERGE — conditionally insert, update, or delete rows of a table

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 performs actions that modify rows in the target table identified as target_table_name, using the data_source. MERGE provides a single SQL statement that can conditionally INSERT, UPDATE or DELETE rows, a task that would otherwise require multiple procedural language statements.

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

First, the MERGE command performs a join from data_source to the target table producing zero or more candidate change rows. For each candidate change row, the status of MATCHED or NOT MATCHED is set just once, after which WHEN clauses are evaluated in the order specified. For each candidate change row, the first clause to evaluate as true is executed. No more than one WHEN clause is executed for any candidate change row.

MERGE 动作具有与同名常规 UPDATE 、 INSERT 或 DELETE 命令相同的效果。这些命令的语法不同，值得注意的是，没有 WHERE 子句且没有指定表名。所有动作都涉及目标表，尽管对其他表的修改可以使用触发器进行。

MERGE actions have the same effect as regular UPDATE, INSERT, or DELETE commands of the same names. The syntax of those commands is different, notably that there is no WHERE clause and no table name is specified. All actions refer to the target table, though modifications to other tables may be made using triggers.

当指定 DO NOTHING 时，将跳过源行。由于按指定顺序评估动作， DO NOTHING 在更细粒度的处理之前，很适合用于跳过无意义的源行。

When DO NOTHING is specified, the source row is skipped. Since actions are evaluated in their specified order, DO NOTHING can be handy to skip non-interesting source rows before more fine-grained handling.

没有单独的 MERGE 权限。如果你指定了更新动作，则你必须对 SET 子句中引用的目标表列具有 UPDATE 权限。如果你指定了插入动作，则你必须对目标表具有 INSERT 权限。如果你指定了删除动作，则你必须对目标表具有 DELETE 权限。如果你指定了 DO NOTHING 动作，则你必须至少对目标表的一列具有 SELECT 权限。你还需要对任何 data_source 和目标表中 condition (包括 join_condition ) 或 expression 中引用的任何列具有 SELECT 权限。在语句开始时对权限进行一次测试，并且无论是否执行特定的 WHEN 子句，都会检查权限。

There is no separate MERGE privilege. If you specify an update action, you must have the UPDATE privilege on the column(s) of the target table that are referred to in the SET clause. If you specify an insert action, you must have the INSERT privilege on the target table. If you specify a delete action, you must have the DELETE privilege on the target table. If you specify a DO NOTHING action, you must have the SELECT privilege on at least one column of the target table. You will also need SELECT privilege on any column(s) of the data_source and of the target table referred to in any condition (including join_condition) or expression. Privileges are tested once at statement start and are checked whether or not particular WHEN clauses are executed.

如果目标表是物化视图、外部表或对其定义了任何规则，则不支持 MERGE 。

MERGE is not supported if the target table is a materialized view, foreign table, or if it has any rules defined on it.

Parameters

  • with_query

    • The WITH clause allows you to specify one or more subqueries that can be referenced by name in the MERGE query. See Section 7.8 and SELECT for details. Note that WITH RECURSIVE is not supported by MERGE.

  • target_table_name

    • The name (optionally schema-qualified) of the target table to merge into. If ONLY is specified before the table name, matching rows are updated or deleted in the named table only. If ONLY is not specified, matching rows are also updated or deleted in any tables inheriting from the named table. Optionally, * can be specified after the table name to explicitly indicate that descendant tables are included. The ONLY keyword and * option do not affect insert actions, which always insert into the named table only.

  • target_alias

    • A substitute name for the target table. When an alias is provided, it completely hides the actual name of the table. For example, given MERGE INTO foo AS f, the remainder of the MERGE statement must refer to this table as f not foo.

  • source_table_name

    • The name (optionally schema-qualified) of the source table, view, or transition table. If ONLY is specified before the table name, matching rows are included from the named table only. If ONLY is not specified, matching rows are also included from any tables inheriting from the named table. Optionally, * can be specified after the table name to explicitly indicate that descendant tables are included.

  • source_query

    • A query (SELECT statement or VALUES statement) that supplies the rows to be merged into the target table. Refer to the SELECT statement or VALUES statement for a description of the syntax.

  • source_alias

    • A substitute name for the data source. When an alias is provided, it completely hides the actual name of the table or the fact that a query was issued.

  • join_condition

    • join_condition is an expression resulting in a value of type boolean (similar to a WHERE clause) that specifies which rows in the data_source match rows in the target table.

  • when_clause

    • At least one WHEN clause is required.

    • If the WHEN clause specifies WHEN MATCHED and the candidate change row matches a row in the target table, the WHEN clause is executed if the condition is absent or it evaluates to true.

    • Conversely, if the WHEN clause specifies WHEN NOT MATCHED and the candidate change row does not match a row in the target table, the WHEN clause is executed if the condition is absent or it evaluates to true.

  • condition

    • An expression that returns a value of type boolean. If this expression for a WHEN clause returns true, then the action for that clause is executed for that row.

    • A condition on a WHEN MATCHED clause can refer to columns in both the source and the target relations. A condition on a WHEN NOT MATCHED clause can only refer to columns from the source relation, since by definition there is no matching target row. Only the system attributes from the target table are accessible.

  • merge_insert

    • The specification of an INSERT action that inserts one row into the target table. The target column names can be listed in any order. If no list of column names is given at all, the default is all the columns of the table in their declared order.

    • Each column not present in the explicit or implicit column list will be filled with a default value, either its declared default value or null if there is none.

    • If the target table is a partitioned table, each row is routed to the appropriate partition and inserted into it. If the target table is a partition, an error will occur if any input row violates the partition constraint.

    • Column names may not be specified more than once. INSERT actions cannot contain sub-selects.

    • Only one VALUES clause can be specified. The VALUES clause can only refer to columns from the source relation, since by definition there is no matching target row.

  • merge_update

    • The specification of an UPDATE action that updates the current row of the target table. Column names may not be specified more than once.

    • Neither a table name nor a WHERE clause are allowed.

  • merge_delete

    • Specifies a DELETE action that deletes the current row of the target table. Do not include the table name or any other clauses, as you would normally do with a DELETE command.

  • column_name

    • The name of a column in the target table. The column name can be qualified with a subfield name or array subscript, if needed. (Inserting into only some fields of a composite column leaves the other fields null.) Do not include the table’s name in the specification of a target column.

  • OVERRIDING SYSTEM VALUE

    • Without this clause, it is an error to specify an explicit value (other than DEFAULT) for an identity column defined as GENERATED ALWAYS. This clause overrides that restriction.

  • OVERRIDING USER VALUE

    • If this clause is specified, then any values supplied for identity columns defined as GENERATED BY DEFAULT are ignored and the default sequence-generated values are applied.

  • DEFAULT VALUES

    • All columns will be filled with their default values. (An OVERRIDING clause is not permitted in this form.)

  • expression

    • An expression to assign to the column. If used in a WHEN MATCHED clause, the expression can use values from the original row in the target table, and values from the data_source row. If used in a WHEN NOT MATCHED clause, the expression can use values from the data_source row.

  • DEFAULT

    • Set the column to its default value (which will be NULL if no specific default expression has been assigned to it).

  • sub-SELECT

    • A SELECT sub-query that produces as many output columns as are listed in the parenthesized column list preceding it. The sub-query must yield no more than one row when executed. If it yields one row, its column values are assigned to the target columns; if it yields no rows, NULL values are assigned to the target columns. The sub-query can refer to values from the original row in the target table, and values from the data_source row.

Warning

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

Only columns from the target table that attempt to match data_source rows should appear in join_condition. join_condition subexpressions that only reference the target table’s columns can affect which action is taken, often in surprising ways.

Outputs

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

On successful completion, a MERGE command returns a command tag of the form

MERGE total_count

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

The total_count is the total number of rows changed (whether inserted, updated, or deleted). If total_count is 0, no rows were changed in any way.

Notes

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

The following steps take place during the execution of MERGE.

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

In summary, statement triggers for an event type (say, INSERT) will be fired whenever we specify an action of that kind. In contrast, row-level triggers will fire only for the specific event type being executed. So a MERGE command might fire statement triggers for both UPDATE and INSERT, even though only UPDATE row triggers were fired.

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

You should ensure that the join produces at most one candidate change row for each target row. In other words, a target row shouldn’t join to more than one data source row. If it does, then only one of the candidate change rows will be used to modify the target row; later attempts to modify the row will cause an error. This can also occur if row triggers make changes to the target table and the rows so modified are then subsequently also modified by MERGE. If the repeated action is an INSERT, this will cause a uniqueness violation, while a repeated UPDATE or DELETE will cause a cardinality violation; the latter behavior is required by the SQL standard. This differs from historical PostgreSQL behavior of joins in UPDATE and DELETE statements where second and subsequent attempts to modify the same row are simply ignored.

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

If a WHEN clause omits an AND sub-clause, it becomes the final reachable clause of that kind (MATCHED or NOT MATCHED). If a later WHEN clause of that kind is specified it would be provably unreachable and an error is raised. If no final reachable clause is specified of either kind, it is possible that no action will be taken for a candidate change row.

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

The order in which rows are generated from the data source is indeterminate by default. A source_query can be used to specify a consistent ordering, if required, which might be needed to avoid deadlocks between concurrent transactions.

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

There is no RETURNING clause with MERGE. Actions of INSERT, UPDATE and DELETE cannot contain RETURNING or WITH clauses.

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

When MERGE is run concurrently with other commands that modify the target table, the usual transaction isolation rules apply; see Section 13.2 for an explanation on the behavior at each isolation level. You may also wish to consider using INSERT …​ ON CONFLICT as an alternative statement which offers the ability to run an UPDATE if a concurrent INSERT occurs. There are a variety of differences and restrictions between the two statement types and they are not interchangeable.

Examples

基于新 recent_transactions 维护 customer_accounts 。

Perform maintenance on customer_accounts based upon new 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 结果在执行过程中不会更改。

Notice that this would be exactly equivalent to the following statement because the MATCHED result does not change during execution.

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

尝试插入一个新的库存项目以及库存数量。如果该项目已存在，则更新现有项目的库存计数。不允许库存为零的条目。

Attempt to insert a new stock item along with the quantity of stock. If the item already exists, instead update the stock count of the existing item. Don’t allow entries that have zero stock.

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 表可能是最近加载到数据库中的临时表。

The wine_stock_changes table might be, for example, a temporary table recently loaded into the database.

Compatibility

此命令符合 SQL 标准。

This command conforms to the SQL standard.

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

The WITH clause and DO NOTHING action are extensions to the SQL standard.