Upsert is a database operation that inserts a new record when no match exists or updates the existing record when it does. It uses a primary key or unique constraint to decide what counts as a match, helping applications and data pipelines avoid duplicate records.
Upsert
What Is an Upsert?
Upsert is a database operation that inserts a new record when no matching record exists or updates an existing record when it does. The name combines “update” and “insert.”
The database finds a match through a primary key or unique constraint, such as a user ID or external customer ID. A customer-sync process can create a profile for a new external ID or refresh the profile already stored for that ID.
How an Upsert Works
An upsert lets the database decide whether to insert or update in one write operation.
- An application submits a record and matching key.
- The database checks the selected primary key or unique constraint for a conflict.
- If no match exists, the database inserts a new row.
- If a match exists, it updates selected fields or, when configured, leaves the row unchanged.
A database-native upsert is safer than checking for a record and then inserting it. Another request could insert the same key between those separate actions. The database constraint prevents this race condition.
Upsert vs. INSERT, UPDATE, and MERGE
These operations overlap but are not interchangeable, and syntax differs by database product.
| Operation | When a record exists | When it is missing | Best use |
|---|---|---|---|
| INSERT | Usually fails on a duplicate key | Creates a record | Known-new data |
| UPDATE | Changes a record | Changes nothing | Known-existing data |
| UPSERT | Updates selected fields | Creates a record | Syncing records by a unique key |
| MERGE | Can update, delete, or take other actions | Can insert | Complex source-to-target matching |
MERGE can implement upsert behavior but is broader. PostgreSQL and SQLite commonly use an ON CONFLICT clause, while other systems use different commands.
Benefits of Using Upserts
Upserts are useful when data may arrive more than once.
- They prevent duplicates when a reliable unique key exists.
- They make imports retry-safe after a failed job.
- They simplify synchronization with third-party APIs and webhooks.
- They reduce database round trips compared with separate checks and writes.
- They can keep customer profiles current by matching an external ID.
Practical Limits and Safeguards
An upsert is only as reliable as its matching rule and update policy.
- Choose a stable, truly unique key. Names and email addresses can change or be shared.
- Update only fields the incoming source is allowed to own, so stale data does not overwrite newer values.
- Test simultaneous writes when several services can modify a record.
- Review database-specific syntax and behavior before moving code between platforms.
- Log unexpected updates and conflicts to find incorrect matching rules early.
Using Upserts in App Development with Emergent
Upserts support full-stack applications that save form submissions, maintain user profiles, and synchronize connected services. When planning app design and development, define the record key and fields each workflow may update before writing database logic.
Emergent can help teams build and test these workflows, including database constraints and safe update rules. It is also useful for projects that need no-code backend tools without overlooking data integrity. Build data-driven apps with Emergent.
Frequently Asked Questions
Your Questions, Answered
This will automatically populate, don't change
Don't change this element unless you know what you are doing
What does UPSERT mean?
UPSERT means update or insert. It creates a record when no matching key exists and updates a record when one does.
How does UPSERT work?
The database checks a primary key or unique constraint. It inserts the incoming record if there is no match, or follows the configured conflict action if a match exists.
What's the difference between UPSERT and INSERT?
INSERT only creates a new record and usually fails if its key already exists. UPSERT handles both new and existing records in one operation.
Are UPSERT and MERGE the same?
No. MERGE is a broader command that can combine matched and unmatched data in several ways. An upsert is a specific insert-or-update behavior that MERGE may implement.
Is UPSERT a POST or PUT?
UPSERT is a database concept, not an HTTP method. An API may use POST or PUT for an endpoint that eventually performs an upsert, depending on its API design.
Start Building
on Emergent today
on Emergent today
Start Building
