Use UPSERT for Insert-or-Update Operations

Why This Matters

Many applications need to insert a row if it doesn't exist, or update it if it does. Using separate INSERT and UPDATE statements creates race conditions and requires multiple round-trips to the database.

UPSERT operations are essential for:

• User preferences: Create or update user settings atomically
• Session data: Store or refresh session information
• Counters and metrics: Increment counters that may not exist yet
• Idempotent operations: Ensure operations can be safely retried
• Data synchronization: Sync external data without duplicates

The Problem

Using separate INSERT and UPDATE statements creates race conditions and requires multiple queries:

-- Application code: Check if exists, then insert or update
SELECT * FROM user_preferences WHERE user_id = 123;

if (exists) {
  UPDATE user_preferences 
  SET theme = 'dark', language = 'en'
  WHERE user_id = 123;
} else {
  INSERT INTO user_preferences (user_id, theme, language)
  VALUES (123, 'dark', 'en');
}

-- Problem: Race condition if two requests run concurrently
-- Both SELECT return no rows, both try INSERT, one fails
-- Also: 2-3 round-trips to database

This pattern is not atomic and can fail under concurrent load. It also requires multiple database round-trips.

The Solution

Use INSERT ... ON CONFLICT (UPSERT) to atomically insert or update:

-- Single atomic operation: insert or update
INSERT INTO user_preferences (user_id, theme, language)
VALUES (123, 'dark', 'en')
ON CONFLICT (user_id)
DO UPDATE SET
  theme = EXCLUDED.theme,
  language = EXCLUDED.language;

-- Result: Atomic operation, no race conditions
-- Single round-trip to database
-- Works correctly under concurrent load

The ON CONFLICT clause specifies which unique constraint to check. When a conflict occurs, DO UPDATE runs instead of failing.

For partial updates (only update specified columns), use EXCLUDED to reference the values that would have been inserted:

-- Update only specific columns on conflict
INSERT INTO user_preferences (user_id, theme, language, last_login)
VALUES (123, 'dark', 'en', NOW())
ON CONFLICT (user_id)
DO UPDATE SET
  theme = COALESCE(EXCLUDED.theme, user_preferences.theme),
  last_login = EXCLUDED.last_login;

-- COALESCE preserves existing theme if new value is NULL

Common Mistakes

• Not specifying conflict target: Always specify which unique constraint to check. Postgres will use the primary key by default, but being explicit is clearer.
• Updating all columns unnecessarily: Use COALESCE or conditional logic to only update columns that have new values.
• Ignoring return values: Use RETURNING to get the final row state and determine whether an insert or update occurred.
• Using DO NOTHING when updates are needed: ON CONFLICT DO NOTHING silently ignores conflicts. Use DO UPDATE when you need to update existing rows.

Advanced Patterns

Use RETURNING to determine if an insert or update occurred:

-- Return the final row and operation type
WITH upserted AS (
  INSERT INTO user_preferences (user_id, theme, language)
  VALUES (123, 'dark', 'en')
  ON CONFLICT (user_id)
  DO UPDATE SET
    theme = EXCLUDED.theme,
    language = EXCLUDED.language
  RETURNING *, (xmax = 0) AS inserted
)
SELECT * FROM upserted;

-- xmax = 0 means row was inserted, xmax > 0 means updated

For incrementing counters atomically:

-- Increment a counter, creating it if it doesn't exist
INSERT INTO page_views (page_id, view_count)
VALUES (456, 1)
ON CONFLICT (page_id)
DO UPDATE SET
  view_count = page_views.view_count + 1,
  last_viewed = NOW();