0Pricing
SQL Academy · Lesson

UPSERT: ON CONFLICT DO UPDATE (PostgreSQL)

Implement idempotent inserts with ON CONFLICT DO UPDATE, use EXCLUDED, and choose the right conflict target.

UPSERT: ON CONFLICT DO UPDATE (PostgreSQL) is a free SQL Academy lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

What Is UPSERT?

UPSERT = INSERT if not exists, UPDATE if it does. PostgreSQL spells it INSERT ... ON CONFLICT.

ON CONFLICT DO NOTHING

Insert if missing, silently skip if duplicate:

INSERT INTO users (email, full_name)
VALUES ('alice@example.com', 'Alice')
ON CONFLICT (email) DO NOTHING;

ON CONFLICT DO UPDATE

Insert if missing, otherwise update specific columns:

INSERT INTO users (email, full_name, last_login)
VALUES ('alice@example.com', 'Alice', NOW())
ON CONFLICT (email)
DO UPDATE SET
  full_name  = EXCLUDED.full_name,
  last_login = EXCLUDED.last_login;

EXCLUDED: The Proposed Row

Inside DO UPDATE, EXCLUDED.col means "the value the INSERT tried to put in". The bare column name (or users.col) means the existing row.

ON CONFLICT (email)
DO UPDATE SET visits = users.visits + 1,
              last_seen = EXCLUDED.last_seen;

The Conflict Target

You must name a unique constraint or primary key:

ON CONFLICT (email)             -- column with UNIQUE
ON CONFLICT (user_id, day)      -- composite UNIQUE
ON CONFLICT ON CONSTRAINT users_email_key   -- by name

Conditional UPSERT (WHERE in DO UPDATE)

Update only when a condition holds:

INSERT INTO inventory (product_id, qty)
VALUES (42, 5)
ON CONFLICT (product_id)
DO UPDATE SET qty = inventory.qty + EXCLUDED.qty
WHERE inventory.qty + EXCLUDED.qty <= 1000;

Multi-Row UPSERT

Insert many rows; each row handled separately:

INSERT INTO page_views (path, day, count)
VALUES ('/home', CURRENT_DATE, 1), ('/about', CURRENT_DATE, 1)
ON CONFLICT (path, day)
DO UPDATE SET count = page_views.count + EXCLUDED.count;

RETURNING with UPSERT

Get back the resulting row (whether inserted or updated):

INSERT INTO users (email, full_name)
VALUES ('alice@example.com', 'Alice')
ON CONFLICT (email) DO UPDATE
  SET full_name = EXCLUDED.full_name
RETURNING id, full_name;

Idempotent Workflows

UPSERT makes retries safe. If your webhook fires twice, the second call updates the same row instead of duplicating.

MERGE (SQL Standard, PG15+)

PostgreSQL 15 added the SQL-standard MERGE, which can INSERT/UPDATE/DELETE in one statement:

MERGE INTO users AS u
USING (VALUES ('alice@example.com','Alice')) AS src(email, name)
ON u.email = src.email
WHEN MATCHED THEN UPDATE SET full_name = src.name
WHEN NOT MATCHED THEN INSERT (email, full_name) VALUES (src.email, src.name);

When NOT to UPSERT

UPSERT requires a unique constraint. If you don't have one, you usually want a separate SELECT then INSERT or UPDATE — but watch for race conditions; wrap in a transaction with appropriate locking.

Recap

ON CONFLICT is PostgreSQL's clean, atomic UPSERT.

  • DO NOTHING for "insert if absent"
  • DO UPDATE for true upsert
  • EXCLUDED = the proposed row
  • Always name a conflict target

Quick Check

In ON CONFLICT (email) DO UPDATE SET last_login = EXCLUDED.last_login, what does EXCLUDED refer to?

Frequently asked questions

Is the “UPSERT: ON CONFLICT DO UPDATE (PostgreSQL)” lesson free?

Yes — the full text of “UPSERT: ON CONFLICT DO UPDATE (PostgreSQL)” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “UPSERT: ON CONFLICT DO UPDATE (PostgreSQL)”?

Implement idempotent inserts with ON CONFLICT DO UPDATE, use EXCLUDED, and choose the right conflict target. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “UPSERT: ON CONFLICT DO UPDATE (PostgreSQL)” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. INSERT with Multiple Rows
  2. UPSERT: ON CONFLICT DO UPDATE (PostgreSQL)
  3. UPDATE with FROM and JOIN-style Updates
  4. DELETE with USING and Safe Patterns
← Back to SQL Academy