Using INSERT ON CONFLICT as an alternative to Upsert in Postgres

Wanna try another way to "Upsert" (update or insert) without using the upsert query? Modify your insert query to include the on conflict clause.

However, Postgres may produce "No unique or exclusion constraint" error if you don't define the unique constraint satisfying some of it conditions (if your unique index is a partial one, the predicates you added to CREATE INDEX must be all provided in on conflict clause).

Please notice the where clauses in 2 queries below:

CREATE UNIQUE INDEX uniq_idx_company_personnel
  ON person(company_id, personnel_no) WHERE company_id > 0;

INSERT INTO person (company_id, personnel_no, name)
  VALUES (1, 1, "Boss")
  ON CONFLICT (company_id, personnel_no) WHERE company_id > 0
    DO UPDATE SET name = EXCLUDED.name;

Please see detailed explanation on how to use the Insert with "on conflict" and how to avoid "No unique or exclusion constraint" error in this interesting post.