PostgreSQL UPSERT and Conflict Resolution Patterns

Need to insert a row if it doesn't exist or update it if it does, without race conditions or multiple queries.

PostgreSQL ON CONFLICT patterns:

-- Basic upsert: insert or update
INSERT INTO products (sku, name, price, stock)
VALUES ('ABC123', 'Widget', 9.99, 100)
ON CONFLICT (sku) DO UPDATE SET
  name = EXCLUDED.name,
  price = EXCLUDED.price,
  stock = products.stock + EXCLUDED.stock,  -- Add to existing stock
  updated_at = NOW();

-- Insert or ignore (skip duplicates)
INSERT INTO tags (name)
VALUES ('javascript'), ('python'), ('rust')
ON CONFLICT (name) DO NOTHING;

-- Upsert with composite unique constraint
INSERT INTO user_roles (user_id, role_id, granted_by)
VALUES (1, 5, 'admin')
ON CONFLICT (user_id, role_id) DO UPDATE SET
  granted_by = EXCLUDED.granted_by;

-- Conditional upsert (only update if newer)
INSERT INTO cache (key, value, version)
VALUES ('config', '{"theme": "dark"}', 5)
ON CONFLICT (key) DO UPDATE SET
  value = EXCLUDED.value,
  version = EXCLUDED.version
WHERE EXCLUDED.version > cache.version;  -- Only if newer

-- Return the result (inserted or updated)
INSERT INTO users (email, name)
VALUES ('alice@example.com', 'Alice')
ON CONFLICT (email) DO UPDATE SET
  name = EXCLUDED.name
RETURNING id, email, (xmax = 0) AS was_inserted;
-- xmax = 0 means it was inserted, not updated

-- Bulk upsert
INSERT INTO inventory (product_id, warehouse_id, quantity)
VALUES
  (1, 'NYC', 50),
  (2, 'NYC', 30),
  (3, 'LAX', 100)
ON CONFLICT (product_id, warehouse_id) DO UPDATE SET
  quantity = EXCLUDED.quantity;

ON CONFLICT is atomic - no race conditions between checking existence and inserting. It replaces the anti-pattern of SELECT then INSERT/UPDATE which has a TOCTOU race.

Handling insert-or-update operations in PostgreSQL