sql - 使用 to_char 和日期在 postgres 中添加生成的列

Question

我有一个日期栏 created_at在 postgres 表中。我有一个长时间运行的查询，按周对数据进行分组，因此查询的一部分是

(SELECT TO_CHAR(score.created_at, 'IYYY-IW') AS week, 
customer_id, AVG(score) AS avg_week_score FROM score

这是低效的，我想使用 Postgres 12 中新生成的列功能来存储每个插入日期的文本版本。

我试着跑

alter table score add column gen_week text generated always as (TO_CHAR(created_at, 'IYYY-IW')) stored;

但得到

ERROR:  generation expression is not immutable

我猜这与 created_at 依赖于语言环境，尤其是时区这一事实有关，所以我尝试了

alter table score add column week_gen text generated always as (TO_CHAR(created_at::timestamptz at time zone 'UTC', 'IYYY-IW')) stored;

使时区明确，但这会产生相同的错误。

有没有办法使 to_char 与日期对象保持不变，或者我是否必须基于 to_char 定义我自己的不可变函数？

Answer 1

问题是to_char()可以从语言环境中进行修改——函数要么是不可变的，要么不是不可变的。

您可以使用 extract() 做同样的事情:

alter table score add column gen_week text generated always as 
    ((extract(isoyear from created_at) * 100 + extract(week from created_at))::text ) stored;

实际上，上面没有把连字符放进去，所以:

alter table score add column gen_week text generated always as
    (extract(isoyear from created_at)::text || '-' || lpad(extract(week from created_at)::text, 2, '0')) stored;

Here是一个db<> fiddle 。