UPSERT: INSERT ... ON CONFLICT

An upsert is "insert, but update instead if it already exists." PostgreSQL implements this with INSERT ... ON CONFLICT, which reacts when a row would violate a UNIQUE or PRIMARY KEY constraint.

Syntax

INSERT INTO table_name (col1, col2, ...)
VALUES (val1, val2, ...)
ON CONFLICT (conflict_column)
DO UPDATE SET col1 = EXCLUDED.col1, col2 = EXCLUDED.col2;

EXCLUDED refers to the row that would have been inserted — the new values you passed in.

Example

Assume email has a UNIQUE constraint:

INSERT INTO employees (name, email, salary)
VALUES ('Rahul Gupta', 'rahul@gmail.com', 65000)
ON CONFLICT (email)
DO UPDATE SET
  name = EXCLUDED.name,
  salary = EXCLUDED.salary;

• If no employee has that email yet → a new row is inserted.
• If an employee with that email already exists → their name and salary are updated instead.

DO NOTHING — Ignore Conflicts Silently

Sometimes you just want to skip duplicates without erroring or changing anything:

INSERT INTO employees (email, name)
VALUES ('rahul@gmail.com', 'Rahul Gupta')
ON CONFLICT (email) DO NOTHING;

Conditional Upsert

Only update if some extra condition holds — here, only overwrite the salary if the new one is higher:

INSERT INTO employees (email, name, salary)
VALUES ('rahul@gmail.com', 'Rahul Gupta', 70000)
ON CONFLICT (email)
DO UPDATE SET salary = EXCLUDED.salary
WHERE EXCLUDED.salary > employees.salary;

Common Errors & Troubleshooting

• ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification — the column(s) named in ON CONFLICT (...) must actually have a UNIQUE or PRIMARY KEY constraint (or matching index) — you can't target an arbitrary column.
• EXCLUDED used outside the DO UPDATE clause — EXCLUDED.column is only valid inside the SET expressions of DO UPDATE; it has no meaning anywhere else in the statement.
• Multiple rows in one INSERT conflicting with each other, not just the table — ON CONFLICT handles conflicts against existing table rows, not duplicate values within the same multi-row VALUES list — a batch insert with two identical keys in one statement will still error.

Key Takeaways

• ON CONFLICT only triggers when the insert would violate a UNIQUE/PRIMARY KEY constraint — the conflict target must be an indexed column.
• EXCLUDED.column refers to the value you tried to insert, inside the DO UPDATE clause.
• DO NOTHING silently skips the conflicting insert instead of updating or erroring.