Home • Explore • Blog • CI
☰



postgresql
11th chunk of `doc/src/sgml/ref/merge.sgml`
444310076e7ddc31f257704acc1a035ec84c478f26348f220000000100000804
 the
   latter behavior is required by the <acronym>SQL</acronym> standard.
   This differs from historical <productname>PostgreSQL</productname>
   behavior of joins in <command>UPDATE</command> and
   <command>DELETE</command> statements where second and subsequent
   attempts to modify the same row are simply ignored.
  </para>

  <para>
   If a <literal>WHEN</literal> clause omits an <literal>AND</literal>
   sub-clause, it becomes the final reachable clause of that
   kind (<literal>MATCHED</literal>, <literal>NOT MATCHED BY SOURCE</literal>,
   or <literal>NOT MATCHED [BY TARGET]</literal>).
   If a later <literal>WHEN</literal> 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.
  </para>

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

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

 <refsect1>
  <title>Examples</title>

  <para>
   Perform maintenance on <literal>customer_accounts</literal> based
   upon new <literal>recent_transactions</literal>.

<programlisting>
MERGE INTO customer_account
Title: MERGE Statement Details: WHEN Clause, Row Order, Concurrency, and Alternatives
Summary
This section describes the handling of WHEN clauses within MERGE statements, including the behavior when an AND sub-clause is omitted. It notes the indeterminate order of rows from the data source, suggesting the use of a source query for consistent ordering. It also addresses concurrency considerations, highlighting transaction isolation rules and suggesting INSERT ... ON CONFLICT as a potential alternative with distinct characteristics. The historical behavior of UPDATE and DELETE statements in previous versions of PostgreSQL is also noted.