Home • Explore • Blog • CI
☰



postgresql
6th chunk of `doc/src/sgml/ref/insert.sgml`
6561a4241e7b6db0c00b30a8268bfeded7799c0f2797b2b00000000100000fa0
 </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> is taken.
    <literal>ON CONFLICT DO NOTHING</literal> simply avoids inserting
    a row as its alternative action.  <literal>ON CONFLICT DO
    UPDATE</literal> updates the existing row that conflicts with the
    row proposed for insertion as its alternative action.
   </para>

   <para>
    <parameter>conflict_target</parameter> can perform
    <emphasis>unique index inference</emphasis>.  When performing
    inference, it consists of one or more <replaceable
    class="parameter">index_column_name</replaceable> columns and/or
    <replaceable class="parameter">index_expression</replaceable>
    expressions, and an optional <replaceable class="parameter">index_predicate</replaceable>.  All <replaceable
    class="parameter">table_name</replaceable> unique indexes that,
    without regard to order, contain exactly the
    <parameter>conflict_target</parameter>-specified
    columns/expressions are inferred (chosen) as arbiter indexes.  If
    an <replaceable class="parameter">index_predicate</replaceable> is
    specified, it must, as a further requirement for inference,
    satisfy arbiter indexes.  Note that this means a non-partial
    unique index (a unique index without a predicate) will be inferred
    (and thus used by <literal>ON CONFLICT</literal>) if such an index
    satisfying every other criteria is available.  If an attempt at
    inference is unsuccessful, an error is raised.
   </para>

   <para>
    <literal>ON CONFLICT DO UPDATE</literal> guarantees an atomic
    <command>INSERT</command> or <command>UPDATE</command> outcome;
    provided there is no independent error, one of those two outcomes
    is guaranteed, even under high concurrency.  This is also known as
    <firstterm>UPSERT</firstterm> &mdash; <quote>UPDATE or
    INSERT</quote>.
   </para>

    <variablelist>
     <varlistentry>
      <term><replaceable class="parameter">conflict_target</replaceable></term>
      <listitem>
       <para>
        Specifies which conflicts <literal>ON CONFLICT</literal> takes
        the alternative action on by choosing <firstterm>arbiter
        indexes</firstterm>.  Either performs <emphasis>unique index
        inference</emphasis>, or names a constraint explicitly.  For
        <literal>ON CONFLICT DO NOTHING</literal>, it is optional to
        specify a <parameter>conflict_target</parameter>; when
        omitted, conflicts with all usable constraints (and unique
        indexes) are handled.  For <literal>ON CONFLICT DO
        UPDATE</literal>, a <parameter>conflict_target</parameter>
        <emphasis>must</emphasis> be provided.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><replaceable class="parameter">conflict_action</replaceable></term>
      <listitem>
       <para>
        <parameter>conflict_action</parameter> specifies an
        alternative <literal>ON CONFLICT</literal> action.  It can be
        either <literal>DO NOTHING</literal>, or a <literal>DO
Title: ON CONFLICT Clause Details: Actions, Target Specification, and Unique Index Inference
Summary
This section elaborates on the ON CONFLICT clause within the INSERT command. It explains that this clause provides an alternative action when a unique or exclusion constraint violation occurs, either avoiding the insertion (DO NOTHING) or updating the existing row (DO UPDATE). The section details unique index inference, where the system infers arbiter indexes based on the specified columns/expressions, and the requirement for the target to be specified with DO UPDATE. It emphasizes that ON CONFLICT DO UPDATE guarantees an atomic INSERT or UPDATE outcome (UPSERT). The section defines the conflict_target, which specifies the constraints or indexes to consider for alternative actions, and the conflict_action, which determines the alternative action to take (DO NOTHING or DO UPDATE).