如何为Postgres物化视图保证唯一的int或bigint键

普通视图实现方案 #

普通视图为实时计算结果，无物理存储，固定ID必须完全依赖源表的唯一字段组合，有两种低维护成本的实现方式：

• 文本拼接方案：在两个整数字段中间加入不会出现在数值中的分隔符直接拼接，完全避免你提到的拼接碰撞问题，示例逻辑为a || '_' || b，(52,6)会生成52_6，(5,26)会生成5_26，唯一且逻辑极简，适合接受文本类型ID的场景。
• 数值类型ID方案：如果必须使用数值类型ID，无需复杂的数学配对计算，直接利用位运算拼接即可：如果两个字段都是32位INT类型，使用(a::bigint << 32) | (b::bigint & 0xFFFFFFFF)即可生成唯一的BIGINT值，本质是将第一个整数存入BIGINT的高32位、第二个存入低32位，逻辑清晰，补充注释后可维护性极高。

row_number()属于动态计算的窗口函数，结果完全依赖排序规则，每次查询/刷新都会变化，本身就不适用于生成固定ID。

物化视图实现方案 #

物化视图有物理存储，完全可以实现刷新后ID不变的需求：

• 直接使用上述视图的固定ID生成逻辑，创建物化视图时将生成的ID固化存储，同时添加唯一约束即可，示例代码：

CREATE MATERIALIZED VIEW your_mv AS
SELECT 
  (a::bigint << 32) | (b::bigint & 0xFFFFFFFF) AS fixed_unique_id,
  -- 其余所需字段
FROM your_source_table;

-- 为固定ID添加唯一索引
CREATE UNIQUE INDEX idx_your_mv_uid ON your_mv(fixed_unique_id);

只要源表中对应行的两个整数字段组合不变，刷新物化视图时该行的ID就不会发生变化。

• 若业务不强制要求单字段ID，直接为物化视图添加复合主键是成本最低的方案：

ALTER MATERIALIZED VIEW your_mv ADD CONSTRAINT pk_your_mv PRIMARY KEY (a,b);

绝大多数ORM和上层应用都支持复合主键的使用。

替代方案说明 #

如果你的场景需要支持行级事务更新、频繁的单属性修改，直接使用普通表存储数据是更合理的选择，你的备选方案具备可行性。

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