Introduction to the PostgreSQL UPSERT Statement

Upsert is a combination ofupdate andinsert. The upsert allows you to update an existing row or insert a new one if it doesn’t exist.

PostgreSQL does not have theUPSERT statement but it supports the upsert operation via theINSERT...ON CONFLICT statement.

If you use PostgreSQL 15 or later, you can use theMERGE statement which is equivalent to theUPSERT statement.

Here’s the basic syntax of theINSERT...ON CONFLICT statement:

INSERT INTO table_name (column1, column2, ...)VALUES (value1, value2, ...)ON CONFLICT (conflict_column)DO NOTHING | DOUPDATE SET column1= value1, column2= value2, ...;

In this syntax:

• table_name: This is the name of the table into which you want to insert data.
• (column1, column2, ...): The list of columns you want to insert values into the table.
• VALUES(value1, value2, ...): The values you want to insert into the specified columns(column1, column2, ...).
• ON CONFLICT (conflict_column): This clause specifies the conflict target, which is theunique constraint orunique index that may cause a conflict.
• DO NOTHING: This instructs PostgreSQL to do nothing when a conflict occurs.
• DO UPDATE: This performs an update if a conflict occurs.
• SET column = value1, column = value2, ...: This list of the columns to be updated and their corresponding values in case of conflict.

How theINSERT ... ON CONFLICT statement works.

First, theON CONFLICT clause identifies the conflict target which is usually a unique constraint (or a unique index). If the data that you insert violates the constraint, a conflict occurs.

Second, theDO UPDATE instructs PostgreSQL to update existing rows or do nothing rather than aborting the entire operation when a conflict occurs.

Third, theSET clause defines the columns and values to update. You can use new values or reference the values you attempted to insert using theEXCLUDED keyword.

PostgreSQL UPSERT examples

The following statements create theinventory table andinsert data into it:

CREATE TABLE inventory(   idINT PRIMARY KEY,   name VARCHAR(255)NOT NULL,   priceDECIMAL(10,2)NOT NULL,   quantityINT NOT NULL);INSERT INTO inventory(id,name, price, quantity)VALUES(1,'A',15.99,100),(2,'B',25.49,50),(3,'C',19.95,75)RETURNING*;

Output:

id | name | price | quantity----+------+-------+----------  1 | A    | 15.99 |      100  2 | B    | 25.49 |       50  3 | C    | 19.95 |       75(3 rows)INSERT 0 3

Theinventory table contains information about various products, including their names, prices, and quantities in stock.

Suppose you’ve received an updated list of products with new prices, and now you need to update the inventory accordingly.

In this case, the upsert operation can be handy to handle the following situations:

• Updating existing products. If a product already exists in theinventory table, you want to update its price and quantity with the new information.
• Insert new products. If a product is not in theinventory table, you want to insert it into the table.

1) Basic PostgreSQL INSERT … ON CONFLICT statement example

The following example uses theINSERT ... ON CONFLICT statement to insert a new row into theinventory table:

INSERT INTO inventory (id,name, price, quantity)VALUES (1,'A', 16.99, 120)ON CONFLICT(id)DO UPDATE SET  price = EXCLUDED.price,  quantity = EXCLUDED.quantity;

Output:

INSERT 0 1

In this example, we attempt to insert a new row into theinventory table.

However, theinventory table already has a row with id 1, therefore, a conflict occurs.

TheDO UPDATE changes the price and quantity of the product to the new values being inserted. TheEXCLUDED allows you to access the new values.

The following statement verifies the update:

SELECT * FROM inventoryWHERE id = 1;

Output:

id | name | price | quantity----+------+-------+----------  1 | A    | 16.99 |      120(1 row)

2) Inserting data example

The following example uses theINSERT ... ON CONFLICT statement to insert a new row into theinventory table:

INSERT INTO inventory (id,name, price, quantity)VALUES (4,'D', 29.99, 20)ON CONFLICT(id)DO UPDATE SET  price = EXCLUDED.price,  quantity = EXCLUDED.quantity;

Output:

INSERT 0 1

In this case, the statementinserts a new row into theinventory table because the product id 4 does not exist in theinventory table.

The following statement verifies the insert:

SELECT * FROM inventoryORDER BY id;

Output:

id | name | price | quantity----+------+-------+----------  1 | A    | 16.99 |      120  2 | B    | 25.49 |       50  3 | C    | 19.95 |       75  4 | D    | 29.99 |       20(4 rows)

Summary

• Use the PostgreSQL upsert to update data if it already exists or insert the data if it does not.
• Use theINSERT...ON CONFLICT statement for upsert.

Last updated onMarch 27, 2024

On this page

• Introduction to the PostgreSQL UPSERT Statement
• PostgreSQL UPSERT examples
• Summary