| PostgreSQL 8.4devel Documentation | ||||
|---|---|---|---|---|
| Prev | Fast Backward | Fast Forward | Next | |
MERGE INTO table [ [ AS ] alias ]
USING source-query
ON join_condition
[when_clause [...]]
where when_clause is
{ WHEN MATCHED [ AND condition ] THEN { merge_update | DELETE }
WHEN NOT MATCHED [ AND condition ] THEN { merge_insert | DO NOTHING } }
where merge_update is
UPDATE SET { column = { expression | DEFAULT } |
( column [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...]
and merge_insert is
INSERT [( column [, ...] )] { VALUES ( { expression | DEFAULT } [, ...] ) | DEFAULT VALUES }MERGE performs at most one action on rows from the target table, driven by the rows from the source query. This provides a way to specify a single SQL statement that can conditionally UPDATE or INSERT rows, a task that would otherwise require multiple procedural language statements.
First, the MERGE command performs a left outer join from source query to target table, producing zero or more merged rows. For each merged row, WHEN clauses are evaluated in the specified order until one of them is activated. The corresponding action is then applied and processing continues for the next row.
MERGE actions have the same effect as regular UPDATE, INSERT, or DELETE commands of the same names, though the syntax is slightly different.
If no WHEN clause activates then an implicit action of INSERT DEFAULT VALUES is performed for that row. If that implicit action is not desirable an explicit action of DO NOTHING may be specified instead.
MERGE will only affect rows only in the specified table.
There is no RETURNING clause with MERGE.
There is no MERGE privilege. You must have the UPDATE privilege on the table if you specify an update action, the INSERT privilege if you specify an insert action and/or the DELETE privilege if you wish to delete. You will also require the SELECT privilege to any table whose values are read in the expressions or condition.
The name (optionally schema-qualified) of the table to merge into.
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.
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.
join_condition is an expression resulting in a value of type boolean (similar to a WHERE clause) that specifies which rows in the join are considered to match. You should ensure that the join produces at most one output row for each row to be modified. An attempt to modify any row of the target table more than once will result in an error. This behaviour requires the user to take greater care in using MERGE, though is required explicitly by the SQL Standard.
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.
The specification of an UPDATE action. 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 can be updated. For example, UPDATE SET col = 1 WHERE key = 57 is invalid.
The specification of an INSERT action. 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.
The name of a column in table. The column name can be qualified with a subfield name or array subscript, if needed. Do not include the table's name in the specification of a target column — for example, UPDATE SET tab.col = 1 is invalid.
An expression to assign to the column. The expression can use the old values of this and other columns in the table.
Set the column to its default value (which will be NULL if no specific default expression has been assigned to it).
On successful completion, a MERGE command returns a command tag of the form
MERGE total-count
The total-count is the number of rows changed (either updated, inserted or deleted). If total-count is 0, no rows were changed (this is not considered an error).
The number of rows updated, inserted or deleted is not available as part of the command tag. An optional NOTIFY message can be generated to present this information, if desired.
NOTIFY: 34 rows processed: 11 updated, 5 deleted, 15 inserted, 3 default inserts, 0 no action
What essentially happens is that the target table is left outer-joined to the tables mentioned in the source-query, and each output row of the join may then activate at most one when-clause. The row will be matched only once per statement, so the status of MATCHED or NOT MATCHED cannot change once testing of WHEN clauses has begun. MERGE will not invoke Rules.
The following steps take place during the execution of MERGE.
Perform any BEFORE STATEMENT triggers for actions specified, whether or not they actually occur.
Perform left outer join from source to target table. Then for each row:
Evaluate whether each row is MATCHED or NOT MATCHED.
Test each WHEN condition in the order specified until one activates. Identify the action and its event type.
Perform any BEFORE ROW triggers that fire for the action's event type.
Apply the action specified.
Perform any AFTER ROW triggers that fire for the action's event type.
Perform any AFTER STATEMENT triggers for actions specified, whether or not they actually occur.
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 event type activated. So a MERGE might fire statement triggers for both UPDATE and INSERT, even though only UPDATE row triggers were fired.
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.
MERGE INTO wines w
USING (VALUES('Chateau Lafite 2003', '24')) v
ON v.column1 = w.winename
WHEN NOT MATCHED
INSERT VALUES(v.column1, v.column2)
WHEN MATCHED
UPDATE SET stock = stock + v.column2;
Perform maintenance on CustomerAccounts based upon new Transactions. The following statement will fail if any accounts have had more than one transaction
MERGE CustomerAccount CA
USING (SELECT CustomerId, TransactionValue,
FROM Transactions
WHERE TransactionId > 35345678) AS T
ON T.CustomerId = CA.CustomerId
WHEN MATCHED
UPDATE SET Balance = Balance - TransactionValue
WHEN NOT MATCHED
INSERT (CustomerId, Balance)
VALUES (T.CustomerId, T.TransactionValue)
;so the right way to do this is to pre-aggregate the data
MERGE CustomerAccount CA
USING (SELECT CustomerId, Sum(TransactionValue) As TransactionSum
FROM Transactions
WHERE TransactionId > 35345678
GROUP BY CustomerId) AS T
ON T.CustomerId = CA.CustomerId
WHEN MATCHED
UPDATE SET Balance = Balance - TransactionSum
WHEN NOT MATCHED
INSERT (CustomerId, Balance)
VALUES (T.CustomerId, T.TransactionSum)
;
This command conforms to the SQL standard, except that the DELETE and DO NOTHING actions are PostgreSQL extensions.
According to the standard, the column-list syntax for an UPDATE action should allow a list of columns to be assigned from a single row-valued expression. This is not currently implemented — the source must be a list of independent expressions.