在PostgreSQL中创建计算列时触发‘生成表达式非不可变’错误

问题原因 #

你使用的to_timestamp函数属于stable类型（结果可能受数据库时区等环境设置影响），而存储类型的生成列要求表达式必须是immutable（结果仅由输入参数决定，不受外部环境变化影响），因此触发报错。

解决方案 #

方案一：直接转换为time类型计算（简洁推荐） #

利用文本转time类型的转换是immutable特性，直接计算时间差：

alter table attendance
add column totalhours text generated always as (
  (cast(check_out as time) - cast(check_in as time))::text
) stored;

• 原理：cast(check_in as time)将"HH24:MI"格式的文本转为time类型，两个time值相减得到interval类型，再转为text存储。整个表达式仅依赖输入列，符合immutable要求。

方案二：通过分钟数计算时间间隔 #

如果需要更精细的控制，可先将时间转为总分钟数，再生成时间间隔：

alter table attendance
add column totalhours text generated always as (
  make_interval(
    mins => (substring(check_out from 1 for 2)::int * 60 + substring(check_out from 4 for 2)::int) 
          - (substring(check_in from 1 for 2)::int * 60 + substring(check_in from 4 for 2)::int)
  )::text
) stored;

• 原理：提取小时和分钟转为总分钟数，相减后用make_interval生成时间间隔，所有用到的函数（substring、类型转换、make_interval）都是immutable的。

扩展：存储为数值类型的小时数 #

如果不需要文本格式的时间间隔，而是需要数值型的总小时数（如1.5代表1小时30分），可以用以下语句：

alter table attendance
add column total_hours numeric generated always as (
  extract(epoch from (cast(check_out as time) - cast(check_in as time))) / 3600
) stored;

• 原理：用extract(epoch from ...)获取时间间隔的总秒数，除以3600转为小时数。

内容的提问来源于stack exchange，提问作者jose andres inga medina