O'Reilly Report: Why Distributed SQL Is the Modern Foundation for AI. (Click for Free Download)

What Is an Upsert in SQL?

An upsert SQL operation inserts a new row if it doesn’t already exist, or updates the existing row if it does. The name is a portmanteau of “update” and “insert.” Instead of writing a conditional check and branching to either an INSERT or an UPDATE, an upsert handles both cases atomically in a single statement.

The problem it solves is practical: checking for a row’s existence before deciding whether to insert or update requires extra round trips, extra application logic, and introduces race conditions in concurrent environments. Upserts eliminate all of that.

How Does an Upsert Work?

When the database receives an upsert, it follows a straightforward sequence:

  1. Attempt to insert the row.
  2. If a conflict is detected on a primary key or unique constraint, apply the defined conflict action. Either update the existing row or skip it entirely.
  3. If no conflict exists, complete the insert normally.

The critical word in step two is “conflict.” A conflict in this context means a violation of a primary key or unique constraint, not just any data mismatch. The update branch fires only when the incoming row would duplicate a key that already exists. If the conflict condition never triggers, the row is inserted as new.

What Is the Upsert Syntax in PostgreSQL?

PostgreSQL does not have a standalone UPSERT keyword. Instead, the upsert vs insert SQL distinction is handled through the ON CONFLICT clause added to a standard INSERT statement. This is the PostgreSQL upsert syntax developers use in practice.

The two most common forms are insert-or-update and insert-or-ignore.

Insert or update — the incoming values replace or merge with the existing row:

INSERT INTO users (id, email, last_login)
VALUES (42, ‘user@example.com’, NOW())
ON CONFLICT (id)
DO UPDATE SET email = EXCLUDED.email, last_login = EXCLUDED.last_login;

Insert or ignore — if the row already exists, do nothing:

INSERT INTO users (id, email)
VALUES (42, ‘user@example.com’)
ON CONFLICT (id)
DO NOTHING;

The conflict target in parentheses, which is (id) in both examples, tells the database which constraint to check. If the incoming value for id already exists in the table, the conflict action fires.

What Does EXCLUDED Mean in ON CONFLICT?

EXCLUDED is a special table alias that refers to the row that failed to insert because of the conflict, the “rejected” row. In the DO UPDATE SET clause, EXCLUDED.column_name gives access to the values that were proposed for insertion.

This is what makes ON CONFLICT DO UPDATE flexible. You can accept the incoming value (SET email = EXCLUDED.email), keep the existing value (SET email = users.email), or apply logic that combines both (SET login_count = users.login_count + 1). The EXCLUDED alias gives you access to both sides of the conflict to decide which wins.

When Should You Use an Upsert?

Upserts are the right tool when your write logic depends on whether a row already exists. Common scenarios:

  • Data synchronization: When pulling incremental updates from an external source, upserts insert new records and update changed ones without a full table scan or manual existence check.
  • Event tracking: When recording user activity where a row may or may not exist yet, an upsert could help with a last_seen timestamp on every login.
  • Idempotent writes: When a pipeline may replay the same data, upserts make writes safe to repeat without producing duplicates.
  • Bulk loads: Batching multiple upserts in a single INSERT ON CONFLICT statement reduces round trips and improves throughput compared to executing individual INSERT and UPDATE operations sequentially.

The choice between DO NOTHING and DO UPDATE comes down to which data should win. Use ON CONFLICT DO NOTHING when the existing row is authoritative and incoming data is to be discarded on conflict. Use ON CONFLICT DO UPDATE when the incoming data should update the existing record or be merged into it.

For a deeper look at the transaction guarantees underlying these operations, see the ACID transactions key concept.

How Do Upserts Work in YugabyteDB?

YugabyteDB is a PostgreSQL-compatible, open source distributed SQL database. Its YSQL API supports INSERT … ON CONFLICT with the same syntax that PostgreSQL developers already know. There’s nothing new to learn for the statement itself.

One consideration specific to distributed systems: in a distributed database, an upsert must check for conflicts across shards before writing. YugabyteDB addresses this with a configuration parameter, yb_insert_on_conflict_read_batch_size, that batches the conflict-check reads when executing upserts at scale. This reduces network round trips significantly when running large, concurrent upsert distributed database workloads, an optimization that matters when individual round trips across distributed nodes add up.

Every upsert in YugabyteDB runs as a fully ACID transaction: atomic, consistent, isolated, and durable. Whether the operation results in an insert or an update, the outcome is always consistent across all distributed nodes. 

YugabyteDB Aeon has a free tier for experimenting with upserts in a live environment, and YugabyteDB can also be downloaded to run locally.