PostgreSQL中如何正确定义GENERATED类型的NUMRANGE生成列？

报错核心原因 #

PostgreSQL 12及更高版本完全支持NUMRANGE类型的STORED生成列，你遇到ERROR 42601语法错误是两个写法问题导致的：

• GENERATED ALWAYS AS后的表达式必须用外层括号完整包裹，这是PG生成列的强制语法要求
• 部分写法中类型转换的双冒号::漏写为单冒号:，字符串字面量写法无法识别字段变量

正确写法 #

推荐直接用numrange内置函数构造区间，语法最清晰：

CREATE TABLE lh  (
   l   NUMERIC,
   h   NUMERIC,
   lhr NUMRANGE GENERATED ALWAYS AS (numrange(l,h)) STORED
);

如果需要指定区间开闭规则，可传入第三个参数，示例为左闭右闭区间：

lhr NUMRANGE GENERATED ALWAYS AS (numrange(l,h, '[]')) STORED

你第二种写法的逻辑也是正确的，补上外层括号即可运行：

lhr NUMRANGE GENERATED ALWAYS AS ((l,h)::NUMRANGE) STORED

额外说明 #

• 生成列的值由数据库自动维护，不支持手动INSERT/UPDATE指定值
• PostgreSQL 11及更早版本不支持GENERATED STORED列，如使用低版本可改用触发器实现相同逻辑

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