PostgreSQL 部分唯一索引和 upsert [英] PostgreSQL partial unique index and upsert

问题描述

我正在尝试对具有部分唯一索引的表进行更新插入

I'm trying to do an upsert to a table that has partial unique indexes

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 不为空，只有 q 或 r 之一可以为空.

In plain terms, p is not null and only one of q or r can be null.

重复插入会按预期抛出约束违规

Duplicate inserts throw constraint violations as expected

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 使用唯一约束时

However, when I'm trying to use the unique constraint for an 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) 已经存在.

ERROR: duplicate key value violates unique constraint "test_pr_idx" DETAIL: Key (p, r)=(p, r) already exists.

但我希望 on conflict 子句能够捕获它并进行更新.

But I'd expect the on conflict clause to catch it and do the update.

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

What am I doing wrong? Should I be using an index_predicate?

index_predicate用于允许推断部分唯一索引.任何满足谓词的索引(实际上不必是部分的索引)可以推断.遵循 CREATE INDEX 格式.https://www.postgresql.org/docs/9.5/static/sql-insert.html

index_predicate Used to allow inference of partial unique indexes. Any indexes that satisfy the predicate (which need not actually be partial indexes) can be inferred. Follows CREATE INDEX format. https://www.postgresql.org/docs/9.5/static/sql-insert.html

推荐答案

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

I don't think it's possible to use multiple partial indexes as a conflict target. You should try to achieve the desired behaviour using a single index. The only way I can see is to use a unique index on expressions:

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, ''));

现在所有三个测试(执行两次)都违反了相同的索引:

Now all three tests (executed twice) violate the same index:

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

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

In the insert command you should pass the expressions used in the index definition:

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;

这篇关于PostgreSQL 部分唯一索引和 upsert的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！