PostgreSQL Partial Indexes and UPSERT

The conflict_target used in on conflict must identify an existing unique index. You cannot use

on conflict (vendor, external_id, extinct)

because you have no index on the three columns. Postgres is not so smart to combine multiple indexes to satisfy your conflict target.

You can however create a single partial index like this one:

create unique index unique_vendor_external_id     on security(vendor, external_id, extinct)     where coalesce(vendor, external_id) is not null;

Now you can use the three columns as a conflict target:

insert into security (vendor, external_id, extinct)     values('Legion', 'LGNONE', false)on conflict (vendor, external_id, extinct)          -- exact match to the index definition     where coalesce(vendor, external_id) is not null -- obligatory index_predicatedo update set    vendor = excluded.vendor,    external_id = excluded.external_id,    extinct = excluded.extinct

Note the use of the special record excluded. For the documentation:

The SET and WHERE clauses in ON CONFLICT DO UPDATE have access to the existing row using the table's name (or an alias), and to rows proposed for insertion using the special excluded table.