How to Use INSERT ON CONFLICT in PostgreSQL

In PostgreSQL, use INSERT ... ON CONFLICT to handle duplicate key conflicts by either updating existing rows or skipping the insert. The ON CONFLICT clause lets you specify the conflict target and the action to take, such as DO NOTHING or DO UPDATE.

Syntax

The basic syntax of INSERT ON CONFLICT includes the INSERT statement followed by ON CONFLICT specifying the conflict target (usually a unique constraint or index). You then choose an action: DO NOTHING to skip the insert if a conflict occurs, or DO UPDATE to update the existing row.

• conflict_target: The column(s) or constraint name to check for conflicts.
• DO NOTHING: Ignores the insert if conflict happens.
• DO UPDATE SET: Updates specified columns if conflict happens.

sql

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

-- Or to update on conflict:
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON CONFLICT (conflict_target) DO UPDATE SET
  column1 = EXCLUDED.column1,
  column2 = EXCLUDED.column2;

Example

This example shows how to insert a user into a users table with a unique 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 a new user
INSERT INTO users (email, name) VALUES ('[e-mail]', 'Alice');

-- Try to insert with the same email, update name on conflict
INSERT INTO users (email, name) VALUES ('[e-mail]', 'Alice Cooper')
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;

-- Check the table content
SELECT * FROM users;

Output

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

Common Pitfalls

Common mistakes include:

• Not specifying the correct conflict_target, causing errors or unexpected behavior.
• Using DO UPDATE without referencing EXCLUDED to get the new values.
• Trying to update columns that are part of the unique constraint, which can cause conflicts again.
• Forgetting to handle conflicts when inserting bulk data, leading to errors.

sql

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

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

Quick Reference

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

Key Takeaways

Use ON CONFLICT to handle duplicate key errors during INSERT in PostgreSQL.

Specify the conflict target to tell PostgreSQL which constraint to check.

Use DO NOTHING to skip inserts that would cause conflicts.

Use DO UPDATE with EXCLUDED to update existing rows on conflict.

Always test your conflict handling logic to avoid unexpected data issues.