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.