# How to use `INSERT ON CONFLICT` to upsert data in PostgreSQL > Part of Prisma's Data Guide. The complete index is at https://www.prisma.io/dataguide/llms.txt. The `INSERT...ON CONFLICT` construct let's you define what PostgreSQL should do if a row you are inserting conflicts with an existing row. This enables 'upsert' operations which insert records or update them on conflict. Canonical URL: https://www.prisma.io/dataguide/postgresql/inserting-and-modifying-data/insert-on-conflict Authors: Justin Ellingwood Content revision: 75693dca60995e658856c83944bdd8f8d23a8896f557e5054de9ddbad41ecb80 Source revision: 3209fc5696440a92402a9e3aaaab817ef4a9fb2c ## Introduction 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](https://www.prisma.io/dataguide/intro/database-glossary#upsert) (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: ```sql INSERT INTO my_table (column1, column2) VALUES (value1, value2), (value3, value4), (value5, value6), (value7, value8) ON CONFLICT ; ``` In this context, the `` 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 ` The companion `` item will define what PostgreSQL should do if a conflict arises. The `` 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 ORM](https://www.prisma.io/docs/orm), you can perform upsert operations using the dedicated [upsert operation](https://www.prisma.io/docs/orm/fundamentals/writing-data?db=postgresql#upsert-a-record). ## Using the `DO NOTHING` action For our examples, suppose that we have a table called `director`. **Expand to see the commands to create and populate the table** ```sql 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: ```sql 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: ```sql 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: ```sql SELECT * FROM director; ``` ``` id | name | latest_film ----+---------+-------------- 3 | sue | 1 | frank | second movie 2 | bob | fourth movie 4 | delores | (4 rows) ``` ## Using the `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: ```sql INSERT INTO director (id, name) VALUES (2, 'robert'), (5, 'sheila'), (6, 'flora') ON CONFLICT (id) DO UPDATE SET 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: ```sql SELECT * FROM director; ``` ``` id | name | latest_film ----+---------+-------------- 3 | sue | 1 | frank | second movie 4 | delores | 2 | robert | fourth movie 5 | sheila | 6 | flora | (6 rows) ``` ## Conclusion PostgreSQL's `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. The `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.