Upsert

Definitions

1. An atomic operation that inserts a row if it doesn't exist or updates it if it does, based on a unique key. Eliminates race conditions between separate SELECT + INSERT/UPDATE. Syntax varies: PostgreSQL uses INSERT...ON CONFLICT, MySQL uses INSERT...ON DUPLICATE KEY, and SQLite uses INSERT OR REPLACE. Essential for idempotent data pipelines.

   In plain English: A database operation that inserts a new row or updates the existing one in a single atomic step.

   Example: "INSERT INTO metrics (date, count) VALUES (today, 1) ON CONFLICT (date) DO UPDATE SET count = count + 1 — atomic upsert, no race condition."

Related Terms

• Query
• Table
• Tombstone
• Write-Ahead Log
• Connection Pooling
• Query Plan
• Index Scan vs Seq Scan
• CTE
• Database Trigger
• Left Join
• Query Engine
• RDBMS