Homeglossary

Upsert

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.

Build data-driven apps with Emergent.

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.

  1. An application submits a record and matching key.
  2. The database checks the selected primary key or unique constraint for a conflict.
  3. If no match exists, the database inserts a new row.
  4. 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.

OperationWhen a record existsWhen it is missingBest use
INSERTUsually fails on a duplicate keyCreates a recordKnown-new data
UPDATEChanges a recordChanges nothingKnown-existing data
UPSERTUpdates selected fieldsCreates a recordSyncing records by a unique key
MERGECan update, delete, or take other actionsCan insertComplex 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.

Start Building
on Emergent today
Start Building