upserts

Upsert, a portmanteau of update and insert, refers to a database operation that inserts a new row or updates an existing one when a conflict on a unique or primary key arises. The upsert merges insert and update into a single atomic operation, ensuring a row with the given key exists with the latest data without requiring separate read and write steps.

Implementation details vary by database system. In PostgreSQL, the construct is INSERT ... ON CONFLICT (constraint) DO UPDATE; in MySQL, INSERT ... ON DUPLICATE KEY UPDATE is used; SQLite supports INSERT ... ON CONFLICT DO UPDATE (or REPLACE in other forms); SQL Server offers MERGE as a broader alternative to perform conditional inserts or updates.

These constructions enable idempotent write operations and simplify application logic, but they introduce concurrency considerations, constraint checks, and potential performance tradeoffs. Misusing MERGE in particular can lead to race conditions or unintended side effects, and upserts that affect many rows or large data types can impose significant index maintenance overhead.

Common use cases include synchronizing records from external sources, maintaining caches, and upserting user or product records where only certain fields change. When implementing upserts, developers should consider transactional guarantees, conflict handling semantics, and any side effects from triggers or cascading constraints to ensure predictable behavior.

Example (PostgreSQL): insert into users (id, name) values (1, 'Alice') on conflict (id) do update set name = excluded.name. This demonstrates the core idea: insert when missing, or update when a conflict on a key occurs.