ON CONFLICT: Insert or Update

What happens if you insert a row with an id that already exists? The id column is the primary key, so PostgreSQL stops with an error:

INSERT INTO employees
VALUES (1, 'Alice', 29, 'HR', 'female');

Output:

ERROR:  duplicate key value violates unique constraint "employees_pkey"
DETAIL:  Key (id)=(1) already exists.

Sometimes you want something different: "insert this row, but if it already exists, update it instead". This is called an upsert (update + insert). In PostgreSQL, you write it with ON CONFLICT.


Syntax

INSERT INTO table_name VALUES (...)
ON CONFLICT (column) DO NOTHING;

INSERT INTO table_name VALUES (...)
ON CONFLICT (column) DO UPDATE SET column1 = EXCLUDED.column1;

(column) is the column that must be unique, usually the primary key.


Example 1: Skip Rows That Already Exist

INSERT INTO employees
VALUES (1, 'Alice', 29, 'HR', 'female')
ON CONFLICT (id) DO NOTHING;

Output:

INSERT 0 0

There is no error. INSERT 0 0 means no row was added, because id 1 already exists.


Example 2: Update Rows That Already Exist

INSERT INTO employees VALUES
(1, 'Alice', 29, 'HR', 'female'),
(21, 'Nora', 27, 'Finance', 'female')
ON CONFLICT (id) DO UPDATE SET age = EXCLUDED.age;

SELECT * FROM employees WHERE id IN (1, 21);

Output:

INSERT 0 2
 id | name  | age | department | gender 
----+-------+-----+------------+--------
  1 | Alice |  29 | HR         | female
 21 | Nora  |  27 | Finance    | female
(2 rows)

Id 1 already existed, so Alice's age was updated to 29. Id 21 was new, so Nora was inserted. INSERT 0 2 counts both rows.

EXCLUDED means "the row you tried to insert". So SET age = EXCLUDED.age copies the new age into the existing row.


Tip

Use an upsert when you don't know if a row exists yet, for example when you copy data from another system. One statement handles both cases.

In the exercise, insert two employees, and update the one that already exists.