---
title: 'PostgreSQL UPSERT using INSERT ON CONFLICT Statement'
page_title: 'PostgreSQL UPSERT Statement'
page_description: 'This tutorial shows you how to use the PostgreSQL UPSERT to either update an existing row or insert a new row if it does not exist.'
prev_url: 'https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-upsert/'
ogImage: ''
updatedOn: '2024-03-27T06:05:16+00:00'
enableTableOfContents: true
previousLink:
  title: 'PostgreSQL DELETE JOIN'
  slug: 'postgresql-tutorial/postgresql-delete-join'
nextLink:
  title: 'PostgreSQL MERGE Statement'
  slug: 'postgresql-tutorial/postgresql-merge'
---

**Summary**: in this tutorial, you will learn how to use the PostgreSQL upsert feature to insert a new row into a table if the row does not exist, or update an existing row if it already exists.

## Introduction to the PostgreSQL UPSERT Statement

Upsert is a combination of [update](postgresql-update) and [insert](postgresql-insert). The upsert allows you to update an existing row or insert a new one if it doesn’t exist.

PostgreSQL does not have the `UPSERT` statement but it supports the upsert operation via the `INSERT...ON CONFLICT` statement.

If you use PostgreSQL 15 or later, you can use the [MERGE](postgresql-merge) statement which is equivalent to the `UPSERT` statement.

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

```sql
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON CONFLICT (conflict_column)
DO NOTHING | DO UPDATE 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 the [unique constraint](postgresql-unique-constraint) or [unique index](../postgresql-indexes/postgresql-unique-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 the `INSERT ... ON CONFLICT` statement works.

First, the `ON 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, the `DO UPDATE` instructs PostgreSQL to update existing rows or do nothing rather than aborting the entire operation when a conflict occurs.

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

## PostgreSQL UPSERT examples

The following statements create the `inventory` table and [insert data into it](postgresql-insert):

```sql
CREATE TABLE inventory(
   id INT PRIMARY KEY,
   name VARCHAR(255) NOT NULL,
   price DECIMAL(10,2) NOT NULL,
   quantity INT 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:

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


INSERT 0 3
```

The `inventory` 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 the `inventory` table, you want to update its price and quantity with the new information.
- **Insert new products**. If a product is not in the `inventory` table, you want to insert it into the table.

### 1\) Basic PostgreSQL INSERT … ON CONFLICT statement example

The following example uses the `INSERT ... ON CONFLICT` statement to insert a new row into the `inventory` 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:

```sql
INSERT 0 1
```

In this example, we attempt to insert a new row into the `inventory` table.

However, the `inventory` table already has a row with id 1, therefore, a conflict occurs.

The `DO UPDATE` changes the price and quantity of the product to the new values being inserted. The `EXCLUDED` allows you to access the new values.

The following statement verifies the update:

```
SELECT * FROM inventory
WHERE id = 1;
```

Output:

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

### 2\) Inserting data example

The following example uses the `INSERT ... ON CONFLICT` statement to insert a new row into the `inventory` 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:

```sql
INSERT 0 1
```

In this case, the statement [inserts a new row](../postgresql-python/insert) into the `inventory` table because the product id 4 does not exist in the `inventory` table.

The following statement verifies the insert:

```
SELECT * FROM inventory
ORDER 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 the `INSERT...ON CONFLICT` statement for upsert.