[HACKERS] MERGE SQL Statement for PG11

[Simon Riggs]

I'm working on re-submitting MERGE for PG11

Earlier thoughts on how this could/could not be done were sometimes
imprecise or inaccurate, so I have gone through the command per
SQL:2011 spec and produced a definitive spec in the form of an SGML
ref page. This is what I intend to deliver for PG11.

MERGE will use the same mechanisms as INSERT ON CONFLICT, so
concurrent behavior does not require further infrastructure changes,
just detailed work on the statement itself.

I'm building up the code from scratch based upon the spec, rather than
trying to thwack earlier attempts into shape. This looks more likely
to yield a commitable patch.

Major spanners or objections, please throw them in now cos I don't see any.

Questions?

-- 
Simon Riggs                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Title: MERGE

MERGE
Prev	Up	SQL Commands	Home	Next

---

MERGE

MERGE — insert, update, or delete rows of a table based upon source data

Synopsis

MERGE INTO target_table_name [ [ AS ] target_alias ]
USING data_source
ON join_condition
when_clause [...]

where data_source is

{ source_table_name |
  ( source_query )
}
[ [ AS ] source_alias ]

where when_clause is

{ WHEN MATCHED [ AND condition ] THEN { merge_update | merge_delete } |
  WHEN NOT MATCHED [ AND condition ] THEN { merge_insert | DO NOTHING }
}

where merge_update is

UPDATE SET { column_name = { _expression_ | DEFAULT } |
             ( column_name [, ...] ) = ( { _expression_ | DEFAULT } [, ...] )
           } [, ...]

and merge_insert is

INSERT [( column_name [, ...] )]
[ OVERRIDING { SYSTEM | USER } VALUE ]
{ VALUES ( { _expression_ | DEFAULT } [, ...] ) | DEFAULT VALUES }

and merge_delete is

DELETE

Description

MERGE performs actions that modify rows in the 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.

First, the MERGE command performs a left outer join from data_source to target_table_name producing zero or more candidate change rows. For each candidate change row, WHEN clauses are evaluated in the order specified; if one of them is activated, the specified action occurs. No more than one WHEN clause can be activated for any candidate change row.

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 tablename is specified. All actions refer to the target_table_name, though modifications to other tables may be made using triggers.

The ON clause must join on all of the column(s) of the primary key, or if other columns are specified then another unique index on the target_table_name. Each candidate change row is locked via speculative insertion into the chosen unique index, ensuring that the status of MATCHED or NOT MATCHED is decided just once for each candidate change row, preventing interference from concurrent transactions. As a result of these requirements MERGE cannot yet work against partitioned tables.

There is no MERGE privilege. You must have the UPDATE privilege on the column(s) of the target_table_name referred to in the SET clause if you specify an update action, the INSERT privilege on the target_table_name if you specify an insert action and/or the DELETE privilege on the if you specify a delete action on the target_table_name. Privileges are tested once at statement start and are checked whether or not particular WHEN clauses are activated during the subsequent execution. You will require the SELECT privilege on the data_source and any column(s) of the target_table_name referred to in a condition.

Parameters

target_table_name

The name (optionally schema-qualified) of the target table to merge into.

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 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.

source_query

A query (SELECT statement or VALUES statement) that supplies the rows to be merged into the target_table_name. 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 whether table or query was specified.

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_name. match. The join condition must refer to the column(s) of the target_table_name that form the primary index, or if not, then another unique index.

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_name the WHEN clause is activated if the condition is absent or is present and evaluates to true. If the WHEN clause specifies WHEN NOT MATCHED and the candidate change row does not match a row in the target_table_name the WHEN clause is activated if the condition is absent or is present and evaluates to true.

condition

An _expression_ that returns a value of type boolean. If this _expression_ returns true then the WHEN clause will be activated and the corresponding action will occur for that row.

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 _expression_ for any column is not of the correct data type, automatic type conversion will be attempted.

If target_table_name is a partitioned table, each row is routed to the appropriate partition and inserted into it. If target_table_name is a partition, an error will occur if one of the input rows violates the partition constraint.

Do not include the table name, as you would normally do with an INSERT command. For example, INSERT INTO tab VALUES (1, 50) is invalid. Column names may not be specified more than once. INSERT actions cannot contain sub-selects.

merge_update

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

Do not include the table name, as you would normally do with an UPDATE command. For example, UPDATE tab SET col = 1 is invalid. Also, do not include a WHERE clause, since only the current row can be updated. For example, UPDATE SET col = 1 WHERE key = 57 is invalid. UPDATE actions cannot contain sub-selects in the SET clause.

merge_delete

Specifies a DELETE action that deletes the current row of the target_table_name. Do not include the tablename or any other clauses, as you would normally do with an DELETE command.

column_name

The name of a column in the target_table_name. 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.) When referencing a column, 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. The _expression_ can use the old values of this and other columns in the table.

DEFAULT

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

Outputs

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

MERGE total-count

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

Notes

The following steps take place during the execution of MERGE.

1. Perform any BEFORE STATEMENT triggers for all actions specified, whether or not their WHEN clauses are activated during execution.

2. Perform left outer join from source to target table. Then for each candidate change row

   1. Evaluate whether each row is MATCHED or NOT MATCHED using speculative insertion into the target table using the unique index specified in the ON clause.

   2. Test each WHEN condition in the order specified until one activates.

   3. When activated, perform the following actions

      1. Perform any BEFORE ROW triggers that fire for the action's event type.

      2. Apply the action specified, invoking any check constraints on the target table. However, it will not invoke rules.

      3. Perform any AFTER ROW triggers that fire for the action's event type.

3. Perform any AFTER STATEMENT triggers for actions specified, whether or not they actually occur. This is similar to the behavior of an UPDATE statement that modifies no rows.

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

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.

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 will cause an error. This can also occur if row triggers make changes to the target table which are then subsequently 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 are simply ignored.

If the ON clause is a constant _expression_ that evaluates to false then no join takes place and the source is used directly as candidate change rows.

If a WHEN clause omits an AND 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.

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

Examples

Perform maintenance on CustomerAccounts based upon new Transactions.

MERGE CustomerAccount CA
USING RecentTransactions T
ON T.CustomerId = CA.CustomerId
WHEN MATCHED THEN
  UPDATE SET Balance = Balance + TransactionValue
WHEN NOT MATCHED THEN
  INSERT (CustomerId, Balance)
  VALUES (T.CustomerId, T.TransactionValue)
;

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

MERGE CustomerAccount CA
USING (Select CustomerId, TransactionValue From RecentTransactions) AS T
ON CA.CustomerId = T.CustomerId
WHEN NOT MATCHED THEN
  INSERT (CustomerId, Balance)
  VALUES (T.CustomerId, T.TransactionValue)
WHEN MATCHED THEN
  UPDATE SET Balance = Balance + TransactionValue
;

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;
ELSE
  DELETE
;

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

Compatibility

This command conforms to the SQL standard, except that the DO NOTHING clause is a PostgreSQL extension.

---

Prev	Up	Next
LOCK	Home	MOVE

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers