DEV Community

Cover image for PostgreSQL Upsert: INSERT ON CONFLICT Explained
DbVisualizer
DbVisualizer

Posted on

1

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;
Enter fullscreen mode Exit fullscreen mode

Update if Exists

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

With UNIQUE Constraint

ALTER TABLE products ADD CONSTRAINT unique_product UNIQUE(name, model);
Enter fullscreen mode Exit fullscreen mode
INSERT INTO products(name, model, tagline)
VALUES('ZenPhone', 'Ultra', 'Efficient design')
ON CONFLICT(name, model)
DO UPDATE SET tagline = EXCLUDED.tagline;
Enter fullscreen mode Exit fullscreen mode

Using a UNIQUE Index

You can also use a unique index:

CREATE UNIQUE INDEX idx_products_unique ON products(name, model);
Enter fullscreen mode Exit fullscreen mode
INSERT INTO products(name, model, tagline)
VALUES('ZenPhone', 'Ultra', 'New version')
ON CONFLICT(name, model)
DO UPDATE SET tagline = EXCLUDED.tagline;
Enter fullscreen mode Exit fullscreen mode

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.

Top comments (0)

Feature flag article image

Create a feature flag in your IDE in 5 minutes with LaunchDarkly’s MCP server 🏁

How to create, evaluate, and modify flags from within your IDE or AI client using natural language with LaunchDarkly's new MCP server. Follow along with this tutorial for step by step instructions.

Read full post

👋 Kindness is contagious

Take a moment to explore this thoughtful article, beloved by the supportive DEV Community. Coders of every background are invited to share and elevate our collective know-how.

A heartfelt "thank you" can brighten someone's day—leave your appreciation below!

On DEV, sharing knowledge smooths our journey and tightens our community bonds. Enjoyed this? A quick thank you to the author is hugely appreciated.

Okay