Home • Explore • Blog • CI
☰



postgresql
5th chunk of `doc/src/sgml/ref/insert.sgml`
5d0172110410d64d75008243bc505bf92e870ec220acbffd0000000100000fc3
 identity column will be filled with a new value generated by the
        associated sequence.  For a generated column, specifying this is
        permitted but merely specifies the normal behavior of computing the
        column from its generation expression.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><replaceable class="parameter">query</replaceable></term>
      <listitem>
       <para>
        A query (<command>SELECT</command> statement) that supplies the
        rows to be inserted.  Refer to the
        <xref linkend="sql-select"/>
        statement for a description of the syntax.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><replaceable class="parameter">output_alias</replaceable></term>
      <listitem>
       <para>
        An optional substitute name for <literal>OLD</literal> or
        <literal>NEW</literal> rows in the <literal>RETURNING</literal> list.
       </para>

       <para>
        By default, old values from the target table can be returned by writing
        <literal>OLD.<replaceable class="parameter">column_name</replaceable></literal>
        or <literal>OLD.*</literal>, and new values can be returned by writing
        <literal>NEW.<replaceable class="parameter">column_name</replaceable></literal>
        or <literal>NEW.*</literal>.  When an alias is provided, these names are
        hidden and the old or new rows must be referred to using the alias.
        For example <literal>RETURNING WITH (OLD AS o, NEW AS n) o.*, n.*</literal>.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><replaceable class="parameter">output_expression</replaceable></term>
      <listitem>
       <para>
        An expression to be computed and returned by the
        <command>INSERT</command> command after each row is inserted or
        updated. The expression can use any column names of the table
        named by <replaceable
        class="parameter">table_name</replaceable>.  Write
        <literal>*</literal> to return all columns of the inserted or updated
        row(s).
       </para>

       <para>
        A column name or <literal>*</literal> may be qualified using
        <literal>OLD</literal> or <literal>NEW</literal>, or the corresponding
        <replaceable class="parameter">output_alias</replaceable> for
        <literal>OLD</literal> or <literal>NEW</literal>, to cause old or new
        values to be returned.  An unqualified column name, or
        <literal>*</literal>, or a column name or <literal>*</literal>
        qualified using the target table name or alias will return new values.
       </para>

       <para>
        For a simple <command>INSERT</command>, all old values will be
        <literal>NULL</literal>.  However, for an <command>INSERT</command>
        with an <literal>ON CONFLICT DO UPDATE</literal> clause, the old
        values may be non-<literal>NULL</literal>.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><replaceable class="parameter">output_name</replaceable></term>
      <listitem>
       <para>
        A name to use for a returned column.
       </para>
      </listitem>
     </varlistentry>
    </variablelist>
  </refsect2>

  <refsect2 id="sql-on-conflict" xreflabel="ON CONFLICT Clause">
   <title><literal>ON CONFLICT</literal> Clause</title>
   <indexterm zone="sql-insert">
    <primary>UPSERT</primary>
   </indexterm>
   <indexterm zone="sql-insert">
    <primary>ON CONFLICT</primary>
   </indexterm>
   <para>
    The optional <literal>ON CONFLICT</literal> clause specifies an
    alternative action to raising a unique violation or exclusion
    constraint violation error.  For each individual row proposed for
    insertion, either the insertion proceeds, or, if an
    <emphasis>arbiter</emphasis> constraint or index specified by
    <parameter>conflict_target</parameter> is violated, the
    alternative <parameter>conflict_action</parameter>
Title: INSERT Parameters: Queries, Output Aliases, and the ON CONFLICT Clause
Summary
This section continues detailing parameters for the INSERT command. It covers the use of a query (SELECT statement) to provide the rows for insertion, and the optional output alias in the RETURNING list, which allows renaming OLD and NEW rows. It explains how the alias hides the default OLD and NEW keywords. The section further describes output expressions for computing and returning values after insertion or update, including the use of OLD and NEW keywords and output aliases. Finally, it introduces the ON CONFLICT clause, which specifies an alternative action to take in case of a unique or exclusion constraint violation during insertion.