PostgreSQL创建生成列遇‘表达式非不可变’错误咨询

以下是针对你遇到的两个问题的具体解决方案，以及修正后的完整SQL语句：

修正后的完整建表语句 #

CREATE TABLE fy_calendar1(
    date date not null,    
    day varchar generated always AS (DATE_PART('day', date)::integer::varchar) stored,
    dow_in_month varchar generated always AS (DATE_PART('day', date)::integer::varchar) stored,
    day_of_year varchar generated always AS (DATE_PART('DOY', date)::integer::varchar) stored,
    week_of_year varchar generated always as (DATE_PART('week', date)::integer::varchar) stored,
    month_of_year varchar generated always as (DATE_PART('MONTH', date)::integer::varchar) stored,
    quarter_of_year varchar generated always as (DATE_PART('QUARTER', date)::integer::varchar) stored,
    year varchar generated always as (DATE_PART('year', date)::integer::varchar) stored,   
    day_name_week varchar generated always as (to_char(date, 'day', 'C')) stored,
    last_day_of_month date generated always as ((date_trunc('month', date::timestamp) + interval '1 month' - interval '1 day')::date) stored
);

问题1：to_char(date, 'day')报错的解决 #

原因 #

to_char(date, 'day')的结果依赖数据库的区域设置（locale），不同locale返回的星期名称不同（比如中文返回"星期一"，英文返回"monday"），因此该函数调用默认不属于**immutable（不可变）**函数。而PostgreSQL的存储型生成列要求表达式必须是immutable的，因为存储列的值需要固定，不能随环境配置变化。

解决方案 #

在to_char中添加第三个参数指定固定locale，比如'C'（POSIX标准locale，返回小写英文星期名称），此时函数调用变为immutable：

day_name_week varchar generated always as (to_char(date, 'day', 'C')) stored

如果需要其他语言的固定名称，可以指定对应locale（比如'en_US.UTF-8'），前提是该locale已在数据库服务器上配置。

问题2：月末日期生成列报错的解决 #

原因 #

原表达式DATE_TRUNC('MONTH', date) + INTERVAL '1 MONTH - 1 day'存在两个问题：

1. 运算结果为timestamp类型，直接存为varchar不符合数据类型规范；
2. 该表达式的immutable属性不满足要求，因为interval的复合写法可能导致PostgreSQL无法判定其不可变性。

解决方案 #

1. 优先将last_day_of_month定义为date类型（而非varchar），更贴合数据实际意义；
2. 调整表达式为明确的immutable形式，分步运算确保不可变性：

   last_day_of_month date generated always as ((date_trunc('month', date::timestamp) + interval '1 month' - interval '1 day')::date) stored
   

   若确实需要存为varchar，可套一层固定格式的to_char（确保immutable）：

   last_day_of_month varchar generated always as (to_char((date_trunc('month', date::timestamp) + interval '1 month' - interval '1 day')::date, 'YYYY-MM-DD')) stored
   

   另外，PostgreSQL 14及以上版本可直接使用last_day(date)函数（默认是immutable的），写法更简洁：

   last_day_of_month date generated always as (last_day(date)) stored
   

额外优化 #

原语句中DATE_PART返回的是double precision类型，直接存为varchar会带小数位（比如1.0），因此添加::integer::varchar转换，确保得到纯数字字符串（比如1），更符合预期。

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