如何将多个JSONB列合并为PostgreSQL生成列？

问题场景 #

针对单个JSONB列，执行以下添加生成列的SQL语句可以正常运行：

alter table r 
add column txt TEXT GENERATED ALWAYS as (
         doc->'contact'->>'name'
        ) stored;

但尝试拼接两个JSON提取结果时执行失败，报错信息为：SQL Error [42883]: ERROR: operator does not exist: text -> unknown
报错的SQL语句如下：

alter table r 
add column txt TEXT GENERATED ALWAYS as (
         doc->'contact'->>'name' || ' ' || doc->'contact'->>'phone'
        ) stored;

问题原因 #

PostgreSQL中字符串拼接运算符||的优先级高于JSON路径运算符->和->>，导致SQL解析时会先执行'name' || ' '，将其作为文本后再尝试执行->操作，而文本类型并不支持->运算符，因此触发错误。

正确解决方案 #

给每个JSON路径提取的表达式加上括号，强制改变运算顺序，确保先完成JSON字段的提取，再执行字符串拼接：

alter table r 
    add column txt TEXT GENERATED ALWAYS as (
             (doc->'contact'->>'name') || ' ' || (doc->'contact'->>'phone')
            ) stored;

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