sql - PostgreSQL 部分索引和 UPSERT

Question

经过大量谷歌搜索后，我的问题描述如下:

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;

尝试插入值:

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;

结果:

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

虽然这有效(根据规范):

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

我的目标是找到方法在该表的多个可空列上创建唯一索引，并在 UPSERT 上用新行更新旧行

Answer 1

conflict_target在 on conflict 中使用必须标识现有的唯一索引。你不能使用

on conflict (vendor, external_id, extinct)

因为你在三列上没有索引。 Postgres 并没有那么聪明地组合多个索引来满足您的冲突目标。

但是您可以像这样创建一个单独的部分索引:

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

现在您可以将这三列用作冲突目标:

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

注意特殊记录excluded 的使用。对于文档:

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.