Home • Explore • Blog • CI
☰



postgresql
8th chunk of `doc/src/sgml/ref/insert.sgml`
9103625c0e3033f5886b3b367bf906c980d163dfa61d4d910000000100000fa8
    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> or
        <replaceable class="parameter">index_expression</replaceable>
        use particular operator class in order to be matched during
        inference.  Typically this is omitted,  as the
        <emphasis>equality</emphasis> semantics are often equivalent
        across a type's operator classes anyway, or because it's
        sufficient to trust that the defined unique indexes have the
        pertinent definition of equality.  Follows <command>CREATE
        INDEX</command> format.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><replaceable class="parameter">index_predicate</replaceable></term>
      <listitem>
       <para>
        Used to allow inference of partial unique indexes.  Any
        indexes that satisfy the predicate (which need not actually be
        partial indexes) can be inferred.  Follows <command>CREATE
        INDEX</command> format.  <literal>SELECT</literal> privilege on any
        column appearing within <replaceable
        class="parameter">index_predicate</replaceable> is required.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><replaceable class="parameter">constraint_name</replaceable></term>
      <listitem>
       <para>
        Explicitly specifies an arbiter
        <emphasis>constraint</emphasis> by name, rather than inferring
        a constraint or index.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><replaceable class="parameter">condition</replaceable></term>
      <listitem>
       <para>
        An expression that returns a value of type
        <type>boolean</type>.  Only rows for which this expression
        returns <literal>true</literal> will be updated, although all
        rows will be locked when the <literal>ON CONFLICT DO UPDATE</literal>
        action is taken.  Note that
        <replaceable>condition</replaceable> is evaluated last, after
        a conflict has been identified as a candidate to update.
       </para>
      </listitem>
     </varlistentry>
    </variablelist>
   <para>
    Note that exclusion constraints are not supported as arbiters with
    <literal>ON CONFLICT DO UPDATE</literal>. In all cases, only
    <literal>NOT DEFERRABLE</literal> constraints and unique indexes
    are supported as arbiters.
   </para>

   <para>
    <command>INSERT</command> with an <literal>ON CONFLICT DO UPDATE</literal>
    clause is a <quote>deterministic</quote> statement.  This means
    that the command will not be allowed to affect any single existing
    row more than once; a cardinality violation error will be raised
    when this situation arises.  Rows proposed for insertion should
    not duplicate each other in terms of attributes constrained by an
    arbiter index or constraint.
   </para>

   <para>
    Note that it is currently not supported for the
    <literal>ON CONFLICT DO UPDATE</literal> clause of an
    <command>INSERT</command> applied to a partitioned
Title: Detailed Explanation of ON CONFLICT Clause Parameters (Continued)
Summary
This section continues the explanation of the `ON CONFLICT` clause parameters, covering `opclass` for mandating a specific operator class during inference, `index_predicate` for inferring partial unique indexes, `constraint_name` for explicitly specifying an arbiter constraint by name, and `condition` as a boolean expression to filter rows for updating during a conflict. It also notes the limitations, such as the exclusion of exclusion constraints as arbiters with `ON CONFLICT DO UPDATE` and the requirement for `NOT DEFERRABLE` constraints and unique indexes as arbiters. The section ends by describing `INSERT` with `ON CONFLICT DO UPDATE` as a deterministic statement which will raise a cardinality violation if an existing row is updated more than once.