PostgreSQL12使用ON CONFLICT处理site_id空/非空冲突避免重复插入

问题根因 #

现有配置存在三处核心错误，导致逻辑不生效：

• 第一个唯一索引的谓词site_id IS NOT NULL OR site_id IS NULL为恒真条件，等价于对全表创建4列普通唯一索引。PostgreSQL默认B树唯一索引不会将两个NULL值判定为相等，因此site_id为NULL的行无法触发该索引的冲突校验，直接导致重复插入。
• 单条ON CONFLICT语句仅能匹配一个唯一约束/索引，同时ON CONFLICT附带的WHERE谓词必须和对应部分索引的定义完全一致，现有语句的谓词与索引定义不匹配，因此抛出约束不匹配错误。
• 两个自定义索引存在判定范围重叠，无法同时被单条插入语句引用。

正确实现步骤 #

不需要拆分两条插入语句，通过支持NULL等值判定的唯一索引即可单条语句兼容所有场景：

1. 清理错误索引，创建兼容NULL的唯一索引 #

-- 删除之前创建的无效索引
DROP INDEX IF EXISTS candidates_4col_uni_idx;
DROP INDEX IF EXISTS candidates_3col_uni_idx;

-- 创建表达式唯一索引，将NULL值转换为业务不可能出现的固定占位值
-- 若业务中site_id可能存储空字符串，可替换为其他不可能出现的值，如'##NULL_PLACEHOLDER##'
CREATE UNIQUE INDEX candidates_day_study_site_status_uni_idx 
ON candidates ("day", study_id, COALESCE(site_id, ''), status);

2. 编写统一的插入冲突更新语句 #

ON CONFLICT后跟随的表达式必须和索引定义完全一致，即可同时兼容site_id为NULL和非NULL的冲突场景：

INSERT INTO candidates ("day", study_id, site_id, status, total, current)
VALUES ('2020-01-01T00:00:00.000Z', 'ABC', NULL, 'INCOMPLETE', 4, 4)
ON CONFLICT ("day", study_id, COALESCE(site_id, ''), status)
DO UPDATE SET
  total = EXCLUDED.total,
  current = EXCLUDED.current;

可选优化方案 #

如果业务允许调整字段约束，可直接给site_id设置非空约束，将原本需要存NULL的场景统一存入空字符串等固定占位值，即可直接使用普通多列唯一约束，无需依赖表达式索引，逻辑更易维护。

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