Home • Explore • Blog • CI
☰



postgresql
11th chunk of `doc/src/sgml/ref/insert.sgml`
460e2bbe2333bda75bd4cd8e1fee8e5a09148f0669b69790000000010000097b
 account for Acme Corporation, and record the whole updated row
   along with current time in a log table:
<programlisting>
WITH upd AS (
  UPDATE employees SET sales_count = sales_count + 1 WHERE id =
    (SELECT sales_person FROM accounts WHERE name = 'Acme Corporation')
    RETURNING *
)
INSERT INTO employees_log SELECT *, current_timestamp FROM upd;
</programlisting>
  </para>
  <para>
   Insert or update new distributors as appropriate.  Assumes a unique
   index has been defined that constrains values appearing in the
   <literal>did</literal> column.  Note that the special
   <varname>excluded</varname> table is used to reference values originally
   proposed for insertion:
<programlisting>
INSERT INTO distributors (did, dname)
    VALUES (5, 'Gizmo Transglobal'), (6, 'Associated Computing, Inc')
    ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname;
</programlisting>
  </para>
  <para>
   Insert or update new distributors as above, returning information
   about any existing values that were updated, together with the new data
   inserted.  Note that the returned values for <literal>old_did</literal>
   and <literal>old_dname</literal> will be <literal>NULL</literal> for
   non-conflicting rows:
<programlisting>
INSERT INTO distributors (did, dname)
    VALUES (5, 'Gizmo Transglobal'), (6, 'Associated Computing, Inc')
    ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname
    RETURNING 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
Title: INSERT Command Examples with RETURNING and ON CONFLICT Clauses
Summary
This section demonstrates advanced examples of using the `INSERT` command. It covers using a Common Table Expression (CTE) with `UPDATE` and `RETURNING` to update a table and insert the updated row into a log table. It also shows how to use `INSERT ... ON CONFLICT DO UPDATE` to insert new rows or update existing ones based on conflicts in a unique column, leveraging the `EXCLUDED` table to reference the proposed values. Additionally, it illustrates the use of `RETURNING` with `ON CONFLICT DO UPDATE` to retrieve information about both old and new values, including handling cases where rows are inserted without conflicts. Finally, it demonstrates using `ON CONFLICT DO NOTHING` to skip inserting rows that would cause a conflict.