PostgreSQL / Inserting and modifying data
How to use `INSERT ON CONFLICT` to upsert data in PostgreSQL
PostgreSQL lets you either add or modify a record within a table depending on whether the record already exists. This is commonly known as an "upsert" operation (a portmanteau of "insert" and "update").
The actual implementation within PostgreSQL uses the
INSERT command with a special
ON CONFLICT clause to specify what to do if the record already exists within the table. You can specify whether you want the record to be updated if it's found in the table already or silently skipped.
How to use the
INSERT...ON CONFLICT construct
The basic syntax for the insert or update operation looks like this:
INSERT INTO my_table (column1, column2)VALUES(value1, value2),(value3, value4),(value5, value6),(value7, value8)ON CONFLICT <target> <action>;
In this context, the
<target> specifies what conflict you want to define a policy for. This can be any of these:
- The name of a specific column or columns:
- The name of a unique constraint:
ON CONSTRAINT <constraint_name>
<action> item will define what PostgreSQL should do if a conflict arises. The
<action> specified can be one of the following:
DO NOTHING: Tells PostgreSQL to leave the conflicting record as-is. In essence, this action makes no changes, but suppresses the error that would normally occur if you tried to insert a row that violates a condition.
DO UPDATE: This tells PostgreSQL that you want to update the row that is already in the table. The syntax for the update mirrors that of the normal
DO UPDATE is specified, a special virtual table called
EXCLUDED is available for use within the
UPDATE clause. The table contains the values suggested in the original
INSERT command (that conflicted with the existing table values).
Note: If you are connecting to your database with Prisma Client, you can perform upsert operations using the dedicated upsert operation.
DO NOTHING action
For our examples, suppose that we have a table called
CREATE TABLE director (id SERIAL PRIMARY KEY,name TEXT NOT NULL,latest_film TEXT);INSERT INTO director (name)VALUES('frank'),('bob'),('sue');
Let's take a look at how PostgreSQL normally handles an insertion where a proposed row conflicts with existing data. Assuming there's already a director with an
id of 3, PostgreSQL throws an error:
INSERT INTO director (id, name)VALUES(3, 'susan'),(4, 'delores');
ERROR: duplicate key value violates unique constraint "director_pkey"DETAIL: Key (id)=(3) already exists.
In this case, neither of the proposed records were added, even if only the first one had a conflict. If we want to continue adding any rows that do not have a conflict, we can use a
ON CONFLICT DO NOTHING clause.
Here, we tell PostgreSQL to move on if a conflict occurs and continue processing the other rows:
INSERT INTO director (id, name)VALUES(3, 'susan'),(4, 'delores')ON CONFLICT (id) DO NOTHING;
INSERT 0 1
If you query the table, it will show that the second record was added even though the first one conflicted with one of the existing records:
SELECT * FROM director;
id | name | latest_film----+---------+--------------3 | sue |1 | frank | second movie2 | bob | fourth movie4 | delores |(4 rows)
DO UPDATE action
If, instead, we want to update rows when they already exist in the table, we can use the
ON CONFLICT DO UPDATE clause.
Here, we'll do the same type of query as before, but this time, we will update the existing records when a conflict occurs:
INSERT INTO director (id, name)VALUES(2, 'robert'),(5, 'sheila'),(6, 'flora')ON CONFLICT (id) DO UPDATESET name = EXCLUDED.name;
INSERT 0 3
This time, we specify a modification to make to the existing row if it conflicts with one of our proposed insertions. We use the virtual
EXCLUDED table, which contains the items we intended to insert, to update the
name column to a new value on conflict.
You can show that the records were all updated or added by typing:
SELECT * FROM director;
id | name | latest_film----+---------+--------------3 | sue |1 | frank | second movie4 | delores |2 | robert | fourth movie5 | sheila |6 | flora |(6 rows)
INSERT...ON CONFLICT construct allows you to choose between two options when a proposed record conflicts with an existing record. Both
DO NOTHING and
DO UPDATE have their uses depending on the way the data you're adding relates to the existing content.
DO NOTHING option allows you to silently skip conflicting rows, allowing you to add any additional records that do not conflict. Meanwhile, the
DO UPDATE choice let's you conditionally alter the existing record when a conflict occurs, optionally using values from the original proposed row. Understanding the scenario where each may be useful and learning how to this use general format can help simplify your queries when adding new data to an existing data set.