PostgreSQL UPSERT (INSERT ON CONFLICT) patterns

Need to insert a row if it doesn't exist, or update it if it does, atomically.

Use INSERT ... ON CONFLICT for atomic upserts:

-- Simple upsert: insert or update
INSERT INTO users (email, name, login_count)
VALUES ('alice@example.com', 'Alice', 1)
ON CONFLICT (email)
DO UPDATE SET
  name = EXCLUDED.name,
  login_count = users.login_count + 1,
  last_login = NOW();

-- Insert or ignore (do nothing on conflict)
INSERT INTO tags (name)
VALUES ('python'), ('rust'), ('go')
ON CONFLICT (name) DO NOTHING;

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

-- Conditional upsert (only update if value changed)
INSERT INTO prices (product_id, price, updated_at)
VALUES (42, 29.99, NOW())
ON CONFLICT (product_id)
DO UPDATE SET
  price = EXCLUDED.price,
  updated_at = EXCLUDED.updated_at
WHERE prices.price != EXCLUDED.price;

-- Returning the result
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;

ON CONFLICT provides atomic upsert operations, eliminating race conditions that exist with SELECT-then-INSERT approaches.

PostgreSQL tables that need idempotent insert/update operations