upsert

An upsert is a database operation that inserts a row if it does not exist or updates the existing row if it does. The term is a portmanteau of update and insert, and it is used to simplify data synchronization and maintenance of up-to-date records.

Different systems implement upserts with distinct syntax. In PostgreSQL, upsert is achieved with INSERT ... ON CONFLICT DO UPDATE, which uses a unique constraint or index to detect conflicts and then applies the specified updates to the proposed row. In MySQL, the pattern is INSERT ... ON DUPLICATE KEY UPDATE, which updates the row if a duplicate primary or unique key is found. SQL Server provides a broader MERGE statement that can perform insert, update, or delete actions in a single statement, enabling upsert-like behavior. In MongoDB, upsert is performed by an update operation with the upsert option set to true, which inserts a new document if no existing document matches the query.

Upsert operations are typically atomic within a single statement or command, helping prevent race conditions where two processes insert the same row. They rely on unique constraints, indexes, or query matching to determine when to insert versus update. The exact semantics can vary by system, especially in scenarios involving multiple unique constraints or composite keys. Some databases offer broader merge or queen-merge-like capabilities that go beyond a simple insert-or-update.

Common considerations when using upserts include ensuring appropriate constraints to guarantee deterministic behavior, understanding performance implications for large datasets, and being aware of potential contention or lock impacts in highly concurrent environments. Overall, upserts provide a practical pattern for keeping data current without explicit pre-checks.