PostgresqlHow-ToBeginner · 4 min read

How to Use Upsert in PostgreSQL: Syntax and Examples

In PostgreSQL, you use INSERT ... ON CONFLICT to perform an upsert, which inserts a new row or updates an existing row if a conflict occurs on a unique constraint. This lets you avoid separate insert and update queries by handling both in one statement.

📐

Syntax

The basic syntax for upsert in PostgreSQL uses INSERT INTO with ON CONFLICT clause. You specify the conflict target (usually a unique column or constraint) and then define what to do if a conflict happens, typically an UPDATE.

INSERT INTO table (columns) VALUES (values): tries to insert a new row.
ON CONFLICT (conflict_target): defines the column(s) to check for conflicts.
DO UPDATE SET column = value: updates the existing row if conflict occurs.
DO NOTHING: skips the insert if conflict occurs.

sql

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

💻

Example

This example shows how to insert a user with an email and name. 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 ('alice@example.com', 'Alice')
ON CONFLICT (email) DO UPDATE
SET name = EXCLUDED.name;

-- Try inserting again with the same email but different name
INSERT INTO users (email, name) VALUES ('alice@example.com', 'Alice Cooper')
ON CONFLICT (email) DO UPDATE
SET name = EXCLUDED.name;

SELECT * FROM users;

Output

id | email | name ----+--------------------+----------------- 1 | alice@example.com | Alice Cooper (1 row)

⚠️

Common Pitfalls

Common mistakes when using upsert include:

Not specifying the correct conflict target, causing errors or unexpected behavior.
Forgetting to use EXCLUDED to refer to the new values in the DO UPDATE clause.
Using DO NOTHING when you actually want to update existing rows.
Assuming upsert works without a unique constraint or index on the conflict column.

sql

/* Wrong: Missing conflict target */
INSERT INTO users (email, name) VALUES ('bob@example.com', 'Bob')
ON CONFLICT DO UPDATE SET name = EXCLUDED.name;

/* Right: Specify conflict target */
INSERT INTO users (email, name) VALUES ('bob@example.com', 'Bob')
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;

📊

Quick Reference

Clause	Description
INSERT INTO	Insert new row into table
ON CONFLICT (column)	Specify column(s) to detect conflict
DO UPDATE SET	Update existing row with new values
DO NOTHING	Skip insert if conflict occurs
EXCLUDED	Reference to values proposed for insertion

✅

Key Takeaways

Use INSERT ... ON CONFLICT to perform upsert in PostgreSQL.

Specify the conflict target column(s) to detect duplicates.

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

Ensure a unique constraint or index exists on the conflict column.

DO NOTHING skips insert if a conflict is found without updating.