PostgreSQL - Upsert

PostgreSQL - UPSERT Operation

The term “UPSERT” is a portmanteau of “UPDATE” and “INSERT”. It describes the action of inserting a new row into a table if it doesn’t already exist, or updating the existing row if it does. This is a common requirement when synchronizing data. Since PostgreSQL 9.5, this is handled elegantly using the ON CONFLICT clause.

Imagine you have a product inventory table and you receive a daily feed of product prices. Some products might be new, while others are updates to existing ones. An UPSERT operation allows you to process this feed in a single, atomic step.

Syntax: INSERT ON CONFLICT

The core of the UPSERT functionality in PostgreSQL is the ON CONFLICT clause, which you add to an INSERT statement. It has two main variations.

Syntax for ‘DO UPDATE’

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON CONFLICT (conflict_target)
DO UPDATE SET
    column1 = EXCLUDED.column1,
    column2 = EXCLUDED.column2, ...;

Syntax for ‘DO NOTHING’

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON CONFLICT (conflict_target)
DO NOTHING;

Key Components

• conflict_target: Specifies what causes a conflict. This can be a column with a UNIQUE constraint (like (product_code)) or a specific constraint name (ON CONSTRAINT constraint_name).
• DO UPDATE SET: This action is taken if a conflict occurs. You define how to update the existing row.
• EXCLUDED: This is a special, temporary table-like object that holds the values of the row that failed to be inserted due to the conflict. You use EXCLUDED.column_name to access these proposed new values.
• DO NOTHING: This action tells PostgreSQL to simply discard the new row and do nothing if a conflict is detected.

Practical Examples

Setup: Product Inventory Table

Let’s create a table to track product inventory and prices. The sku (Stock Keeping Unit) will be our unique identifier.

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    sku VARCHAR(20) UNIQUE NOT NULL,
    name VARCHAR(100),
    price NUMERIC(10, 2),
    stock_count INT
);

INSERT INTO products (sku, name, price, stock_count)
VALUES ('LAP-101', '14-inch Laptop', 1200.00, 50);

Example 1: Using ON CONFLICT DO UPDATE

We’ve received an update for product ‘LAP-101’ with a new price and stock count. We’ll use UPSERT to apply this change.

INSERT INTO products (sku, name, price, stock_count)
VALUES ('LAP-101', '14-inch Laptop Pro', 1250.50, 45)
ON CONFLICT (sku)
DO UPDATE SET
    price = EXCLUDED.price,
    stock_count = EXCLUDED.stock_count,
    name = EXCLUDED.name; -- Also update the name

Now, let’s verify the result. The existing row for ‘LAP-101’ should be updated.

postgres=# SELECT * FROM products WHERE sku = 'LAP-101';
 id |   sku   |         name         |  price  | stock_count
----+---------+----------------------+---------+-------------
  1 | LAP-101 | 14-inch Laptop Pro   | 1250.50 |          45
(1 row)

Next, let’s insert a completely new product. Since there is no conflict on the sku, it will be a standard INSERT.

INSERT INTO products (sku, name, price, stock_count)
VALUES ('KBD-202', 'Mechanical Keyboard', 150.00, 200)
ON CONFLICT (sku)
DO UPDATE SET price = EXCLUDED.price, stock_count = EXCLUDED.stock_count;

The table now contains two distinct products:

postgres=# SELECT * FROM products ORDER BY id;
 id |   sku   |         name         |  price  | stock_count
----+---------+----------------------+---------+-------------
  1 | LAP-101 | 14-inch Laptop Pro   | 1250.50 |          45
  2 | KBD-202 | Mechanical Keyboard  |  150.00 |         200
(2 rows)

Example 2: Using ON CONFLICT DO NOTHING

This is useful for cases where you only want to add new entries and ignore any duplicates. For example, populating a mailing_list table.

CREATE TABLE mailing_list (
    id SERIAL PRIMARY KEY,
    email VARCHAR(100) UNIQUE NOT NULL
);

INSERT INTO mailing_list (email) VALUES ('alpha@example.com');

-- Now, try to insert the same email again
INSERT INTO mailing_list (email) VALUES ('alpha@example.com')
ON CONFLICT (email) DO NOTHING;

The INSERT statement will report INSERT 0 0, meaning no rows were inserted. The table remains unchanged, and no error is thrown.

postgres=# SELECT * FROM mailing_list;
 id |       email
----+-------------------
  1 | alpha@example.com
(1 row)

Advanced Tip: Conditional Updates

You can add a WHERE clause to the DO UPDATE action to make the update conditional. For example, you might only want to update the product price if the new price is lower than the current price.

INSERT INTO products (sku, name, price, stock_count)
VALUES ('LAP-101', '14-inch Laptop Pro', 1300.00, 40) -- Higher price
ON CONFLICT (sku)
DO UPDATE SET
    price = EXCLUDED.price
WHERE
    products.price > EXCLUDED.price; -- Only update if new price is lower

In this case, since 1250.50 is not greater than 1300.00, the UPDATE will be skipped, and the row will remain unchanged. This provides fine-grained control over your data synchronization logic.