Home Explore Blog CI



postgresql

6th chunk of `doc/src/sgml/ref/merge.sgml`
4cd5c199d5750b2f90d2bd0c06916beb19d5040e79b595dd0000000100000fa5
 <literal>true</literal>.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><replaceable class="parameter">condition</replaceable></term>
    <listitem>
     <para>
      An expression that returns a value of type <type>boolean</type>.
      If this expression for a <literal>WHEN</literal> clause
      returns <literal>true</literal>, then the action for that clause
      is executed for that row.
     </para>
     <para>
      A condition on a <literal>WHEN MATCHED</literal> clause can refer to columns
      in both the source and the target relations. A condition on a
      <literal>WHEN NOT MATCHED BY SOURCE</literal> clause can only refer to
      columns from the target relation, since by definition there is no matching
      source row. A condition on a <literal>WHEN NOT MATCHED [BY TARGET]</literal>
      clause can only refer to columns from
      the source relation, since by definition there is no matching target row.
      Only the system attributes from the target table are accessible.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><replaceable class="parameter">merge_insert</replaceable></term>
    <listitem>
     <para>
      The specification of an <literal>INSERT</literal> 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.
     </para>
     <para>
      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.
     </para>
     <para>
      If the target table
      is a partitioned table, each row is routed to the appropriate partition
      and inserted into it.
      If the target table
      is a partition, an error will occur if any input row violates the
      partition constraint.
     </para>
     <para>
      Column names may not be specified more than once.
      <command>INSERT</command> actions cannot contain sub-selects.
     </para>
     <para>
      Only one <literal>VALUES</literal> clause can be specified.
      The <literal>VALUES</literal> clause can only refer to columns from
      the source relation, since by definition there is no matching target row.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><replaceable class="parameter">merge_update</replaceable></term>
    <listitem>
     <para>
      The specification of an <literal>UPDATE</literal> action that updates
      the current row of the target table.
      Column names may not be specified more than once.
     </para>
     <para>
      Neither a table name nor a <literal>WHERE</literal> clause are allowed.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><replaceable class="parameter">merge_delete</replaceable></term>
    <listitem>
     <para>
      Specifies a <literal>DELETE</literal> action that deletes the current row
      of the target table.
      Do not include the table name or any other clauses, as you would normally
      do with a <xref linkend="sql-delete"/> command.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><replaceable class="parameter">column_name</replaceable></term>
    <listitem>
     <para>
      The name of a column in the target table.  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.)
      Do not include the table's name in the specification
      of a target column.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><literal>OVERRIDING SYSTEM VALUE</literal></term>
    <listitem>
     <para>
      Without this clause, it is an error to specify an explicit value
      (other than <literal>DEFAULT</literal>)

Title: MERGE Statement Components: Conditions, INSERT, UPDATE, and DELETE Actions
Summary
This section details the key components of the MERGE statement. It clarifies the role of the boolean 'condition' in 'WHEN' clauses and how it determines action execution, including which table columns can be accessed. It then explains the 'merge_insert' action, covering column specifications, default values, partition handling, and restrictions on the 'VALUES' clause. Finally, it describes 'merge_update' and 'merge_delete' actions, emphasizing the constraints on table and clause inclusion, as well as detailing column names and the 'OVERRIDING SYSTEM VALUE' clause.