PostgreSQL 部分唯一索引和 upsert

Question

我正在尝试对具有部分唯一索引的表进行upsert操作

create table test (
    p text not null,
    q text,
    r text,
    txt text,
    unique(p,q,r)
);

create unique index test_p_idx on test(p) where q is null and r is null;
create unique index test_pq_idx on test(p, q) where r IS NULL;
create unique index test_pr_idx on test(p, r) where q is NULL;

简单来说，p 不是 null，并且只有 q 或 r 中的一个可以为 null。

重复插入会如预期般引发约束违规。

insert into test(p,q,r,txt) values ('p',null,null,'a'); -- violates test_p_idx
insert into test(p,q,r,txt) values ('p','q',null,'b'); -- violates test_pq_idx
insert into test(p,q,r,txt) values ('p',null, 'r','c'); -- violates test_pr_idx

然而，当我尝试对upsert使用唯一约束时。

insert into test as u (p,q,r,txt) values ('p',null,'r','d') 
on conflict (p, q, r) do update 
set txt = excluded.txt

尽管如此，它仍然会抛出约束违规的错误。

错误：重复键违反唯一约束 "test_pr_idx" 详细信息：(p, r)=(p, r) 已经存在。

但是我希望 on conflict 子句能够捕获并执行更新操作。

我做错了什么？我应该使用 index_predicate 吗？

index_predicate 用于允许推断部分唯一索引。满足谓词条件的任何索引（实际上不需要是部分索引）都可以被推断。遵循 CREATE INDEX 格式。 https://www.postgresql.org/docs/9.5/static/sql-insert.html

- ubi

---

https://dev59.com/06Xja4cB1Zd3GeqPYu1G#46728249 - user330315

可能是在Postgresql中执行upsert时未使用部分索引的ON CONFLICT子句的重复问题。 - Eelke

Answer 1

我认为不可能将多个部分索引用作冲突目标。您应该尝试使用单个索引来实现所需的行为。我唯一能想到的方法是在表达式上使用唯一索引：

drop table if exists test;
create table test (
    p text not null,
    q text,
    r text,
    txt text
);

create unique index test_unique_idx on test (p, coalesce(q, ''), coalesce(r, ''));

现在所有三个测试（每个测试执行两次）都违反了相同的索引：

insert into test(p,q,r,txt) values ('p',null,null,'a'); -- violates test_unique_idx
insert into test(p,q,r,txt) values ('p','q',null,'b');  -- violates test_unique_idx
insert into test(p,q,r,txt) values ('p',null, 'r','c'); -- violates test_unique_idx

在插入命令中，您应该传递索引定义中使用的表达式：

insert into test as u (p,q,r,txt) 
values ('p',null,'r','d') 
on conflict (p, coalesce(q, ''), coalesce(r, '')) do update 
set txt = excluded.txt;

- klin