PostgreSQL创建含Timestamp的生成列报错：表达式非不可变

问题出在生成存储列（STORED）要求表达式必须是**不可变（immutable）的，而你用来转换timestamp without time zone到文本的操作（不管是TO_CHAR还是直接::text）都属于稳定（stable）**函数，不是不可变的——因为它们的输出会受数据库的datestyle等会话参数影响，不符合生成存储列的要求。

解决方案：自定义不可变函数转换时间戳 #

先创建一个返回固定格式文本的不可变函数，确保时间戳转文本的操作不受外部参数影响：

CREATE OR REPLACE FUNCTION timestamp_to_iso(timestamp without time zone) 
RETURNS text 
LANGUAGE sql 
IMMUTABLE 
AS $$
SELECT to_char($1, 'YYYY-MM-DD');
$$;

然后用这个函数创建生成列：

CREATE TABLE lms.test_person (
    first_name text,
    last_name text,
    date_of_birth timestamp without time zone,
    search tsvector GENERATED ALWAYS AS (
        to_tsvector('english', first_name || ' ' || last_name || ' ' || timestamp_to_iso(date_of_birth))
    ) STORED
);

为什么原来的写法不行？ #

• date_of_birth::text的输出格式依赖datestyle参数（比如ISO模式输出2024-05-20，SQL模式可能输出20/05/2024），所以这个转换是stable而非immutable。
• TO_CHAR(date_of_birth, 'YYYY-MM-DD')虽然指定了格式，但函数本身的定义是stable（可能受NLS相关参数影响），同样不满足生成存储列的要求。
• 自定义函数通过声明IMMUTABLE，告诉PostgreSQL这个函数的输出只由输入参数决定，不会受外部参数影响，因此可以安全用于生成存储列。

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