PostgreSQL partial unique index and upsert
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_idxinsert into test(p,q,r,txt) values ('p','q',null,'b'); -- violates test_unique_idxinsert into test(p,q,r,txt) values ('p',null, 'r','c'); -- violates test_unique_idxIn 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;