PostgreSQL Partial Indexes and UPSERT

Question

After googling a lot my question is described below:

CREATE TABLE security (
  id          SERIAL PRIMARY KEY,
  vendor      VARCHAR(20),
  external_id VARCHAR(20),
  extinct     BOOLEAN DEFAULT FALSE
);

CREATE UNIQUE INDEX unique_vendor ON security(vendor, extinct) where vendor is not null;
CREATE UNIQUE INDEX unique_external_id ON security(external_id, extinct) where external_id is not null;

Attempting to insert values:

insert into security (vendor, external_id, extinct) 
  values('Legion', 'LGNONE', false)
  ON CONFLICT(vendor, external_id, extinct) DO UPDATE
  SET vendor = 'Legion', external_id = 'LGNONE', extinct = false;

Results in:

[42P10] ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

Altho this works(per spec):

insert into security (vendor, external_id, extinct) 
    values('Legion', 'LGNONE', false)
    ON CONFLICT DO NOTHING;

PostgreSQL documentation stands that it should work

PostgreSQL v9.5

My aim is to find way to create unique index on this table on multiple nullable columns and update old rows with new ones on UPSERT

Answer 1

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_predicate
do 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.