1MERGE(7) PostgreSQL 15.4 Documentation MERGE(7)
2
6 MERGE - conditionally insert, update, or delete rows of a table
7
9 [ WITH with_query [, ...] ]
10 MERGE INTO [ ONLY ] target_table_name [ * ] [ [ AS ] target_alias ]
11 USING data_source ON join_condition
12 when_clause [...]
13 where data_source is:
15 { [ ONLY ] source_table_name [ * ] | ( source_query ) } [ [ AS ] source_alias ]
17 and when_clause is:
19 { WHEN MATCHED [ AND condition ] THEN { merge_update | merge_delete | DO NOTHING } |
21 WHEN NOT MATCHED [ AND condition ] THEN { merge_insert | DO NOTHING } }
22 and merge_insert is:
24 INSERT [( column_name [, ...] )]
26 [ OVERRIDING { SYSTEM | USER } VALUE ]
27 { VALUES ( { expression | DEFAULT } [, ...] ) | DEFAULT VALUES }
28 and merge_update is:
30 UPDATE SET { column_name = { expression | DEFAULT } |
32 ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...]
33 and merge_delete is:
35 DELETE
37
39 MERGE performs actions that modify rows in the target_table_name, using
40 the data_source. MERGE provides a single SQL statement that can
41 conditionally INSERT, UPDATE or DELETE rows, a task that would
42 otherwise require multiple procedural language statements.
43 First, the MERGE command performs a join from data_source to
45 target_table_name producing zero or more candidate change rows. For
46 each candidate change row, the status of MATCHED or NOT MATCHED is set
47 just once, after which WHEN clauses are evaluated in the order
48 specified. For each candidate change row, the first clause to evaluate
49 as true is executed. No more than one WHEN clause is executed for any
50 candidate change row.
51 MERGE actions have the same effect as regular UPDATE, INSERT, or DELETE
53 commands of the same names. The syntax of those commands is different,
54 notably that there is no WHERE clause and no table name is specified.
55 All actions refer to the target_table_name, though modifications to
56 other tables may be made using triggers.
57 When DO NOTHING is specified, the source row is skipped. Since actions
59 are evaluated in their specified order, DO NOTHING can be handy to skip
60 non-interesting source rows before more fine-grained handling.
61 There is no separate MERGE privilege. If you specify an update action,
63 you must have the UPDATE privilege on the column(s) of the
64 target_table_name that are referred to in the SET clause. If you
65 specify an insert action, you must have the INSERT privilege on the
66 target_table_name. If you specify an delete action, you must have the
67 DELETE privilege on the target_table_name. Privileges are tested once
68 at statement start and are checked whether or not particular WHEN
69 clauses are executed. You will require the SELECT privilege on the
70 data_source and any column(s) of the target_table_name referred to in a
71 condition.
72 MERGE is not supported if the target_table_name is a materialized view,
74 foreign table, or if it has any rules defined on it.
75
77 target_table_name
78 The name (optionally schema-qualified) of the target table to merge
79 into. If ONLY is specified before the table name, matching rows are
80 updated or deleted in the named table only. If ONLY is not
81 specified, matching rows are also updated or deleted in any tables
82 inheriting from the named table. Optionally, * can be specified
83 after the table name to explicitly indicate that descendant tables
84 are included. The ONLY keyword and * option do not affect insert
85 actions, which always insert into the named table only.
86 target_alias
88 A substitute name for the target table. When an alias is provided,
89 it completely hides the actual name of the table. For example,
90 given MERGE INTO foo AS f, the remainder of the MERGE statement
91 must refer to this table as f not foo.
92 source_table_name
94 The name (optionally schema-qualified) of the source table, view,
95 or transition table. If ONLY is specified before the table name,
96 matching rows are included from the named table only. If ONLY is
97 not specified, matching rows are also included from any tables
98 inheriting from the named table. Optionally, * can be specified
99 after the table name to explicitly indicate that descendant tables
100 are included.
101 source_query
103 A query (SELECT statement or VALUES statement) that supplies the
104 rows to be merged into the target_table_name. Refer to the
105 SELECT(7) statement or VALUES(7) statement for a description of the
106 syntax.
107 source_alias
109 A substitute name for the data source. When an alias is provided,
110 it completely hides the actual name of the table or the fact that a
111 query was issued.
112 join_condition
114 join_condition is an expression resulting in a value of type
115 boolean (similar to a WHERE clause) that specifies which rows in
116 the data_source match rows in the target_table_name.
117 Warning
119 Only columns from target_table_name that attempt to match
120 data_source rows should appear in join_condition.
121 join_condition subexpressions that only reference
122 target_table_name columns can affect which action is taken,
123 often in surprising ways.
124 when_clause
126 At least one WHEN clause is required.
127 If the WHEN clause specifies WHEN MATCHED and the candidate change
129 row matches a row in the target_table_name, the WHEN clause is
130 executed if the condition is absent or it evaluates to true.
131 Conversely, if the WHEN clause specifies WHEN NOT MATCHED and the
133 candidate change row does not match a row in the target_table_name,
134 the WHEN clause is executed if the condition is absent or it
135 evaluates to true.
136 condition
138 An expression that returns a value of type boolean. If this
139 expression for a WHEN clause returns true, then the action for that
140 clause is executed for that row.
141 A condition on a WHEN MATCHED clause can refer to columns in both
143 the source and the target relations. A condition on a WHEN NOT
144 MATCHED clause can only refer to columns from the source relation,
145 since by definition there is no matching target row. Only the
146 system attributes from the target table are accessible.
147 merge_insert
149 The specification of an INSERT action that inserts one row into the
150 target table. The target column names can be listed in any order.
151 If no list of column names is given at all, the default is all the
152 columns of the table in their declared order.
153 Each column not present in the explicit or implicit column list
155 will be filled with a default value, either its declared default
156 value or null if there is none.
157 If target_table_name is a partitioned table, each row is routed to
159 the appropriate partition and inserted into it. If
160 target_table_name is a partition, an error will occur if any input
161 row violates the partition constraint.
162 Column names may not be specified more than once. INSERT actions
164 cannot contain sub-selects.
165 Only one VALUES clause can be specified. The VALUES clause can only
167 refer to columns from the source relation, since by definition
168 there is no matching target row.
169 merge_update
171 The specification of an UPDATE action that updates the current row
172 of the target_table_name. Column names may not be specified more
173 than once.
174 Neither a table name nor a WHERE clause are allowed.
176 merge_delete
178 Specifies a DELETE action that deletes the current row of the
179 target_table_name. Do not include the table name or any other
180 clauses, as you would normally do with a DELETE(7) command.
181 column_name
183 The name of a column in the target_table_name. The column name can
184 be qualified with a subfield name or array subscript, if needed.
185 (Inserting into only some fields of a composite column leaves the
186 other fields null.) Do not include the table's name in the
187 specification of a target column.
188 OVERRIDING SYSTEM VALUE
190 Without this clause, it is an error to specify an explicit value
191 (other than DEFAULT) for an identity column defined as GENERATED
192 ALWAYS. This clause overrides that restriction.
193 OVERRIDING USER VALUE
195 If this clause is specified, then any values supplied for identity
196 columns defined as GENERATED BY DEFAULT are ignored and the default
197 sequence-generated values are applied.
198 DEFAULT VALUES
200 All columns will be filled with their default values. (An
201 OVERRIDING clause is not permitted in this form.)
202 expression
204 An expression to assign to the column. If used in a WHEN MATCHED
205 clause, the expression can use values from the original row in the
206 target table, and values from the data_source row. If used in a
207 WHEN NOT MATCHED clause, the expression can use values from the
208 data_source.
209 DEFAULT
211 Set the column to its default value (which will be NULL if no
212 specific default expression has been assigned to it).
213 with_query
215 The WITH clause allows you to specify one or more subqueries that
216 can be referenced by name in the MERGE query. See Section 7.8 and
217 SELECT(7) for details.
218
220 On successful completion, a MERGE command returns a command tag of the
221 form
222 MERGE total_count
224 The total_count is the total number of rows changed (whether inserted,
226 updated, or deleted). If total_count is 0, no rows were changed in any
227 way.
228
230 The following steps take place during the execution of MERGE.
231 1. Perform any BEFORE STATEMENT triggers for all actions specified,
233 whether or not their WHEN clauses match.
234 2. Perform a join from source to target table. The resulting query
236 will be optimized normally and will produce a set of candidate
237 change rows. For each candidate change row,
238 1. Evaluate whether each row is MATCHED or NOT MATCHED.
240 2. Test each WHEN condition in the order specified until one
242 returns true.
243 3. When a condition returns true, perform the following actions:
245 1. Perform any BEFORE ROW triggers that fire for the action's
247 event type.
248 2. Perform the specified action, invoking any check
250 constraints on the target table.
251 3. Perform any AFTER ROW triggers that fire for the action's
253 event type.
254 3. Perform any AFTER STATEMENT triggers for actions specified, whether
256 or not they actually occur. This is similar to the behavior of an
257 UPDATE statement that modifies no rows.
258 In summary, statement triggers for an event type (say, INSERT) will be
260 fired whenever we specify an action of that kind. In contrast,
261 row-level triggers will fire only for the specific event type being
262 executed. So a MERGE command might fire statement triggers for both
263 UPDATE and INSERT, even though only UPDATE row triggers were fired.
264 You should ensure that the join produces at most one candidate change
266 row for each target row. In other words, a target row shouldn't join to
267 more than one data source row. If it does, then only one of the
268 candidate change rows will be used to modify the target row; later
269 attempts to modify the row will cause an error. This can also occur if
270 row triggers make changes to the target table and the rows so modified
271 are then subsequently also modified by MERGE. If the repeated action is
272 an INSERT, this will cause a uniqueness violation, while a repeated
273 UPDATE or DELETE will cause a cardinality violation; the latter
274 behavior is required by the SQL standard. This differs from historical
275 PostgreSQL behavior of joins in UPDATE and DELETE statements where
276 second and subsequent attempts to modify the same row are simply
277 ignored.
278 If a WHEN clause omits an AND sub-clause, it becomes the final
280 reachable clause of that kind (MATCHED or NOT MATCHED). If a later WHEN
281 clause of that kind is specified it would be provably unreachable and
282 an error is raised. If no final reachable clause is specified of either
283 kind, it is possible that no action will be taken for a candidate
284 change row.
285 The order in which rows are generated from the data source is
287 indeterminate by default. A source_query can be used to specify a
288 consistent ordering, if required, which might be needed to avoid
289 deadlocks between concurrent transactions.
290 There is no RETURNING clause with MERGE. Actions of INSERT, UPDATE and
292 DELETE cannot contain RETURNING or WITH clauses.
293 When MERGE is run concurrently with other commands that modify the
295 target table, the usual transaction isolation rules apply; see
296 Section 13.2 for an explanation on the behavior at each isolation
297 level. You may also wish to consider using INSERT ... ON CONFLICT as an
298 alternative statement which offers the ability to run an UPDATE if a
299 concurrent INSERT occurs. There are a variety of differences and
300 restrictions between the two statement types and they are not
301 interchangeable.
302
304 Perform maintenance on customer_accounts based upon new
305 recent_transactions.
306 MERGE INTO customer_account ca
308 USING recent_transactions t
309 ON t.customer_id = ca.customer_id
310 WHEN MATCHED THEN
311 UPDATE SET balance = balance + transaction_value
312 WHEN NOT MATCHED THEN
313 INSERT (customer_id, balance)
314 VALUES (t.customer_id, t.transaction_value);
315 Notice that this would be exactly equivalent to the following statement
317 because the MATCHED result does not change during execution.
318 MERGE INTO customer_account ca
320 USING (SELECT customer_id, transaction_value FROM recent_transactions) AS t
321 ON t.customer_id = ca.customer_id
322 WHEN MATCHED THEN
323 UPDATE SET balance = balance + transaction_value
324 WHEN NOT MATCHED THEN
325 INSERT (customer_id, balance)
326 VALUES (t.customer_id, t.transaction_value);
327 Attempt to insert a new stock item along with the quantity of stock. If
329 the item already exists, instead update the stock count of the existing
330 item. Don't allow entries that have zero stock.
331 MERGE INTO wines w
333 USING wine_stock_changes s
334 ON s.winename = w.winename
335 WHEN NOT MATCHED AND s.stock_delta > 0 THEN
336 INSERT VALUES(s.winename, s.stock_delta)
337 WHEN MATCHED AND w.stock + s.stock_delta > 0 THEN
338 UPDATE SET stock = w.stock + s.stock_delta
339 WHEN MATCHED THEN
340 DELETE;
341 The wine_stock_changes table might be, for example, a temporary table
343 recently loaded into the database.
344
346 This command conforms to the SQL standard.
347 The WITH clause and DO NOTHING action are extensions to the SQL
349 standard.
350PostgreSQL 15.4 2023 MERGE(7)