Home Explore Blog CI



postgresql

7th chunk of `doc/src/sgml/ref/merge.sgml`
fa487913e2d3085647293983b2e7881652f94cc530f9d6b60000000100000fb4
   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>) for an identity column defined
      as <literal>GENERATED ALWAYS</literal>.  This clause overrides that
      restriction.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><literal>OVERRIDING USER VALUE</literal></term>
    <listitem>
     <para>
      If this clause is specified, then any values supplied for identity
      columns defined as <literal>GENERATED BY DEFAULT</literal> are ignored
      and the default sequence-generated values are applied.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><literal>DEFAULT VALUES</literal></term>
    <listitem>
     <para>
      All columns will be filled with their default values.
      (An <literal>OVERRIDING</literal> clause is not permitted in this
      form.)
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><replaceable class="parameter">expression</replaceable></term>
    <listitem>
     <para>
      An expression to assign to the column.  If used in a
      <literal>WHEN MATCHED</literal> clause, the expression can use values
      from the original row in the target table, and values from the
      <replaceable class="parameter">data_source</replaceable> row.
      If used in a <literal>WHEN NOT MATCHED BY SOURCE</literal> clause, the
      expression can only use values from the original row in the target table.
      If used in a <literal>WHEN NOT MATCHED [BY TARGET]</literal> clause, the
      expression can only use values from the
      <replaceable class="parameter">data_source</replaceable> row.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><literal>DEFAULT</literal></term>
    <listitem>
     <para>
      Set the column to its default value (which will be <literal>NULL</literal>
      if no specific default expression has been assigned to it).
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><replaceable class="parameter">sub-SELECT</replaceable></term>
    <listitem>
     <para>
      A <literal>SELECT</literal> sub-query that produces as many output columns
      as are listed in the parenthesized column list preceding it.  The
      sub-query must yield no more than one row when executed.  If it
      yields one row, its column values are assigned to the target columns;
      if it yields no rows, NULL values are assigned to the target columns.
      If used in a <literal>WHEN MATCHED</literal> clause, the sub-query can
      refer to values from the original row in the target table, and values
      from the <replaceable class="parameter">data_source</replaceable> row.
      If used in a <literal>WHEN NOT MATCHED BY SOURCE</literal> clause, the
      sub-query can only refer to values from the original row in the target
      table.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><replaceable class="parameter">output_alias</replaceable></term>
    <listitem>
     <para>
      An optional substitute name for <literal>OLD</literal>

Title: MERGE Statement Details: Column Names, Overriding Values, and Expressions
Summary
This section elaborates on elements within the MERGE statement, including 'column_name' specifications, the function of 'OVERRIDING SYSTEM VALUE' and 'OVERRIDING USER VALUE' clauses for identity columns, and the 'DEFAULT VALUES' option. It further explains how 'expression' and 'DEFAULT' are used to assign values to columns, highlighting which table values can be referenced in different 'WHEN' clauses. Finally, it describes the use of 'sub-SELECT' queries for assigning values and introduces the optional 'output_alias'.