🔁Upsert

NEW

An operation that inserts a new row or updates an existing one in a single atomic step.

DESCRIPTION

Upsert combines insert and update semantics to avoid duplicate records and ensure idempotent writes. It’s implemented via database-specific syntax such as INSERT … ON CONFLICT in PostgreSQL, ON DUPLICATE KEY UPDATE in MySQL, MERGE in SQL Server/Oracle, or upsert flags in NoSQL databases. Upsert reduces race conditions and simplifies ingestion pipelines. It depends on unique keys or constraints to detect existing rows.

ALTERNATIVE DEFINITIONS

PostgreSQL Documentation

This feature is also known as upsert.

Refers to INSERT … ON CONFLICT functionality as upsert.

Source

MySQL Documentation

If a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is performed.

Explains MySQL’s ON DUPLICATE KEY UPDATE behavior implementing upsert.

Source

MongoDB Documentation

If upsert: true and no document matches the query criteria, it inserts a new document.

Describes NoSQL upsert flag behavior for document updates.

Source

RELATED TERMS

Merge INSERT UPDATE Primary Key Entity Matching Idempotency ACID Consistency (ACID) Change Data Capture

COMPETING TERMS

Insert-Only Append-Only

KNOWLEDGE AREAS

Data Storage & Operations Data Engineering Data Integration & Interoperability

TARGET AUDIENCE

Data Engineer Software Developer Data Architect Analytics Engineer

EXTERNAL SOURCES

• Wikipedia - UPSERT
• PostgreSQL - INSERT … ON CONFLICT upsert behavior
• Microsoft Learn - T-SQL MERGE statement documentation
• Oracle - Oracle MERGE statement for upsert

TOP QUESTIONS

When should you use Upsert in data pipelines?

Use upsert when ingesting records that may already exist, ensuring idempotent writes and preventing duplicates.

Category: Requirements

What database syntax implements Upsert?

PostgreSQL uses ON CONFLICT, MySQL uses ON DUPLICATE KEY UPDATE, and SQL Server/Oracle use MERGE.

Category: Overview

What are common pitfalls with Upsert operations?

Lacking proper unique keys, race conditions, and incorrect conflict targets can create duplicate or lost updates.

Category: Mistakes

How does Upsert relate to idempotency?

Upsert helps achieve idempotency: repeated writes produce the same final state based on keys.

Category: Benefits

Is Upsert always atomic?

In transactional databases, upsert statements are atomic, but application-level retries must consider concurrency.

Category: Risk

How does MERGE compare to Upsert?

MERGE generalizes upsert by handling matching and non-matching rows with conditional logic in one statement.

Category: Alternatives

How do NoSQL databases implement Upsert?

They often provide an upsert flag in update APIs to insert when no document matches the filter.

Category: Platform

What performance considerations exist for Upsert?

Indexes and conflict detection can add overhead; batch size and partitioning impact throughput.

Category: Performance

How does Upsert affect downstream systems?

It stabilizes keys and reduces duplicates, simplifying CDC and analytics while maintaining consistency.

Category: Benefits