Upsert

Upsert is a combination of update and insert. PostgreSQL does not have a dedicated UPSERT keyword, but supports the operation through the INSERT ... ON CONFLICT statement. When a conflict occurs on a unique constraint or unique index, you can either do nothing or update the existing row with the new values.

INSERT ... ON CONFLICT syntax

The basic syntax specifies a conflict target and the action to take when that conflict occurs:

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

The EXCLUDED keyword refers to the row that was proposed for insertion but was rejected due to the conflict. Use DO NOTHING instead of DO UPDATE if you want to silently skip duplicates.

Practical upsert examples

Create an inventory table and insert some sample rows:

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);

Update an existing row when a conflict on id occurs:

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;

INSERT 0 1

Verify the update:

SELECT * FROM inventory WHERE id = 1;

 id | name | price | quantity
----+------+-------+----------
  1 | A    | 16.99 |      120

Insert a brand-new row when no conflict exists (id 4 is new):

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;

SELECT * FROM inventory ORDER BY id;

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

Production tips for upsert

• Always specify a conflict target — ON CONFLICT (column) or a named constraint — so the statement fails loudly if the schema changes.
• Use EXCLUDED.column to reference proposed values in the SET clause; mixing EXCLUDED with table-qualified names lets you build conditional updates.
• Add a WHERE clause to the DO UPDATE block to skip no-op updates: DO UPDATE SET price = EXCLUDED.price WHERE inventory.price <> EXCLUDED.price.
• For PostgreSQL 15+, consider MERGE when you need a single statement that also handles deletes.
• Validate upsert behavior against concurrent writers using SERIALIZABLE isolation if data correctness is critical.