Conditional INSERT with ON CONFLICT in PostgreSQL

Introduction

Sometimes you want to add new data to a table but avoid errors if the data already exists. Conditional INSERT with ON CONFLICT helps you do this smoothly.

Adding a new user to a database but skipping if the user already exists.

Inserting a product into inventory only if it is not already listed.

Logging events where duplicate entries should be ignored.

Updating a record if it exists, or inserting it if it does not.

Syntax

PostgreSQL

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

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

ON CONFLICT specifies what to do if a duplicate key violation happens.

DO NOTHING skips the insert if there is a conflict; DO UPDATE changes existing data.

Examples

Try to add user Alice with id 1. If id 1 exists, skip without error.

PostgreSQL

INSERT INTO users (id, name) VALUES (1, 'Alice') ON CONFLICT (id) DO NOTHING;

If user with id 1 exists, update their name to 'Alice'. Otherwise, insert new user.

PostgreSQL

INSERT INTO users (id, name) VALUES (1, 'Alice') ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;

Sample Program

We create a users table, insert Alice, then try to insert Bob with the same id but skip it. Then we insert Charlie with the same id and update the name. Finally, we select all users.

PostgreSQL

CREATE TABLE users (id INT PRIMARY KEY, name TEXT);

INSERT INTO users (id, name) VALUES (1, 'Alice');

-- Try inserting duplicate id with DO NOTHING
INSERT INTO users (id, name) VALUES (1, 'Bob') ON CONFLICT (id) DO NOTHING;

-- Try inserting duplicate id with DO UPDATE
INSERT INTO users (id, name) VALUES (1, 'Charlie') ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;

SELECT * FROM users ORDER BY id;

Important Notes

EXCLUDED refers to the values proposed for insertion that caused the conflict.

You must specify the conflict target column(s) that have unique constraints.

ON CONFLICT helps keep your data clean without manual checks.

Summary

Use ON CONFLICT to handle duplicate key errors during INSERT.

DO NOTHING skips inserting duplicates silently.

DO UPDATE lets you change existing rows when duplicates occur.