Documentation Source Text

[ON CONFLICT|INSERT OR IGNORE].

<p>If the insert operation would cause the uniqueness constraint identified
by the conflict-target clause to fail, then the insert is omitted and
either the DO NOTHING or DO UPDATE operation is performed instead.
In the case of a multi-row insert, this decision is made separately
for each row of the insert.

<p>Column names in the expressions of a DO UPDATE refer to the original
unchanged value of the column, before the attempted INSERT.  To use the
value that would have been inserted had the constraint not failed,
add the special "excluded." table qualifier to the column name.

<p>Some examples will help illustrate the difference:

<blockquote><pre>
CREATE TABLE vocabulary(word TEXT PRIMARY KEY, count INT DEFAULT 1);
INSERT INTO vocabulary(word) VALUES('jovial')
  ON CONFLICT(word) DO UPDATE SET count=count+1;
</pre></blockquote>

<p>The upsert above inserts the new vocabulary word "jovial" if that
word is not already in the dictionary, or if it is already in the
dictionary, it increments the counter.  The "count+1" expression
could also be written as "vocabulary.count".  PostgreSQL requires the
second form, but SQLite accepts either.

<blockquote><pre>
CREATE TABLE phonebook(name TEXT PRIMARY KEY, phonenumber TEXT);
INSERT INTO phonebook(name,phonenumber) VALUES('Alice','704-555-1212')
  ON CONFLICT(name) DO UPDATE SET phonenumber=excluded.phonenumber;
</pre></blockquote>

<p>In the second example, the expression in the DO UPDATE clause is
of the form "excluded.phonenumber".  The "excluded." prefix causes the
"phonenumber" to refer to the value for phonenumber that would have been
inserted had there been no conflict.  Hence, the effect of the upsert
is to insert a phonenumber of Alice if none exists, or to overwrite
any prior phonenumber for Alice with the new one.

<p>Note that the DO UPDATE clause acts only on the single row
that experienced the constraint error during INSERT.  It is not
necessary to include a WHERE clause that restrictions the action
to that one row.  The only use for the WHERE clause at
the end of the DO UPDATE is to optionally change the DO UPDATE
into a no-op depending on the original and/or new values.
For example:

<blockquote><pre>
CREATE TABLE phonebook2(
  name TEXT PRIMARY KEY,
  phonenumber TEXT,
  validDate DATE
);
INSERT INTO phonebook2(name,phonenumber,validDate)
  VALUES('Alice','704-555-1212','2018-05-08')
  ON CONFLICT(name) DO UPDATE SET
    phonenumber=excluded.phonenumber,
    validDate=excluded.validDate
  WHERE excluded.validDate>phonebook2.validDate;
</pre></blockquote>

<p>In this last example, the phonebook2 entry is only
updated if the validDate for the newly inserted value is
newer than the entry already in the table.  If the table already
contains an entry with the same name and a current validDate,
then the WHERE clause causes the DO UPDATE to become a no-op.


<tcl>
##############################################################################
Section {ON CONFLICT clause} conflict {{conflict clause} {ON CONFLICT}}

RecursiveBubbleDiagram conflict-clause
</tcl>