PostgreSQL 14生成列用to_timestamp报错，SELECT语句却正常？

PostgreSQL 14数据库中有一张由其他软件自动生成的表，表内有一个BIGINT类型的t_stamp列（存储毫秒级时间戳），需要将该列转换为TIMESTAMP WITH TIME ZONE类型供其他场景使用。

尝试通过生成列实现转换，执行SQL语句：

ALTER TABLE myTable 
ADD COLUMN cpt_timestamp TIMESTAMP WITH TIME ZONE GENERATED ALWAYS AS to_timestamp(t_stamp/1000) STORED

执行后报错：

ERROR:  syntax error at or near "to_timestamp"
LINE 2: ...tamp TIMESTAMP WITH TIME ZONE GENERATED ALWAYS AS to_timesta...
                                                             ^
SQL state: 42601
Character: 97

但在SELECT语句中使用相同逻辑却能正常运行：

SELECT *, to_timestamp(t_stamp/1000) FROM my_table

疑问：生成列是否不允许此类转换操作？

生成列并非完全禁止转换操作，但PostgreSQL要求生成列的表达式必须使用immutable（不可变）函数——即相同输入在任何环境下都能返回完全相同的输出，不会受时区、配置参数等外部因素影响。

而to_timestamp(double precision)属于stable（稳定）函数，它的结果会受数据库时区设置影响，因此不符合生成列的要求，这才是报错的核心原因（报错信息里的语法错误提示容易误导，实际是函数稳定性不达标）。

解决方法 #

方案1：使用immutable表达式替代to_timestamp

可以通过epoch时间戳与时间间隔的运算实现转换，这个表达式属于immutable类型：

ALTER TABLE myTable 
ADD COLUMN cpt_timestamp TIMESTAMP WITH TIME ZONE GENERATED ALWAYS AS 
    (TIMESTAMP WITH TIME ZONE 'epoch' + t_stamp * INTERVAL '1 millisecond') STORED;

如果t_stamp是秒级时间戳，只需把INTERVAL '1 millisecond'改成INTERVAL '1 second'即可。

方案2：用视图替代生成列

如果不需要存储转换后的值，只是为了查询方便，可以创建视图：

CREATE VIEW my_table_with_timestamp AS
SELECT *, to_timestamp(t_stamp/1000) AS cpt_timestamp FROM my_table;

视图不需要遵守immutable函数的限制，使用起来更灵活。

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