PostgreSQL中substring(enumColumn::TEXT from 'pattern')非IMMUTABLE的问题排查

你猜得太准啦！问题的核心确实是枚举类型转TEXT的强制转换（enumColumn::TEXT）不是IMMUTABLE函数，和substring本身或者正则匹配返回NULL的可能性完全无关。

为什么enum::TEXT是STABLE而非IMMUTABLE？ #

PostgreSQL里函数的稳定性分为三个关键等级：

• IMMUTABLE：无论何时调用，只要输入相同，结果就绝对相同，完全不依赖任何外部状态（比如系统参数、数据库元数据变化）。
• STABLE：在同一个事务内，输入相同结果不变，但如果数据库的元数据发生变化（比如修改枚举的显示标签），结果可能跟着改变。
• VOLATILE：每次调用结果都可能不同。

枚举类型的文本标签是可以通过ALTER TYPE ... RENAME VALUE命令修改的！比如你现在有个枚举值'OLD_STATUS'，之后把它改成'NEW_STATUS'，那同一个枚举值转TEXT的结果就变了。正因为PostgreSQL要预留这种修改的可能性，所以把enum::text的强制转换标记为STABLE，而不是IMMUTABLE。

当你把enum::text作为substring的参数时，整个表达式的稳定性会被拉低到STABLE——只要组合表达式里有一个输入是STABLE，整个表达式就无法达到IMMUTABLE等级，这就是你看到“generation expression is not immutable”错误的直接原因。

为什么TEXT列的情况没问题？ #

当源列本身就是TEXT类型时，输入到substring的参数是纯TEXT值，属于IMMUTABLE的输入。substring函数本身在输入都是IMMUTABLE的情况下，整个表达式自然就是IMMUTABLE的，所以不会触发错误。

解决方法 #

这里给你几个实用的方案，你可以根据自己的场景选择：

• 自定义IMMUTABLE枚举转TEXT函数（推荐，需提前约定）
  如果你能保证之后永远不会修改枚举的文本标签，那可以自己写一个强制标记为IMMUTABLE的转换函数：

  CREATE OR REPLACE FUNCTION enum_to_text(your_enum_type)
  RETURNS text
  LANGUAGE sql
  IMMUTABLE
  AS $$
  SELECT $1::text;
  $$;
  

  之后用这个函数替代直接强制转换：substring(enum_to_text(enumColumn) from '-(.+)')，这样整个表达式就会被PostgreSQL识别为IMMUTABLE，满足生成列的要求。
• 改用虚拟生成列（适合查询频率不高的场景）
  如果你用的是PostgreSQL 12及以上版本，虚拟生成列（VIRTUAL）可以接受STABLE的表达式，不需要IMMUTABLE。不过要注意虚拟生成列不会存储计算结果，每次查询时都会重新计算，适合数据量不大、查询频率低的场景。
• 提前固化枚举标签（最稳妥）
  在创建枚举类型时就确定好所有标签，并且在团队内约定永远不修改这些标签，这样用第一个方案的自定义函数就不会有任何隐患。

内容来源于stack exchange