Postgres INSERT ON CONFLICT DO UPDATE: The Upsert Guide
Master postgres insert on conflict do update: the atomic upsert pattern, EXCLUDED, conditional updates, and the Supabase RLS gotcha that breaks it.
"Insert this row, but if it already exists, update it instead" — the upsert — shows up everywhere: syncing a product catalog, saving user settings, importing a CSV twice without creating duplicates. The naive approach (SELECT, then INSERT or UPDATE) has a race condition: two concurrent requests can both SELECT, both decide "doesn't exist," and both INSERT. Postgres insert on conflict do update solves it atomically in a single statement. This guide builds the pattern from a plain insert to production-ready upserts, including the Supabase/RLS gotcha that silently breaks it. What you'll learn The postgres upsert syntax: ON CONFLICT DO NOTHING vs DO UPDATE What EXCLUDED really is (the row you *tried* to insert) Conditional updates with ON CONFLICT ... WHERE Multi-row upserts and what RETURNING gives you back The Supabase upsert bridge: client syntax plus the RLS policies it needs Every error message you'll meet, and what each one means Step 1 — Setup: a products table with a unique SKU Upserts need a conflict *target* — a unique constraint or index that defines "already exists." Our sku column has one: CREATE TABLE products ( id SERIAL PRIMARY KEY, sku TEXT UNIQUE NOT NULL, -- the conflict target name TEXT NOT NULL, price NUMERIC(10,2) NOT NULL, stock INT NOT NULL DEFAULT 0, updated_at TIMESTAMPTZ NOT NULL DEFAULT now() ); INSERT INTO products (sku, name, price, stock) VALUES ('KB-001', 'Mechanical Keyboard', 89.99, 12), ('MS-002', 'Wireless Mouse', 29.99, 40); Without that UNIQUE , there is no conflict to detect — Postgres will tell you so (see Common mistakes #1). Step 2 — postgres on conflict do nothing: skip duplicates The gentlest upsert: try the insert, and if the SKU already exists, silently skip it. No error, no…