What is UPSERT (INSERT ON CONFLICT)?

Question

Accepted Answer

**UPSERT** means "insert, or update if it already exists." PostgreSQL implements this with **`INSERT ... ON CONFLICT`** — insert a row, but if it would violate a unique constraint, instead update the existing row (or do nothing). It handles the common "insert-or-update" need atomically.

## The problem UPSERT solves

```text
You want to insert a row, but it might already exist (by a unique key):
  ❌ a plain INSERT fails with a unique-violation error if it exists
  ❌ check-then-insert (SELECT, then INSERT or UPDATE) has a RACE CONDITION
     (another transaction could insert between your check and insert)
→ ON CONFLICT does it atomically in ONE statement (no race condition)
```

## ON CONFLICT DO UPDATE — insert or update

```sql
INSERT INTO users (email, name, login_count)
VALUES ('ann@x.com', 'Ann', 1)
ON CONFLICT (email)                  -- if a row with this email already exists...
DO UPDATE SET                        -- ...UPDATE it instead
  name = EXCLUDED.name,              -- EXCLUDED = the values you tried to insert
  login_count = users.login_count + 1;  -- can reference the existing row too
```

If inserting would conflict on the `email` unique constraint, Postgres updates the existing row instead. **`EXCLUDED`** refers to the values you attempted to insert, and you can reference the existing row (`users.login_count`) too — enabling logic like incrementing a counter.

## ON CONFLICT DO NOTHING — insert or skip

```sql
INSERT INTO users (email, name) VALUES ('ann@x.com', 'Ann')
ON CONFLICT (email) DO NOTHING;      -- if it exists, just skip (no error)
```

`DO NOTHING` silently skips conflicting rows — useful for "insert if not present" without errors.

## A common real use: counters and idempotent inserts

```sql
-- track page views — insert on first view, increment on subsequent
INSERT INTO page_views (page_id, views) VALUES (5, 1)
ON CONFLICT (page_id) DO UPDATE SET views = page_views.views + 1;
```

## Why it matters

UPSERT (`INSERT ... ON CONFLICT`) is an important, practically-valuable PostgreSQL feature that solves the very common "insert-or-update" need cleanly and **safely**, so understanding it is useful for real-world data operations.

The need — inserting a row that might already exist (by a unique key) — arises constantly (upserting records, counters, idempotent inserts, syncing data), and the naive alternatives are problematic: a plain INSERT fails with an error if the row exists, and the check-then-insert approach (SELECT then INSERT-or-UPDATE) has a **race condition** (another concurrent transaction could insert between your check and your insert, causing errors or lost updates). `ON CONFLICT` solves this **atomically in a single statement** — eliminating the race condition, which is its key correctness benefit for concurrent applications.

Understanding both forms — **`DO UPDATE`** (insert or update, with `EXCLUDED` referencing the attempted values and the ability to reference the existing row for logic like incrementing counters) and **`DO NOTHING`** (insert or skip silently) — covers the common patterns (upserting records, idempotent inserts, maintaining counters/aggregates).

Since insert-or-update is a frequent operation and doing it correctly and safely (atomically, without race conditions) matters for data integrity in concurrent applications, understanding UPSERT — what it solves, the atomicity/race-condition benefit, and the DO UPDATE/DO NOTHING forms — is valuable, frequently-applied PostgreSQL knowledge that provides a clean, safe solution to a common need that's otherwise error-prone, making it a practical, important feature for real applications.