PostgreSQL复合重叠唯一键ON CONFLICT报错问题求解

报错原因 #

你编写的ON CONFLICT (tok, sid, aid, qtt, hash)指定的列组合没有对应的唯一约束，你的表仅包含两个独立的复合唯一约束：(tok, sid, aid)和(sid, qtt, hash)，PostgreSQL无法找到匹配的约束，因此抛出错误。

可行解决方案 #

以下两种方案均能实现单条Upsert语句处理两种唯一键冲突的需求，且保持动态查询特性：

方案一：添加排除约束（推荐） #

排除约束可定义复杂冲突规则，覆盖你的两种唯一约束场景：

-- 先给原有唯一约束命名（若创建表时未指定）
ALTER TABLE list DROP CONSTRAINT IF EXISTS list_tok_sid_aid_key;
ALTER TABLE list ADD CONSTRAINT list_tok_sid_aid_key UNIQUE (tok, sid, aid);

ALTER TABLE list DROP CONSTRAINT IF EXISTS list_sid_qtt_hash_key;
ALTER TABLE list ADD CONSTRAINT list_sid_qtt_hash_key UNIQUE (sid, qtt, hash);

-- 添加排除约束，涵盖两种冲突情况
ALTER TABLE list ADD CONSTRAINT list_upsert_excl
EXCLUDE USING btree (
  (CASE 
    WHEN tok IS NOT NULL AND aid IS NOT NULL THEN (tok, sid, aid)
    WHEN qtt IS NOT NULL AND hash IS NOT NULL THEN (sid, qtt, hash)
    ELSE (sid) -- sid为NOT NULL，兜底避免全空场景
  END) WITH =
);

修改后的插入语句：

INSERT INTO list (tok, sid, aid, qtt, hash, p)
VALUES ($1, $2, $3, $4, $5, $6)
ON CONFLICT ON CONSTRAINT list_upsert_excl
DO UPDATE SET p = $6;

方案二：创建表达式唯一索引 #

通过表达式索引合并两种唯一约束逻辑，让ON CONFLICT可以匹配该索引：

CREATE UNIQUE INDEX list_upsert_idx ON list (
  CASE
    WHEN tok IS NOT NULL AND aid IS NOT NULL THEN (tok, sid, aid)
    WHEN qtt IS NOT NULL AND hash IS NOT NULL THEN (sid, qtt, hash)
    ELSE (sid)
  END
);

对应的插入语句：

INSERT INTO list (tok, sid, aid, qtt, hash, p)
VALUES ($1, $2, $3, $4, $5, $6)
ON CONFLICT USING INDEX list_upsert_idx
DO UPDATE SET p = $6;

两种方案均符合你“只会匹配其中一组唯一键”的假设，若插入行同时触发两种约束冲突（如tok, aid, qtt, hash均非空且同时匹配两组约束），也能正常执行Upsert逻辑。

内容的提问来源于stack exchange，提问作者bouncytorch