PostgreSQLData Manipulation (DML)

How to UPSERT in PostgreSQL Using ON CONFLICT DO UPDATE

Atomically insert a new row or update an existing row if a unique constraint or primary key conflict is encountered.

Quick SQL Solution

Safe (Read / Non-destructive)PostgreSQLIntermediate

INSERT INTO users (id, name, login_count)
VALUES (1, 'Alice', 1)
ON CONFLICT (id)
DO UPDATE SET
  name = EXCLUDED.name,
  login_count = users.login_count + 1,
  updated_at = NOW();

When to use this scenario:Syncing external CRM data or tracking user session counters without throwing duplicate key errors or doing double SELECT/INSERT checks.

Step-by-Step Query Breakdown

1Basic Upsert with DO UPDATE

INSERT INTO user_settings (user_id, theme, updated_at)
VALUES (42, 'dark', NOW())
ON CONFLICT (user_id)
DO UPDATE SET
  theme = EXCLUDED.theme,
  updated_at = EXCLUDED.updated_at;

EXCLUDED references the proposed row that would have been inserted. If user_id 42 exists, theme is updated.

2Upsert with DO NOTHING (Ignore conflicts)

INSERT INTO tag_subscribers (tag_id, user_id)
VALUES (5, 42)
ON CONFLICT (tag_id, user_id)
DO NOTHING;

Silently ignores duplicate inserts without throwing error code 23505 (unique_violation).

3Conditional DO UPDATE with WHERE filter

INSERT INTO inventory (sku, stock_qty)
VALUES ('WIDGET-01', 10)
ON CONFLICT (sku)
DO UPDATE SET stock_qty = EXCLUDED.stock_qty
WHERE EXCLUDED.stock_qty > inventory.stock_qty;

Only updates the existing row if the incoming data meets a specific condition.

Critical SQL Pitfalls & Precautions

•ON CONFLICT requires an explicit unique index or primary key constraint on the conflicting target columns.

Frequently Asked Questions

PostgreSQL Upsert (ON CONFLICT) - Frequently Asked Questions

Common questions about transactional safety, indexing, and engine compatibility.

MySQL uses "INSERT INTO ... ON DUPLICATE KEY UPDATE name = VALUES(name)".

Related SQL Query Guides

Browse All SQL Recipes

ANSI SQL / UniversalSafe

How to Find and Delete Duplicate Rows in SQL

Identify rows with duplicate column values using GROUP BY and HAVING count(*) > 1, and safely remove duplicates using ROW_NUMBER() CTEs.

ANSI SQL / UniversalDestructive

DELETE vs TRUNCATE vs DROP in SQL: Differences Explained

Compare row-by-row logging (DELETE), fast table emptying with auto-increment reset (TRUNCATE), and complete table removal (DROP).

ANSI SQL / UniversalSafe

How to Calculate a Running Total (Cumulative Sum) in SQL

Compute cumulative running totals over time or partitioned groups using SUM() OVER (ORDER BY ...).

PostgreSQLSafe

How to Create an Index Concurrently in PostgreSQL (Zero Downtime)

Build a production database index on a table with millions of rows without locking out live INSERT, UPDATE, or DELETE operations.

ANSI SQL / UniversalReversible

How to UPDATE a Table From Another Table in SQL

Update values in one table based on matching columns or aggregations in a secondary table.

ANSI SQL / UniversalReversible

How to Reset Auto Increment / Sequence in MySQL & PostgreSQL

Reset the next auto-generated primary key ID back to 1 or synchronize it with the maximum existing ID in the table.