PostgreSQL生成列报错：如何用Timestamp和Interval计算locked_until值？

错误根源在于locked_at + locked_for这个表达式的函数级别是stable，而PostgreSQL的存储型生成列（STORED）要求表达式必须是immutable——因为存储生成列的值会被持久化到磁盘，数据库需要确保表达式结果不会随时间、时区配置等环境变化而改变。

给你两种可行的解决方式：

方式一：创建自定义Immutable函数封装计算 #

先定义一个immutable级别的函数来处理时间相加：

CREATE OR REPLACE FUNCTION calculate_locked_until(locked_at timestamptz, locked_for interval)
RETURNS timestamptz
LANGUAGE sql
IMMUTABLE
AS $$
SELECT locked_at + locked_for;
$$;

然后用这个函数来定义生成列：

CREATE TABLE public.instance_lock
(
    id uuid NOT NULL,
    instance_id character(8) NOT NULL,
    locked_at timestamp with time zone NOT NULL,
    locked_for interval NOT NULL,
    locked_until timestamp with time zone NOT NULL GENERATED ALWAYS AS (calculate_locked_until(locked_at, locked_for)) STORED,
    PRIMARY KEY (id)
);

方式二：用视图替代存储生成列 #

如果业务上不需要把locked_until持久化存储，完全可以用视图实时计算：

CREATE TABLE public.instance_lock
(
    id uuid NOT NULL,
    instance_id character(8) NOT NULL,
    locked_at timestamp with time zone NOT NULL,
    locked_for interval NOT NULL,
    PRIMARY KEY (id)
);

CREATE VIEW instance_lock_with_until AS
SELECT *, locked_at + locked_for AS locked_until
FROM public.instance_lock;

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