Home • Explore • Blog • CI
☰



postgresql
12th chunk of `doc/src/sgml/ref/insert.sgml`
a255d40b900a79936242c8892823f9f3b40fee1bdb33397c0000000100000d51
 old.did AS old_did, old.dname AS old_dname,
              new.did AS new_did, new.dname AS new_dname;
</programlisting>
  </para>
  <para>
   Insert a distributor, or do nothing for rows proposed for insertion
   when an existing, excluded row (a row with a matching constrained
   column or columns after before row insert triggers fire) exists.
   Example assumes a unique index has been defined that constrains
   values appearing in the <literal>did</literal> column:
<programlisting>
INSERT INTO distributors (did, dname) VALUES (7, 'Redline GmbH')
    ON CONFLICT (did) DO NOTHING;
</programlisting>
  </para>
  <para>
   Insert or update new distributors as appropriate.  Example assumes
   a unique index has been defined that constrains values appearing in
   the <literal>did</literal> column.  <literal>WHERE</literal> clause is
   used to limit the rows actually updated (any existing row not
   updated will still be locked, though):
<programlisting>
-- Don't update existing distributors based in a certain ZIP code
INSERT INTO distributors AS d (did, dname) VALUES (8, 'Anvil Distribution')
    ON CONFLICT (did) DO UPDATE
    SET dname = EXCLUDED.dname || ' (formerly ' || d.dname || ')'
    WHERE d.zipcode &lt;&gt; '21201';

-- Name a constraint directly in the statement (uses associated
-- index to arbitrate taking the DO NOTHING action)
INSERT INTO distributors (did, dname) VALUES (9, 'Antwerp Design')
    ON CONFLICT ON CONSTRAINT distributors_pkey DO NOTHING;
</programlisting>
  </para>
  <para>
   Insert new distributor if possible;  otherwise
   <literal>DO NOTHING</literal>.  Example assumes a unique index has been
   defined that constrains values appearing in the
   <literal>did</literal> column on a subset of rows where the
   <literal>is_active</literal> Boolean column evaluates to
   <literal>true</literal>:
<programlisting>
-- This statement could infer a partial unique index on "did"
-- with a predicate of "WHERE is_active", but it could also
-- just use a regular unique constraint on "did"
INSERT INTO distributors (did, dname) VALUES (10, 'Conrad International')
    ON CONFLICT (did) WHERE is_active DO NOTHING;
</programlisting></para>
 </refsect1>

 <refsect1>
  <title>Compatibility</title>

  <para>
   <command>INSERT</command> conforms to the SQL standard, except that
   the <literal>RETURNING</literal> clause is a
   <productname>PostgreSQL</productname> extension, as is the ability
   to use <literal>WITH</literal> with <command>INSERT</command>, and the ability to
   specify an alternative action with <literal>ON CONFLICT</literal>.
   Also, the case in
   which a column name list is omitted, but not all the columns are
   filled from the <literal>VALUES</literal> clause or <replaceable>query</replaceable>,
   is disallowed by the standard. If you prefer a more SQL standard
   conforming statement than <literal>ON CONFLICT</literal>, see
   <xref linkend="sql-merge"/>.
  </para>

  <para>
   The SQL standard specifies that <literal>OVERRIDING SYSTEM VALUE</literal>
   can only be specified if an identity column that is generated always
   exists.  PostgreSQL allows the clause in any case and ignores it if it is
   not applicable.
  </para>

  <para>
   Possible limitations of the <replaceable
   class="parameter">query</replaceable> clause are documented under
   <xref linkend="sql-select"/>.
  </para>
 </refsect1>
</refentry>
Title: More INSERT ON CONFLICT Examples and Compatibility
Summary
This section continues the examples of using `INSERT ... ON CONFLICT`, demonstrating how to use a `WHERE` clause to conditionally update existing rows. It also demonstrates how to name a constraint directly in the `ON CONFLICT` clause. Finally, it shows an example with a partial unique index. The section then discusses the compatibility of the `INSERT` command with the SQL standard, noting that the `RETURNING`, `WITH`, and `ON CONFLICT` clauses are PostgreSQL extensions. It also mentions that the SQL standard disallows omitting the column name list when not all columns are filled from the `VALUES` clause. It directs to the `MERGE` command for a more SQL standard conforming command than `ON CONFLICT`. The section also points out PostgreSQL's relaxed handling of `OVERRIDING SYSTEM VALUE` and refers to the `SELECT` command documentation for potential limitations of the query clause.