PostgreSQL 如何实现符合跨房屋重名规则的唯一约束（支持upsert）

普通的(house_id, dogname)联合唯一约束仅能满足「同一house内名称唯一」的基础规则，无法识别跨主/非主house的同名冲突；同时触发器方案无法被ON CONFLICT子句识别，不满足upsert的使用需求。利用PostgreSQL的排他约束（Exclusion Constraint）可以完美适配这套规则，且完全支持upsert的冲突探测。

实现步骤 #

1. 安装依赖扩展 #

排他约束基于GiST索引实现，普通等值匹配的GiST支持需要依赖官方扩展btree_gist：

CREATE EXTENSION IF NOT EXISTS btree_gist;

2. 核心约束创建 #

我们为每一行数据构造冲突匹配范围，配合排他约束的重叠判断逻辑，覆盖所有校验规则：

• 当house_id = 0（主房屋）时，设置冲突范围为全体整数域：意味着只要任意其他行的dogname与该行相同，无论该行属于哪个非主house，都会被判定为冲突。
• 当house_id != 0（非主房屋）时，设置冲突范围为仅包含自身house_id的单个值范围：意味着只有同house下的同dogname行，才会被判定为冲突，其他非主house的同名行不触发冲突。

最终的排他约束创建语句：

ALTER TABLE dogs
ADD CONSTRAINT dogs_dogname_conflict_rule
EXCLUDE USING gist (
  dogname WITH =,
  (
    CASE 
      WHEN house_id = 0 THEN int4range(NULL, NULL, '()')
      ELSE int4range(house_id, house_id, '[]')
    END
  ) WITH &&
);

规则验证（完全匹配示例场景） #

逐行对照示例数据的校验逻辑，所有场景的判定结果均符合业务要求：

• 插入house_id=0, dogname=Fido、house_id=0, dogname=Rover、house_id=1, dogname=Shep：无冲突，允许写入。
• 再次插入house_id=1, dogname=Shep：与已有house_id=1的Shep行名称相同，两个非主行的单个值范围重叠，拒绝写入。
• 插入house_id=2, dogname=Shep：与house_id=1的Shep行名称相同，但两个非主行的单个值范围不重叠，无冲突，允许写入。
• 插入house_id=2, dogname=Fido：与主房屋的Fido行名称相同，非主行的范围与主房屋的全域范围重叠，拒绝写入。

注：主房屋内部的同名场景同样会被正确校验：两个主房屋的同名行都会拥有全域冲突范围，名称相同则必然范围重叠，触发冲突，满足同一house内名称唯一的规则。

Upsert适配说明 #

该排他约束是表级的正式约束，完全可以被ON CONFLICT子句识别，upsert写法示例：

INSERT INTO dogs (dog_id, house_id, dogname)
VALUES (10, 1, 'Shep')
ON CONFLICT ON CONSTRAINT dogs_dogname_conflict_rule
DO UPDATE SET dog_id = EXCLUDED.dog_id;

注意事项 #

• 若house_id字段为bigint类型，将语句中的int4range替换为int8range即可，逻辑完全一致。
• 由于索引基于GiST范围类型实现，索引体积会略大于普通btree唯一索引，但整体性能开销在常规业务可接受范围内，且完全避免了并发写入下的校验漏洞。

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