我有timestampz列，名为created，我希望使用Postgres 12+generated columns从它创建为月、日和年生成的列.
由于timestampz中有一个时区，因此像date_part(create, ..)和EXTRACT (month from created)这样的表达式会失败，并显示错误:
ERROR: generation expression is not immutable
我try 将时间戳转换为时间戳并修复时区，但两者仍然被认为是可变的生成表达式:
-
GENERATED ALWAYS AS (date_part('month', created::timestamp))个 -
GENERATED ALWAYS AS (date_part('month', created::timestamp AT TIME ZONE 'UTC'))个
然而，正如Erwin Brandstetter在他对this answer的 comments 中所指出的那样，这是可行的:
ALTER TABLE tbl
ADD COLUMN created_year numeric GENERATED ALWAYS AS
(date_part('month', created AT TIME ZONE 'UTC')) STORED;
当然，EXTRACT(month from created AT TIME ZONE 'UTC'))也行得通.
That leads me to the conclusion that if I want N timezones, I need N generated columns.
Why does this work and not #2 above? Does it have to do with the servers' configuration params when doing ::timestamp datatype conversion?