How to Use ON CONFLICT DO UPDATE in PostgreSQL

In PostgreSQL, use ON CONFLICT (column) DO UPDATE SET to handle conflicts during insert by updating existing rows instead of failing. This lets you insert new data or update existing rows in one command.

Syntax

The ON CONFLICT clause in PostgreSQL lets you specify what to do when a unique constraint violation occurs during an INSERT. You can choose to do nothing or do update the conflicting row.

Parts explained:

• ON CONFLICT (conflict_target): The column(s) or constraint to check for conflicts.
• DO UPDATE SET column = value: Updates specified columns if conflict happens.
• EXCLUDED: Refers to the row proposed for insertion.

sql

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

Example

This example shows inserting a user with an email. If the email already exists, it updates the user's name instead of inserting a new row.

sql

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email TEXT UNIQUE,
  name TEXT
);

INSERT INTO users (email, name) VALUES ('[e-mail]', 'Alice');

-- Insert or update if email exists
INSERT INTO users (email, name) VALUES ('[e-mail]', 'Alice Cooper')
ON CONFLICT (email) DO UPDATE
SET name = EXCLUDED.name;

-- Check result
SELECT * FROM users;

Output

 id |       email        |     name      
----+--------------------+---------------
  1 | [e-mail]   | Alice Cooper
(1 row)

Common Pitfalls

Common mistakes when using ON CONFLICT DO UPDATE include:

• Not specifying the conflict target column or constraint.
• Trying to update the primary key or unique column causing conflict again.
• Forgetting to use EXCLUDED to refer to the new values.
• Not handling cases where no conflict occurs (which inserts normally).

sql

/* Wrong: missing conflict target */
INSERT INTO users (email, name) VALUES ('[e-mail]', 'Bob')
ON CONFLICT DO NOTHING;

/* Right: specify conflict column */
INSERT INTO users (email, name) VALUES ('[e-mail]', 'Bob')
ON CONFLICT (email) DO UPDATE
SET name = EXCLUDED.name;

Quick Reference

Clause	Description
ON CONFLICT (column)	Specifies the column(s) to check for conflicts
DO NOTHING	Skip insert if conflict occurs
DO UPDATE SET	Update existing row with new values on conflict
EXCLUDED	References the values proposed for insertion

Key Takeaways

Use ON CONFLICT (column) DO UPDATE to update rows on insert conflict.

Always specify the conflict target column or constraint.

Use EXCLUDED to access the new row's values in the update.

Avoid updating unique or primary key columns that cause conflicts.

ON CONFLICT DO NOTHING skips insert if conflict occurs.