sqlintermediate

UPSERT with ON CONFLICT

Insert or update rows atomically using PostgreSQL ON CONFLICT clause with partial indexes and conditions.

sqlPress ⌘/Ctrl + Shift + C to copy

-- Basic upsert: insert or update on unique constraint
INSERT INTO users (email, name, updated_at)
VALUES ('ada@example.com', 'Ada Lovelace', NOW())
ON CONFLICT (email)
DO UPDATE SET
  name = EXCLUDED.name,
  updated_at = EXCLUDED.updated_at;

-- Upsert with conditional update (only if data changed)
INSERT INTO products (sku, price, stock)
VALUES ('WIDGET-01', 29.99, 100)
ON CONFLICT (sku)
DO UPDATE SET
  price = EXCLUDED.price,
  stock = EXCLUDED.stock
WHERE products.price != EXCLUDED.price
   OR products.stock != EXCLUDED.stock;

-- Bulk upsert with RETURNING
INSERT INTO tags (name, slug)
VALUES
  ('JavaScript', 'javascript'),
  ('TypeScript', 'typescript'),
  ('Python', 'python')
ON CONFLICT (slug) DO NOTHING
RETURNING id, name, slug;

-- Upsert with composite key
INSERT INTO user_settings (user_id, key, value)
VALUES (1, 'theme', 'dark')
ON CONFLICT (user_id, key)
DO UPDATE SET value = EXCLUDED.value;