INSERT ON CONFLICT (upsert) in PostgreSQL

Introduction

This helps you add new data to a table but also update existing data if there is a conflict, all in one step.

When you want to add a new user but update their info if they already exist.

When importing data and you want to avoid duplicate entries.

When you want to keep a list updated without deleting and re-adding rows.

When you want to track inventory and update stock if the item is already listed.

Syntax

PostgreSQL

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

-- OR

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

ON CONFLICT tells PostgreSQL what to do if the new data conflicts with existing data.

EXCLUDED refers to the new data you tried to insert.

Examples

Insert a user or update their name and email if the id already exists.

PostgreSQL

INSERT INTO users (id, name, email)
VALUES (1, 'Alice', '[e-mail]')
ON CONFLICT (id) DO UPDATE SET
  name = EXCLUDED.name,
  email = EXCLUDED.email;

Try to insert a product but skip if the sku already exists.

PostgreSQL

INSERT INTO products (sku, price)
VALUES ('ABC123', 9.99)
ON CONFLICT (sku) DO NOTHING;

Sample Program

This creates a table, adds one item, then tries to add the same item with a new quantity. Instead of error, it updates the quantity.

PostgreSQL

CREATE TABLE inventory (
  item_id SERIAL PRIMARY KEY,
  sku TEXT UNIQUE,
  quantity INT
);

INSERT INTO inventory (sku, quantity) VALUES ('X123', 10);

-- Try to add same sku with new quantity, update if exists
INSERT INTO inventory (sku, quantity) VALUES ('X123', 20)
ON CONFLICT (sku) DO UPDATE SET quantity = EXCLUDED.quantity;

SELECT * FROM inventory;

Important Notes

You must have a unique constraint or primary key on the conflict column.

Use DO NOTHING to skip duplicates without error.

Use DO UPDATE to change existing rows with new data.

Summary

INSERT ON CONFLICT helps add or update data in one command.

It prevents errors from duplicate keys by updating or ignoring.

Use EXCLUDED to refer to the new data in updates.