UPSERT with ON CONFLICT

UPSERT means "insert if the row doesn't exist, update if it does." PostgreSQL implements this with INSERT ... ON CONFLICT, introduced in PostgreSQL 9.5. It is one of the most useful features for building idempotent data pipelines, counters, caches, and leaderboards.

The Problem

Without UPSERT, you'd need two round-trips to the database:

-- The naive (wrong) way — race condition!
SELECT COUNT(*) FROM user_stats WHERE user_id = 1;
-- ... if 0, INSERT; if > 0, UPDATE
-- Two concurrent transactions can both see 0 and both INSERT → duplicate key error

ON CONFLICT solves this atomically.

Basic Syntax

INSERT INTO table (col1, col2, ...)
VALUES (val1, val2, ...)
ON CONFLICT (conflict_target) DO action;

The conflict target is a column (or columns) with a unique constraint or primary key.

ON CONFLICT DO NOTHING

Silently ignore the insert if a conflict occurs:

CREATE TABLE tags (
    id   SERIAL PRIMARY KEY,
    name TEXT UNIQUE
);

-- Try to insert 'postgresql' — if it exists, do nothing
INSERT INTO tags (name) VALUES ('postgresql')
ON CONFLICT (name) DO NOTHING;

-- Insert multiple, ignore any that already exist
INSERT INTO tags (name) VALUES
    ('postgresql'), ('sql'), ('database'), ('postgresql')  -- duplicate in list
ON CONFLICT (name) DO NOTHING
RETURNING *;  -- only returns newly inserted rows

ON CONFLICT DO UPDATE — The Full UPSERT

When a conflict occurs, update the existing row using the special EXCLUDED pseudo-table, which contains the values you tried to INSERT:

CREATE TABLE user_settings (
    user_id    INTEGER PRIMARY KEY,
    theme      TEXT DEFAULT 'light',
    language   TEXT DEFAULT 'en',
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Insert or update user settings
INSERT INTO user_settings (user_id, theme, language)
VALUES (42, 'dark', 'fr')
ON CONFLICT (user_id) DO UPDATE SET
    theme      = EXCLUDED.theme,
    language   = EXCLUDED.language,
    updated_at = NOW();

EXCLUDED.theme refers to the value 'dark' that was in the attempted INSERT. If user 42 already exists, their settings are updated to the new values.

The EXCLUDED Table

EXCLUDED is a virtual table representing the row that would have been inserted but was rejected due to the conflict:

INSERT INTO products (sku, name, price, stock)
VALUES ('ABC-123', 'Widget', 29.99, 100)
ON CONFLICT (sku) DO UPDATE SET
    name   = EXCLUDED.name,   -- take the new name
    price  = EXCLUDED.price,  -- take the new price
    stock  = products.stock + EXCLUDED.stock;  -- ADD to existing stock
    -- products.stock = existing value, EXCLUDED.stock = the value we tried to insert

The existing row is referenced via the table name (products.stock), while EXCLUDED.stock is the incoming value.

Conflict Targets

Single Column

ON CONFLICT (user_id) DO UPDATE SET ...

Multiple Columns (Composite Unique Key)

CREATE TABLE monthly_stats (
    user_id   INTEGER,
    month     DATE,
    page_views INTEGER,
    PRIMARY KEY (user_id, month)
);

INSERT INTO monthly_stats (user_id, month, page_views)
VALUES (1, '2024-01-01', 150)
ON CONFLICT (user_id, month) DO UPDATE SET
    page_views = monthly_stats.page_views + EXCLUDED.page_views;

Named Constraint

-- Use the constraint name instead of column names
ON CONFLICT ON CONSTRAINT monthly_stats_pkey DO UPDATE SET ...

Common UPSERT Patterns

Pattern 1: Counter / Accumulator

-- Increment a page view counter atomically
INSERT INTO page_views (url, view_count)
VALUES ('/home', 1)
ON CONFLICT (url) DO UPDATE SET
    view_count = page_views.view_count + 1;

Pattern 2: Leaderboard Score

CREATE TABLE leaderboard (
    player_id  INTEGER PRIMARY KEY,
    username   TEXT,
    high_score INTEGER
);

-- Insert new score; only update if new score is higher
INSERT INTO leaderboard (player_id, username, high_score)
VALUES (7, 'alice', 9500)
ON CONFLICT (player_id) DO UPDATE SET
    high_score = GREATEST(leaderboard.high_score, EXCLUDED.high_score),
    username   = EXCLUDED.username;
-- GREATEST() returns the larger of the two values

Pattern 3: Idempotent Data Pipeline

When loading data from an external source (ETL), you want re-runs to be safe:

-- Sync a product catalog — safe to run multiple times
INSERT INTO products (external_id, name, price, category, last_synced)
SELECT
    p.external_id,
    p.name,
    p.price,
    p.category,
    NOW()
FROM staging_products p
ON CONFLICT (external_id) DO UPDATE SET
    name        = EXCLUDED.name,
    price       = EXCLUDED.price,
    category    = EXCLUDED.category,
    last_synced = EXCLUDED.last_synced;

Pattern 4: Cache Population

CREATE TABLE geocode_cache (
    address    TEXT PRIMARY KEY,
    latitude   DOUBLE PRECISION,
    longitude  DOUBLE PRECISION,
    cached_at  TIMESTAMPTZ DEFAULT NOW()
);

-- Store a geocode result, skip if already cached
INSERT INTO geocode_cache (address, latitude, longitude)
VALUES ('123 Main St', 37.7749, -122.4194)
ON CONFLICT (address) DO NOTHING;

-- Or update the cache if older than 30 days
INSERT INTO geocode_cache (address, latitude, longitude, cached_at)
VALUES ('123 Main St', 37.7749, -122.4194, NOW())
ON CONFLICT (address) DO UPDATE SET
    latitude   = EXCLUDED.latitude,
    longitude  = EXCLUDED.longitude,
    cached_at  = EXCLUDED.cached_at
WHERE geocode_cache.cached_at < NOW() - INTERVAL '30 days';
-- The WHERE clause on DO UPDATE makes it conditional!

Partial Index Conflicts

If your unique constraint is a partial index (with a WHERE clause), you must specify the same predicate in ON CONFLICT:

-- Partial unique index: one active session per user
CREATE UNIQUE INDEX one_active_session
ON sessions (user_id)
WHERE status = 'active';

-- Conflict target must match the partial index
INSERT INTO sessions (user_id, status, token)
VALUES (42, 'active', 'abc123')
ON CONFLICT (user_id) WHERE status = 'active' DO UPDATE SET
    token      = EXCLUDED.token,
    created_at = NOW();

Using RETURNING with UPSERT

-- Know whether a row was inserted or updated
INSERT INTO products (sku, name, price)
VALUES ('XYZ-999', 'New Gadget', 89.99)
ON CONFLICT (sku) DO UPDATE SET
    price = EXCLUDED.price
RETURNING
    id,
    name,
    price,
    (xmax = 0) AS was_inserted;  -- xmax = 0 means freshly inserted

The xmax system column is 0 for newly inserted rows and non-zero for updated rows — a useful trick to tell whether the UPSERT inserted or updated.