PostgreSQL UPSERT: INSERT ON CONFLICT Explained

Posted by Kyle Hankinson July 8, 2022

---

An upsert inserts a row if it does not exist, or updates it if it does. PostgreSQL implements this with the INSERT ... ON CONFLICT syntax, available since PostgreSQL 9.5.

Basic Upsert

Suppose you have a settings table with a unique key column:

CREATE TABLE settings (
    key   TEXT PRIMARY KEY,
    value TEXT NOT NULL
);

To insert a setting or update it if the key already exists:

INSERT INTO settings (key, value)
VALUES ('theme', 'dark')
ON CONFLICT (key)
DO UPDATE SET value = EXCLUDED.value;

The EXCLUDED keyword refers to the row that was proposed for insertion. So EXCLUDED.value is 'dark' in this example.

ON CONFLICT DO NOTHING

If you just want to silently skip duplicates without updating anything:

INSERT INTO settings (key, value)
VALUES ('theme', 'dark')
ON CONFLICT (key)
DO NOTHING;

This is useful for idempotent inserts — running the same statement multiple times has no effect after the first.

Upserting Multiple Rows

You can upsert many rows in a single statement:

INSERT INTO settings (key, value)
VALUES
    ('theme', 'dark'),
    ('language', 'en'),
    ('timezone', 'UTC')
ON CONFLICT (key)
DO UPDATE SET value = EXCLUDED.value;

Using a Constraint Name

Instead of specifying columns, you can reference a named constraint:

INSERT INTO settings (key, value)
VALUES ('theme', 'dark')
ON CONFLICT ON CONSTRAINT settings_pkey
DO UPDATE SET value = EXCLUDED.value;

This is useful when the conflict target involves a partial index or an expression.

Conditional Updates

You can add a WHERE clause to only update under certain conditions:

INSERT INTO settings (key, value)
VALUES ('theme', 'dark')
ON CONFLICT (key)
DO UPDATE SET value = EXCLUDED.value
WHERE settings.value <> EXCLUDED.value;

This avoids unnecessary writes when the value has not actually changed.

Comparison with MySQL

MySQL uses a different syntax for the same operation — INSERT ... ON DUPLICATE KEY UPDATE:

INSERT INTO settings (`key`, value)
VALUES ('theme', 'dark')
ON DUPLICATE KEY UPDATE value = VALUES(value);

PostgreSQL's ON CONFLICT is more flexible because you can specify exactly which constraint triggers the upsert, and you can use DO NOTHING for silent skips.

Using SQLPro Studio

SQLPro Studio supports both PostgreSQL and MySQL, so you can write and test upsert queries for either database in the same app. The syntax highlighting will correctly handle both ON CONFLICT and ON DUPLICATE KEY UPDATE syntax.

---