Home • Explore • Blog • CI
☰



postgresql
7th chunk of `doc/src/sgml/ref/insert.sgml`
4f40f78407dd99fed6476a5bfe9e1e99c0887f026bd176cc0000000100000fb9
 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
        UPDATE</literal> clause specifying the exact details of the
        <literal>UPDATE</literal> action to be performed in case of a
        conflict.  The <literal>SET</literal> and
        <literal>WHERE</literal> clauses in <literal>ON CONFLICT DO
        UPDATE</literal> have access to the existing row using the
        table's name (or an alias), and to the row proposed for insertion
        using the special <varname>excluded</varname> table.
        <literal>SELECT</literal> privilege is required on any column in the
        target table where corresponding <varname>excluded</varname>
        columns are read.
       </para>
       <para>
        Note that the effects of all per-row <literal>BEFORE
        INSERT</literal> triggers are reflected in
        <varname>excluded</varname> values, since those effects may
        have contributed to the row being excluded from insertion.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><replaceable class="parameter">index_column_name</replaceable></term>
      <listitem>
       <para>
        The name of a <replaceable
        class="parameter">table_name</replaceable> column.  Used to
        infer arbiter indexes.  Follows <command>CREATE
        INDEX</command> format.  <literal>SELECT</literal> privilege on
        <replaceable class="parameter">index_column_name</replaceable>
        is required.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><replaceable class="parameter">index_expression</replaceable></term>
      <listitem>
       <para>
        Similar to <replaceable
        class="parameter">index_column_name</replaceable>, but used to
        infer expressions on <replaceable
        class="parameter">table_name</replaceable> columns appearing
        within index definitions (not simple columns).  Follows
        <command>CREATE INDEX</command> format.  <literal>SELECT</literal>
        privilege on any column appearing within <replaceable
        class="parameter">index_expression</replaceable> is required.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><replaceable class="parameter">collation</replaceable></term>
      <listitem>
       <para>
        When specified, mandates that corresponding <replaceable
        class="parameter">index_column_name</replaceable> or
        <replaceable class="parameter">index_expression</replaceable>
        use a particular collation in order to be matched during
        inference.  Typically this is omitted, as collations usually
        do not affect whether or not a constraint violation occurs.
        Follows <command>CREATE INDEX</command> format.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><replaceable class="parameter">opclass</replaceable></term>
      <listitem>
       <para>
        When specified, mandates that corresponding <replaceable
        class="parameter">index_column_name</replaceable>
Title: Detailed Explanation of ON CONFLICT Clause Parameters
Summary
This section delves into the specifics of the parameters associated with the `ON CONFLICT` clause in SQL. It describes `conflict_target` as the specification for which constraints or indexes to consider for alternative actions, `conflict_action` as determining the alternative action (either `DO NOTHING` or an `UPDATE` clause with access to the existing row and the `excluded` table for the proposed row), `index_column_name` and `index_expression` as parameters used to infer arbiter indexes, `collation` as a way to mandate a particular collation for matching during inference, and `opclass`.