Upserting

Upserting is a database operation that combines inserting a new row with updating an existing one. If a row already exists that matches a unique constraint or primary key, the database updates the specified fields; otherwise it inserts a new row. The term is a portmanteau of insert and update and is widely supported in modern relational and document-oriented databases.

Vertical variants exist across systems. In PostgreSQL, the common pattern is INSERT ... ON CONFLICT DO UPDATE; MySQL offers INSERT ... ON DUPLICATE KEY UPDATE; SQL Server provides MERGE; MongoDB uses an upsert option on update operations. Some databases also provide a no-op alternative that performs no action when a conflict occurs.

Upserts are designed to be atomic, preventing race conditions where two processes insert the same row simultaneously. The operation relies on unique constraints to detect conflicts and may acquire locks or use optimistic concurrency controls. Performance and locking considerations vary by database and index design.

Common use cases include data synchronization, feed processing, and maintaining slowly changing dimensions, where the same key may appear multiple times with new values. While convenient, upserts require careful planning related to conflicting updates, trigger side effects, and the effect of updates on derived data.

See also: MERGE; UPSERT; upsert in NoSQL.