UPSERT with ON CONFLICT

7 min read

UPSERT with ON CONFLICT

An UPSERT inserts a row if it does not exist, or updates it if it does — all in a single atomic statement. Postgres implements this with the ON CONFLICT clause on INSERT.

sql

-- ON CONFLICT DO NOTHING — silently ignore duplicate inserts
INSERT INTO users (email, full_name)
VALUES ('alice@example.com', 'Alice')
ON CONFLICT (email) DO NOTHING;

-- ON CONFLICT DO UPDATE — update specific columns on conflict
INSERT INTO users (email, full_name, updated_at)
VALUES ('alice@example.com', 'Alice Smith', NOW())
ON CONFLICT (email) DO UPDATE
  SET
    full_name  = EXCLUDED.full_name,
    updated_at = EXCLUDED.updated_at;
-- EXCLUDED refers to the row that was proposed for insertion

The EXCLUDED pseudo-table gives you access to the values that would have been inserted. This lets you selectively update only the columns that changed.

sql

-- Upsert page view counts
INSERT INTO page_views (slug, count, last_seen)
VALUES ('/blog/postgres-guide', 1, NOW())
ON CONFLICT (slug) DO UPDATE
  SET
    count     = page_views.count + EXCLUDED.count,
    last_seen = EXCLUDED.last_seen;

-- ON CONFLICT on a composite unique constraint
INSERT INTO user_roles (user_id, role_id)
VALUES (1, 3)
ON CONFLICT (user_id, role_id) DO NOTHING;

The ON CONFLICT target must match an existing unique constraint or unique index exactly. If Postgres cannot identify the constraint from the column list you provide, it will raise an error.

Ready to test yourself?

Practice PostgreSQL— quiz & coding exercises