PostgreSQL Upsert: INSERT ON CONFLICT Explained

Inserting a record only if it doesn't already exist—or updating it if it does—is a common pattern in application development. PostgreSQL supports this natively with the INSERT ... ON CONFLICT clause. This lets you avoid writing extra logic in your app code and instead rely on a single SQL query. Here’s how to use it in different upsert scenarios.

Upsert Examples

Skip on Duplicate

INSERT INTO users(name, email)
VALUES('Alice', 'alice@example.com')
ON CONFLICT(email) DO NOTHING;

Update if Exists

INSERT INTO users(name, email)
VALUES('Alice', 'alice@example.com')
ON CONFLICT(email)
DO UPDATE SET name = EXCLUDED.name;

With UNIQUE Constraint

ALTER TABLE products ADD CONSTRAINT unique_product UNIQUE(name, model);

INSERT INTO products(name, model, tagline)
VALUES('ZenPhone', 'Ultra', 'Efficient design')
ON CONFLICT(name, model)
DO UPDATE SET tagline = EXCLUDED.tagline;

Using a UNIQUE Index

You can also use a unique index:

CREATE UNIQUE INDEX idx_products_unique ON products(name, model);

INSERT INTO products(name, model, tagline)
VALUES('ZenPhone', 'Ultra', 'New version')
ON CONFLICT(name, model)
DO UPDATE SET tagline = EXCLUDED.tagline;

PostgreSQL matches on indexed columns automatically.

FAQ

• What is EXCLUDED?

  The row PostgreSQL tried to insert.

• Can I use indexes instead of constraints?

  Yes, if the index is unique.

• Is upsert atomic?

  Yes, one query, one transaction.

• Multiple conflict actions?

  Not in a single statement.

Conclusion

PostgreSQL makes upserts straightforward with INSERT ON CONFLICT, giving you a single, atomic operation to handle conditional inserts and updates. It works cleanly with both UNIQUE constraints and indexes, and is perfect for streamlining data operations in your apps.

For more insights read PostgreSQL Upsert: INSERT ON CONFLICT Guide.