Debian Codegraph @

doc/src/sgml/ref/merge.sgml

raw
1 <!--
2 doc/src/sgml/ref/merge.sgml
3 PostgreSQL documentation
4 -->
5
6 <refentry id="sql-merge">
7 <indexterm zone="sql-merge">
8 <primary>MERGE</primary>
9 </indexterm>
10
11 <refmeta>
12 <refentrytitle>MERGE</refentrytitle>
13 <manvolnum>7</manvolnum>
14 <refmiscinfo>SQL - Language Statements</refmiscinfo>
15 </refmeta>
16
17 <refnamediv>
18 <refname>MERGE</refname>
19 <refpurpose>conditionally insert, update, or delete rows of a table</refpurpose>
20 </refnamediv>
21
22 <refsynopsisdiv>
23 <synopsis>
24 [ WITH <replaceable class="parameter">with_query</replaceable> [, ...] ]
25 MERGE INTO [ ONLY ] <replaceable class="parameter">target_table_name</replaceable> [ * ] [ [ AS ] <replaceable class="parameter">target_alias</replaceable> ]
26 USING <replaceable class="parameter">data_source</replaceable> ON <replaceable class="parameter">join_condition</replaceable>
27 <replaceable class="parameter">when_clause</replaceable> [...]
28 [ RETURNING [ WITH ( { OLD | NEW } AS <replaceable class="parameter">output_alias</replaceable> [, ...] ) ]
29 { * | <replaceable class="parameter">output_expression</replaceable> [ [ AS ] <replaceable class="parameter">output_name</replaceable> ] } [, ...] ]
30
31 <phrase>where <replaceable class="parameter">data_source</replaceable> is:</phrase>
32
33 { [ ONLY ] <replaceable class="parameter">source_table_name</replaceable> [ * ] | ( <replaceable class="parameter">source_query</replaceable> ) } [ [ AS ] <replaceable class="parameter">source_alias</replaceable> ]
34
35 <phrase>and <replaceable class="parameter">when_clause</replaceable> is:</phrase>
36
37 { WHEN MATCHED [ AND <replaceable class="parameter">condition</replaceable> ] THEN { <replaceable class="parameter">merge_update</replaceable> | <replaceable class="parameter">merge_delete</replaceable> | DO NOTHING } |
38 WHEN NOT MATCHED BY SOURCE [ AND <replaceable class="parameter">condition</replaceable> ] THEN { <replaceable class="parameter">merge_update</replaceable> | <replaceable class="parameter">merge_delete</replaceable> | DO NOTHING } |
39 WHEN NOT MATCHED [ BY TARGET ] [ AND <replaceable class="parameter">condition</replaceable> ] THEN { <replaceable class="parameter">merge_insert</replaceable> | DO NOTHING } }
40
41 <phrase>and <replaceable class="parameter">merge_insert</replaceable> is:</phrase>
42
43 INSERT [( <replaceable class="parameter">column_name</replaceable> [, ...] )]
44 [ OVERRIDING { SYSTEM | USER } VALUE ]
45 { VALUES ( { <replaceable class="parameter">expression</replaceable> | DEFAULT } [, ...] ) | DEFAULT VALUES }
46
47 <phrase>and <replaceable class="parameter">merge_update</replaceable> is:</phrase>
48
49 UPDATE SET { <replaceable class="parameter">column_name</replaceable> = { <replaceable class="parameter">expression</replaceable> | DEFAULT } |
50 ( <replaceable class="parameter">column_name</replaceable> [, ...] ) = [ ROW ] ( { <replaceable class="parameter">expression</replaceable> | DEFAULT } [, ...] ) |
51 ( <replaceable class="parameter">column_name</replaceable> [, ...] ) = ( <replaceable class="parameter">sub-SELECT</replaceable> )
52 } [, ...]
53
54 <phrase>and <replaceable class="parameter">merge_delete</replaceable> is:</phrase>
55
56 DELETE
57 </synopsis>
58 </refsynopsisdiv>
59
60 <refsect1>
61 <title>Description</title>
62
63 <para>
64 <command>MERGE</command> performs actions that modify rows in the
65 target table identified as <replaceable class="parameter">target_table_name</replaceable>,
66 using the <replaceable class="parameter">data_source</replaceable>.
67 <command>MERGE</command> provides a single <acronym>SQL</acronym>
68 statement that can conditionally <command>INSERT</command>,
69 <command>UPDATE</command> or <command>DELETE</command> rows, a task
70 that would otherwise require multiple procedural language statements.
71 </para>
72
73 <para>
74 First, the <command>MERGE</command> command performs a join
75 from <replaceable class="parameter">data_source</replaceable> to
76 the target table
77 producing zero or more candidate change rows. For each candidate change
78 row, the status of <literal>MATCHED</literal>,
79 <literal>NOT MATCHED BY SOURCE</literal>,
80 or <literal>NOT MATCHED [BY TARGET]</literal>
81 is set just once, after which <literal>WHEN</literal> clauses are evaluated
82 in the order specified. For each candidate change row, the first clause to
83 evaluate as true is executed. No more than one <literal>WHEN</literal>
84 clause is executed for any candidate change row.
85 </para>
86
87 <para>
88 <command>MERGE</command> actions have the same effect as
89 regular <command>UPDATE</command>, <command>INSERT</command>, or
90 <command>DELETE</command> commands of the same names. The syntax of
91 those commands is different, notably that there is no <literal>WHERE</literal>
92 clause and no table name is specified. All actions refer to the
93 target table,
94 though modifications to other tables may be made using triggers.
95 </para>
96
97 <para>
98 When <literal>DO NOTHING</literal> is specified, the source row is
99 skipped. Since actions are evaluated in their specified order, <literal>DO
100 NOTHING</literal> can be handy to skip non-interesting source rows before
101 more fine-grained handling.
102 </para>
103
104 <para>
105 The optional <literal>RETURNING</literal> clause causes <command>MERGE</command>
106 to compute and return value(s) based on each row inserted, updated, or
107 deleted. Any expression using the source or target table's columns, or
108 the <link linkend="merge-action"><function>merge_action()</function></link>
109 function can be computed. By default, when an <command>INSERT</command> or
110 <command>UPDATE</command> action is performed, the new values of the target
111 table's columns are used, and when a <command>DELETE</command> is performed,
112 the old values of the target table's columns are used, but it is also
113 possible to explicitly request old and new values. The syntax of the
114 <literal>RETURNING</literal> list is identical to that of the output list
115 of <command>SELECT</command>.
116 </para>
117
118 <para>
119 There is no separate <literal>MERGE</literal> privilege.
120 If you specify an update action, you must have the
121 <literal>UPDATE</literal> privilege on the column(s)
122 of the target table
123 that are referred to in the <literal>SET</literal> clause.
124 If you specify an insert action, you must have the <literal>INSERT</literal>
125 privilege on the target table.
126 If you specify a delete action, you must have the <literal>DELETE</literal>
127 privilege on the target table.
128 If you specify a <literal>DO NOTHING</literal> action, you must have
129 the <literal>SELECT</literal> privilege on at least one column
130 of the target table.
131 You will also need <literal>SELECT</literal> privilege on any column(s)
132 of the <replaceable class="parameter">data_source</replaceable> and
133 of the target table referred to
134 in any <literal>condition</literal> (including <literal>join_condition</literal>)
135 or <literal>expression</literal>.
136 Privileges are tested once at statement start and are checked
137 whether or not particular <literal>WHEN</literal> clauses are executed.
138 </para>
139
140 <para>
141 <command>MERGE</command> is not supported if the
142 target table is a
143 materialized view, foreign table, or if it has any
144 rules defined on it.
145 </para>
146 </refsect1>
147
148 <refsect1>
149 <title>Parameters</title>
150
151 <variablelist>
152 <varlistentry>
153 <term><replaceable class="parameter">with_query</replaceable></term>
154 <listitem>
155 <para>
156 The <literal>WITH</literal> clause allows you to specify one or more
157 subqueries that can be referenced by name in the <command>MERGE</command>
158 query. See <xref linkend="queries-with"/> and <xref linkend="sql-select"/>
159 for details. Note that <literal>WITH RECURSIVE</literal> is not supported
160 by <command>MERGE</command>.
161 </para>
162 </listitem>
163 </varlistentry>
164
165 <varlistentry>
166 <term><replaceable class="parameter">target_table_name</replaceable></term>
167 <listitem>
168 <para>
169 The name (optionally schema-qualified) of the target table or view to
170 merge into. If <literal>ONLY</literal> is specified before a table
171 name, matching rows are updated or deleted in the named table only. If
172 <literal>ONLY</literal> is not specified, matching rows are also updated
173 or deleted in any tables inheriting from the named table. Optionally,
174 <literal>*</literal> can be specified after the table name to explicitly
175 indicate that descendant tables are included. The
176 <literal>ONLY</literal> keyword and <literal>*</literal> option do not
177 affect insert actions, which always insert into the named table only.
178 </para>
179
180 <para>
181 If <replaceable class="parameter">target_table_name</replaceable> is a
182 view, it must either be automatically updatable with no
183 <literal>INSTEAD OF</literal> triggers, or it must have
184 <literal>INSTEAD OF</literal> triggers for every type of action
185 (<literal>INSERT</literal>, <literal>UPDATE</literal>, and
186 <literal>DELETE</literal>) specified in the <literal>WHEN</literal>
187 clauses. Views with rules are not supported.
188 </para>
189 </listitem>
190 </varlistentry>
191
192 <varlistentry>
193 <term><replaceable class="parameter">target_alias</replaceable></term>
194 <listitem>
195 <para>
196 A substitute name for the target table. When an alias is
197 provided, it completely hides the actual name of the table. For
198 example, given <literal>MERGE INTO foo AS f</literal>, the remainder of the
199 <command>MERGE</command> statement must refer to this table as
200 <literal>f</literal> not <literal>foo</literal>.
201 </para>
202 </listitem>
203 </varlistentry>
204
205 <varlistentry>
206 <term><replaceable class="parameter">source_table_name</replaceable></term>
207 <listitem>
208 <para>
209 The name (optionally schema-qualified) of the source table, view, or
210 transition table. If <literal>ONLY</literal> is specified before the
211 table name, matching rows are included from the named table only. If
212 <literal>ONLY</literal> is not specified, matching rows are also included
213 from any tables inheriting from the named table. Optionally,
214 <literal>*</literal> can be specified after the table name to explicitly
215 indicate that descendant tables are included.
216 </para>
217 </listitem>
218 </varlistentry>
219
220 <varlistentry>
221 <term><replaceable class="parameter">source_query</replaceable></term>
222 <listitem>
223 <para>
224 A query (<command>SELECT</command> statement or <command>VALUES</command>
225 statement) that supplies the rows to be merged into the
226 target table.
227 Refer to the <xref linkend="sql-select"/>
228 statement or <xref linkend="sql-values"/>
229 statement for a description of the syntax.
230 </para>
231 </listitem>
232 </varlistentry>
233
234 <varlistentry>
235 <term><replaceable class="parameter">source_alias</replaceable></term>
236 <listitem>
237 <para>
238 A substitute name for the data source. When an alias is
239 provided, it completely hides the actual name of the table or the fact
240 that a query was issued.
241 </para>
242 </listitem>
243 </varlistentry>
244
245 <varlistentry>
246 <term><replaceable class="parameter">join_condition</replaceable></term>
247 <listitem>
248 <para>
249 <replaceable class="parameter">join_condition</replaceable> is
250 an expression resulting in a value of type
251 <type>boolean</type> (similar to a <literal>WHERE</literal>
252 clause) that specifies which rows in the
253 <replaceable class="parameter">data_source</replaceable>
254 match rows in the target table.
255 </para>
256 <warning>
257 <para>
258 Only columns from the target table
259 that attempt to match <replaceable class="parameter">data_source</replaceable>
260 rows should appear in <replaceable class="parameter">join_condition</replaceable>.
261 <replaceable class="parameter">join_condition</replaceable> subexpressions that
262 only reference the target table's
263 columns can affect which action is taken, often in surprising ways.
264 </para>
265 <para>
266 If both <literal>WHEN NOT MATCHED BY SOURCE</literal> and
267 <literal>WHEN NOT MATCHED [BY TARGET]</literal> clauses are specified,
268 the <command>MERGE</command> command will perform a <literal>FULL</literal>
269 join between <replaceable class="parameter">data_source</replaceable>
270 and the target table. For this to work, at least one
271 <replaceable class="parameter">join_condition</replaceable> subexpression
272 must use an operator that can support a hash join, or all of the
273 subexpressions must use operators that can support a merge join.
274 </para>
275 </warning>
276 </listitem>
277 </varlistentry>
278
279 <varlistentry>
280 <term><replaceable class="parameter">when_clause</replaceable></term>
281 <listitem>
282 <para>
283 At least one <literal>WHEN</literal> clause is required.
284 </para>
285 <para>
286 The <literal>WHEN</literal> clause may specify <literal>WHEN MATCHED</literal>,
287 <literal>WHEN NOT MATCHED BY SOURCE</literal>, or
288 <literal>WHEN NOT MATCHED [BY TARGET]</literal>.
289 Note that the <acronym>SQL</acronym> standard only defines
290 <literal>WHEN MATCHED</literal> and <literal>WHEN NOT MATCHED</literal>
291 (which is defined to mean no matching target row).
292 <literal>WHEN NOT MATCHED BY SOURCE</literal> is an extension to the
293 <acronym>SQL</acronym> standard, as is the option to append
294 <literal>BY TARGET</literal> to <literal>WHEN NOT MATCHED</literal>, to
295 make its meaning more explicit.
296 </para>
297 <para>
298 If the <literal>WHEN</literal> clause specifies <literal>WHEN MATCHED</literal>
299 and the candidate change row matches a row in the
300 <replaceable class="parameter">data_source</replaceable> to a row in the
301 target table, the <literal>WHEN</literal> clause is executed if the
302 <replaceable class="parameter">condition</replaceable> is
303 absent or it evaluates to <literal>true</literal>.
304 </para>
305 <para>
306 If the <literal>WHEN</literal> clause specifies
307 <literal>WHEN NOT MATCHED BY SOURCE</literal> and the candidate change
308 row represents a row in the target table that does not match a row in the
309 <replaceable class="parameter">data_source</replaceable>, the
310 <literal>WHEN</literal> clause is executed if the
311 <replaceable class="parameter">condition</replaceable> is
312 absent or it evaluates to <literal>true</literal>.
313 </para>
314 <para>
315 If the <literal>WHEN</literal> clause specifies
316 <literal>WHEN NOT MATCHED [BY TARGET]</literal> and the candidate change
317 row represents a row in the
318 <replaceable class="parameter">data_source</replaceable> that does not
319 match a row in the target table,
320 the <literal>WHEN</literal> clause is executed if the
321 <replaceable class="parameter">condition</replaceable> is
322 absent or it evaluates to <literal>true</literal>.
323 </para>
324 </listitem>
325 </varlistentry>
326
327 <varlistentry>
328 <term><replaceable class="parameter">condition</replaceable></term>
329 <listitem>
330 <para>
331 An expression that returns a value of type <type>boolean</type>.
332 If this expression for a <literal>WHEN</literal> clause
333 returns <literal>true</literal>, then the action for that clause
334 is executed for that row.
335 </para>
336 <para>
337 A condition on a <literal>WHEN MATCHED</literal> clause can refer to columns
338 in both the source and the target relations. A condition on a
339 <literal>WHEN NOT MATCHED BY SOURCE</literal> clause can only refer to
340 columns from the target relation, since by definition there is no matching
341 source row. A condition on a <literal>WHEN NOT MATCHED [BY TARGET]</literal>
342 clause can only refer to columns from
343 the source relation, since by definition there is no matching target row.
344 Only the system attributes from the target table are accessible.
345 </para>
346 </listitem>
347 </varlistentry>
348
349 <varlistentry>
350 <term><replaceable class="parameter">merge_insert</replaceable></term>
351 <listitem>
352 <para>
353 The specification of an <literal>INSERT</literal> action that inserts
354 one row into the target table.
355 The target column names can be listed in any order. If no list of
356 column names is given at all, the default is all the columns of the
357 table in their declared order.
358 </para>
359 <para>
360 Each column not present in the explicit or implicit column list will be
361 filled with a default value, either its declared default value
362 or null if there is none.
363 </para>
364 <para>
365 If the target table
366 is a partitioned table, each row is routed to the appropriate partition
367 and inserted into it.
368 If the target table
369 is a partition, an error will occur if any input row violates the
370 partition constraint.
371 </para>
372 <para>
373 Column names may not be specified more than once.
374 <command>INSERT</command> actions cannot contain sub-selects.
375 </para>
376 <para>
377 Only one <literal>VALUES</literal> clause can be specified.
378 The <literal>VALUES</literal> clause can only refer to columns from
379 the source relation, since by definition there is no matching target row.
380 </para>
381 </listitem>
382 </varlistentry>
383
384 <varlistentry>
385 <term><replaceable class="parameter">merge_update</replaceable></term>
386 <listitem>
387 <para>
388 The specification of an <literal>UPDATE</literal> action that updates
389 the current row of the target table.
390 Column names may not be specified more than once.
391 </para>
392 <para>
393 Neither a table name nor a <literal>WHERE</literal> clause are allowed.
394 </para>
395 </listitem>
396 </varlistentry>
397
398 <varlistentry>
399 <term><replaceable class="parameter">merge_delete</replaceable></term>
400 <listitem>
401 <para>
402 Specifies a <literal>DELETE</literal> action that deletes the current row
403 of the target table.
404 Do not include the table name or any other clauses, as you would normally
405 do with a <xref linkend="sql-delete"/> command.
406 </para>
407 </listitem>
408 </varlistentry>
409
410 <varlistentry>
411 <term><replaceable class="parameter">column_name</replaceable></term>
412 <listitem>
413 <para>
414 The name of a column in the target table. The column name
415 can be qualified with a subfield name or array subscript, if
416 needed. (Inserting into only some fields of a composite
417 column leaves the other fields null.)
418 Do not include the table's name in the specification
419 of a target column.
420 </para>
421 </listitem>
422 </varlistentry>
423
424 <varlistentry>
425 <term><literal>OVERRIDING SYSTEM VALUE</literal></term>
426 <listitem>
427 <para>
428 Without this clause, it is an error to specify an explicit value
429 (other than <literal>DEFAULT</literal>) for an identity column defined
430 as <literal>GENERATED ALWAYS</literal>. This clause overrides that
431 restriction.
432 </para>
433 </listitem>
434 </varlistentry>
435
436 <varlistentry>
437 <term><literal>OVERRIDING USER VALUE</literal></term>
438 <listitem>
439 <para>
440 If this clause is specified, then any values supplied for identity
441 columns defined as <literal>GENERATED BY DEFAULT</literal> are ignored
442 and the default sequence-generated values are applied.
443 </para>
444 </listitem>
445 </varlistentry>
446
447 <varlistentry>
448 <term><literal>DEFAULT VALUES</literal></term>
449 <listitem>
450 <para>
451 All columns will be filled with their default values.
452 (An <literal>OVERRIDING</literal> clause is not permitted in this
453 form.)
454 </para>
455 </listitem>
456 </varlistentry>
457
458 <varlistentry>
459 <term><replaceable class="parameter">expression</replaceable></term>
460 <listitem>
461 <para>
462 An expression to assign to the column. If used in a
463 <literal>WHEN MATCHED</literal> clause, the expression can use values
464 from the original row in the target table, and values from the
465 <replaceable class="parameter">data_source</replaceable> row.
466 If used in a <literal>WHEN NOT MATCHED BY SOURCE</literal> clause, the
467 expression can only use values from the original row in the target table.
468 If used in a <literal>WHEN NOT MATCHED [BY TARGET]</literal> clause, the
469 expression can only use values from the
470 <replaceable class="parameter">data_source</replaceable> row.
471 </para>
472 </listitem>
473 </varlistentry>
474
475 <varlistentry>
476 <term><literal>DEFAULT</literal></term>
477 <listitem>
478 <para>
479 Set the column to its default value (which will be <literal>NULL</literal>
480 if no specific default expression has been assigned to it).
481 </para>
482 </listitem>
483 </varlistentry>
484
485 <varlistentry>
486 <term><replaceable class="parameter">sub-SELECT</replaceable></term>
487 <listitem>
488 <para>
489 A <literal>SELECT</literal> sub-query that produces as many output columns
490 as are listed in the parenthesized column list preceding it. The
491 sub-query must yield no more than one row when executed. If it
492 yields one row, its column values are assigned to the target columns;
493 if it yields no rows, NULL values are assigned to the target columns.
494 If used in a <literal>WHEN MATCHED</literal> clause, the sub-query can
495 refer to values from the original row in the target table, and values
496 from the <replaceable class="parameter">data_source</replaceable> row.
497 If used in a <literal>WHEN NOT MATCHED BY SOURCE</literal> clause, the
498 sub-query can only refer to values from the original row in the target
499 table.
500 </para>
501 </listitem>
502 </varlistentry>
503
504 <varlistentry>
505 <term><replaceable class="parameter">output_alias</replaceable></term>
506 <listitem>
507 <para>
508 An optional substitute name for <literal>OLD</literal> or
509 <literal>NEW</literal> rows in the <literal>RETURNING</literal> list.
510 </para>
511 <para>
512 By default, old values from the target table can be returned by writing
513 <literal>OLD.<replaceable class="parameter">column_name</replaceable></literal>
514 or <literal>OLD.*</literal>, and new values can be returned by writing
515 <literal>NEW.<replaceable class="parameter">column_name</replaceable></literal>
516 or <literal>NEW.*</literal>. When an alias is provided, these names are
517 hidden and the old or new rows must be referred to using the alias.
518 For example <literal>RETURNING WITH (OLD AS o, NEW AS n) o.*, n.*</literal>.
519 </para>
520 </listitem>
521 </varlistentry>
522
523 <varlistentry>
524 <term><replaceable class="parameter">output_expression</replaceable></term>
525 <listitem>
526 <para>
527 An expression to be computed and returned by the <command>MERGE</command>
528 command after each row is changed (whether inserted, updated, or deleted).
529 The expression can use any columns of the source or target tables, or the
530 <link linkend="merge-action"><function>merge_action()</function></link>
531 function to return additional information about the action executed.
532 </para>
533 <para>
534 Writing <literal>*</literal> will return all columns from the source
535 table, followed by all columns from the target table. Often this will
536 lead to a lot of duplication, since it is common for the source and
537 target tables to have a lot of the same columns. This can be avoided by
538 qualifying the <literal>*</literal> with the name or alias of the source
539 or target table.
540 </para>
541 <para>
542 A column name or <literal>*</literal> may also be qualified using
543 <literal>OLD</literal> or <literal>NEW</literal>, or the corresponding
544 <replaceable class="parameter">output_alias</replaceable> for
545 <literal>OLD</literal> or <literal>NEW</literal>, to cause old or new
546 values from the target table to be returned. An unqualified column
547 name from the target table, or a column name or <literal>*</literal>
548 qualified using the target table name or alias will return new values
549 for <literal>INSERT</literal> and <literal>UPDATE</literal> actions, and
550 old values for <literal>DELETE</literal> actions.
551 </para>
552 </listitem>
553 </varlistentry>
554
555 <varlistentry>
556 <term><replaceable class="parameter">output_name</replaceable></term>
557 <listitem>
558 <para>
559 A name to use for a returned column.
560 </para>
561 </listitem>
562 </varlistentry>
563
564 </variablelist>
565 </refsect1>
566
567 <refsect1>
568 <title>Outputs</title>
569
570 <para>
571 On successful completion, a <command>MERGE</command> command returns a command
572 tag of the form
573 <screen>
574 MERGE <replaceable class="parameter">total_count</replaceable>
575 </screen>
576 The <replaceable class="parameter">total_count</replaceable> is the total
577 number of rows changed (whether inserted, updated, or deleted).
578 If <replaceable class="parameter">total_count</replaceable> is 0, no rows
579 were changed in any way.
580 </para>
581
582 <para>
583 If the <command>MERGE</command> command contains a <literal>RETURNING</literal>
584 clause, the result will be similar to that of a <command>SELECT</command>
585 statement containing the columns and values defined in the
586 <literal>RETURNING</literal> list, computed over the row(s) inserted, updated,
587 or deleted by the command.
588 </para>
589 </refsect1>
590
591 <refsect1>
592 <title>Notes</title>
593
594 <para>
595 The following steps take place during the execution of
596 <command>MERGE</command>.
597 <orderedlist>
598 <listitem>
599 <para>
600 Perform any <literal>BEFORE STATEMENT</literal> triggers for all
601 actions specified, whether or not their <literal>WHEN</literal>
602 clauses match.
603 </para>
604 </listitem>
605 <listitem>
606 <para>
607 Perform a join from source to target table.
608 The resulting query will be optimized normally and will produce
609 a set of candidate change rows. For each candidate change row,
610 <orderedlist>
611 <listitem>
612 <para>
613 Evaluate whether each row is <literal>MATCHED</literal>,
614 <literal>NOT MATCHED BY SOURCE</literal>, or
615 <literal>NOT MATCHED [BY TARGET]</literal>.
616 </para>
617 </listitem>
618 <listitem>
619 <para>
620 Test each <literal>WHEN</literal> condition in the order
621 specified until one returns true.
622 </para>
623 </listitem>
624 <listitem>
625 <para>
626 When a condition returns true, perform the following actions:
627 <orderedlist>
628 <listitem>
629 <para>
630 Perform any <literal>BEFORE ROW</literal> triggers that fire
631 for the action's event type.
632 </para>
633 </listitem>
634 <listitem>
635 <para>
636 Perform the specified action, invoking any check constraints on the
637 target table.
638 </para>
639 </listitem>
640 <listitem>
641 <para>
642 Perform any <literal>AFTER ROW</literal> triggers that fire for
643 the action's event type.
644 </para>
645 </listitem>
646 </orderedlist>
647 If the target relation is a view with <literal>INSTEAD OF ROW</literal>
648 triggers for the action's event type, they are used to perform the
649 action instead.
650 </para>
651 </listitem>
652 </orderedlist></para>
653 </listitem>
654 <listitem>
655 <para>
656 Perform any <literal>AFTER STATEMENT</literal> triggers for actions
657 specified, whether or not they actually occur. This is similar to the
658 behavior of an <command>UPDATE</command> statement that modifies no rows.
659 </para>
660 </listitem>
661 </orderedlist>
662 In summary, statement triggers for an event type (say,
663 <command>INSERT</command>) will be fired whenever we
664 <emphasis>specify</emphasis> an action of that kind.
665 In contrast, row-level triggers will fire only for the specific event type
666 being <emphasis>executed</emphasis>.
667 So a <command>MERGE</command> command might fire statement triggers for both
668 <command>UPDATE</command> and <command>INSERT</command>, even though only
669 <command>UPDATE</command> row triggers were fired.
670 </para>
671
672 <para>
673 You should ensure that the join produces at most one candidate change row
674 for each target row. In other words, a target row shouldn't join to more
675 than one data source row. If it does, then only one of the candidate change
676 rows will be used to modify the target row; later attempts to modify the
677 row will cause an error.
678 This can also occur if row triggers make changes to the target table
679 and the rows so modified are then subsequently also modified by
680 <command>MERGE</command>.
681 If the repeated action is an <command>INSERT</command>, this will
682 cause a uniqueness violation, while a repeated <command>UPDATE</command>
683 or <command>DELETE</command> will cause a cardinality violation; the
684 latter behavior is required by the <acronym>SQL</acronym> standard.
685 This differs from historical <productname>PostgreSQL</productname>
686 behavior of joins in <command>UPDATE</command> and
687 <command>DELETE</command> statements where second and subsequent
688 attempts to modify the same row are simply ignored.
689 </para>
690
691 <para>
692 If a <literal>WHEN</literal> clause omits an <literal>AND</literal>
693 sub-clause, it becomes the final reachable clause of that
694 kind (<literal>MATCHED</literal>, <literal>NOT MATCHED BY SOURCE</literal>,
695 or <literal>NOT MATCHED [BY TARGET]</literal>).
696 If a later <literal>WHEN</literal> clause of that kind
697 is specified it would be provably unreachable and an error is raised.
698 If no final reachable clause is specified of either kind, it is
699 possible that no action will be taken for a candidate change row.
700 </para>
701
702 <para>
703 The order in which rows are generated from the data source is
704 indeterminate by default.
705 A <replaceable class="parameter">source_query</replaceable> can be
706 used to specify a consistent ordering, if required, which might be
707 needed to avoid deadlocks between concurrent transactions.
708 </para>
709
710 <para>
711 When <command>MERGE</command> is run concurrently with other commands
712 that modify the target table, the usual transaction isolation rules
713 apply; see <xref linkend="transaction-iso"/> for an explanation
714 on the behavior at each isolation level.
715 You may also wish to consider using <command>INSERT ... ON CONFLICT</command>
716 as an alternative statement which offers the ability to run an
717 <command>UPDATE</command> if a concurrent <command>INSERT</command>
718 occurs. There are a variety of differences and restrictions between
719 the two statement types and they are not interchangeable.
720 </para>
721 </refsect1>
722
723 <refsect1>
724 <title>Examples</title>
725
726 <para>
727 Perform maintenance on <literal>customer_accounts</literal> based
728 upon new <literal>recent_transactions</literal>.
729
730 <programlisting>
731 MERGE INTO customer_account ca
732 USING recent_transactions t
733 ON t.customer_id = ca.customer_id
734 WHEN MATCHED THEN
735 UPDATE SET balance = balance + transaction_value
736 WHEN NOT MATCHED THEN
737 INSERT (customer_id, balance)
738 VALUES (t.customer_id, t.transaction_value);
739 </programlisting>
740 </para>
741
742 <para>
743 Attempt to insert a new stock item along with the quantity of stock. If
744 the item already exists, instead update the stock count of the existing
745 item. Don't allow entries that have zero stock. Return details of all
746 changes made.
747 <programlisting>
748 MERGE INTO wines w
749 USING wine_stock_changes s
750 ON s.winename = w.winename
751 WHEN NOT MATCHED AND s.stock_delta > 0 THEN
752 INSERT VALUES(s.winename, s.stock_delta)
753 WHEN MATCHED AND w.stock + s.stock_delta > 0 THEN
754 UPDATE SET stock = w.stock + s.stock_delta
755 WHEN MATCHED THEN
756 DELETE
757 RETURNING merge_action(), w.winename, old.stock AS old_stock, new.stock AS new_stock;
758 </programlisting>
759
760 The <literal>wine_stock_changes</literal> table might be, for example, a
761 temporary table recently loaded into the database.
762 </para>
763
764 <para>
765 Update <literal>wines</literal> based on a replacement wine list, inserting
766 rows for any new stock, updating modified stock entries, and deleting any
767 wines not present in the new list.
768 <programlisting>
769 MERGE INTO wines w
770 USING new_wine_list s
771 ON s.winename = w.winename
772 WHEN NOT MATCHED BY TARGET THEN
773 INSERT VALUES(s.winename, s.stock)
774 WHEN MATCHED AND w.stock != s.stock THEN
775 UPDATE SET stock = s.stock
776 WHEN NOT MATCHED BY SOURCE THEN
777 DELETE;
778 </programlisting>
779 </para>
780
781 </refsect1>
782
783 <refsect1>
784 <title>Compatibility</title>
785 <para>
786 This command conforms to the <acronym>SQL</acronym> standard.
787 </para>
788 <para>
789 The <literal>WITH</literal> clause, <literal>BY SOURCE</literal> and
790 <literal>BY TARGET</literal> qualifiers to
791 <literal>WHEN NOT MATCHED</literal>, <literal>DO NOTHING</literal> action,
792 and <literal>RETURNING</literal> clause are extensions to the
793 <acronym>SQL</acronym> standard.
794 </para>
795 </refsect1>
796 </refentry>