Upsert

An upsert asks the database to insert a row, but update the existing one if that insert collides with a declared key. PostgreSQL spells it INSERT ... ON CONFLICT DO UPDATE. The whole choice happens as one database statement, so it avoids the race in 'SELECT first, then INSERT or UPDATE'.

The word is not standard SQL, which is why every database spells it differently. MySQL has had INSERT ... ON DUPLICATE KEY UPDATE for years, PostgreSQL only gained ON CONFLICT in 9.5 and the standard MERGE statement in 15, and ORMs paper over the gap with a single method such as Prisma's upsert. Check what your layer actually emits before you trust it to be one statement.

Reach for it when a stable identity already tells you which logical row you are syncing: a provider's event id, a user's setting, or a daily aggregate keyed by account and date. The conflict target must be backed by a primary key, unique constraint, or suitable unique index. Without that identity rule, the database cannot know which row counts as already present.

Gotcha: an upsert can overwrite newer data just as atomically as it can write correct data. Update only the columns the incoming source owns, and add a timestamp or version condition when stale events can arrive late. It also does not make side effects idempotent: sending an email before every upsert still sends the email twice.