AdvancedAdvanced PostgreSQL

UPSERT with ON CONFLICT

The query

SQL

INSERT INTO employees (emp_id, first_name, last_name, email, salary)
VALUES (101, 'John', 'Doe', 'john.doe@company.com', 75000)
ON CONFLICT (email) DO UPDATE SET
  first_name = EXCLUDED.first_name,
  last_name  = EXCLUDED.last_name,
  salary     = EXCLUDED.salary,
  updated_at = NOW()
WHERE employees.salary != EXCLUDED.salary; -- only update if salary changed

Tested against PostgreSQL 16

Note

“UPSERT (ON CONFLICT) atomically inserts or updates. EXCLUDED refers to the proposed-insert row values.

Tables referenced

employees10 cols