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: (column1)
• The name of a unique constraint: ON CONSTRAINT <constraint_name>

The companion <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 UPDATE command.

When 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 .

Using the DO NOTHING action

For our examples, suppose that we have a table called director .

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 movie
  2 | bob     | fourth movie
  4 | delores |
(4 rows)