PostgreSQL计算timestamptz字段时间差及生成列报错解决

PostgreSQL中GENERATED ALWAYS AS ... STORED定义的存储生成列，值完全由数据库根据列定义的表达式自动计算维护，不允许用户在执行INSERT/UPDATE操作时，给该列传入除DEFAULT外的自定义值。
你遇到这个报错，就是因为插入数据的SQL语句中，显式给scheduled_duration字段指定了非DEFAULT的具体值，违反了生成列的约束。

1. 修正插入语句的写法 #

你当前写的生成列定义本身逻辑没有问题：通过extract(epoch from 时间差)获取两个timestamptz类型时间的秒级差值，再除以3600转换为保留两位小数的小时数值，计算逻辑准确，也规避了timestamptz时区转换、夏令时带来的计算误差。
只需要调整插入语句，不要手动给scheduled_duration传值即可：

• 写法一：插入字段列表直接省略生成列

-- 示例：插入时只写两个基础时间字段
INSERT INTO 你的表名 (scheduled_departure_time, scheduled_arrival_time)
VALUES ('2024-05-01 09:00:00+08', '2024-05-01 12:15:00+08');

• 写法二：如果必须在字段列表中保留该列，显式传入DEFAULT

INSERT INTO 你的表名 (scheduled_departure_time, scheduled_arrival_time, scheduled_duration)
VALUES ('2024-05-01 09:00:00+08', '2024-05-01 12:15:00+08', DEFAULT);

2. 按需调整生成列类型（可选） #

如果你不需要把时间差转换为数值类型的小时数，想要直接保留时间间隔格式，可以把生成列类型改为INTERVAL，定义更简洁：

scheduled_departure_time TIMESTAMPTZ NOT NULL,
scheduled_arrival_time TIMESTAMPTZ NOT NULL,
scheduled_duration INTERVAL NOT NULL
    GENERATED ALWAYS AS (scheduled_arrival_time - scheduled_departure_time)
    STORED

这种写法下查询时会直接返回类似03:15:00的间隔结果，适合需要直接读取时间长度格式的场景。

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