Greetings hackers.
It seems that PostgreSQL 14 allows using the AT TIME ZONE operator within
generated column definitions; according to the docs, that means the
operator is considered immutable. However, unless I'm mistaken, the result
of AT TIME ZONE depends on the time zone database, which is external and
can change. I think that means that generated column data can become
out-of-date upon tz database changes.
Sample table creation DDL:
CREATE TABLE events (
id integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
local_timestamp timestamp without time zone NOT NULL,
utc_timestamp timestamp with time zone GENERATED ALWAYS AS
(local_timestamp AT TIME ZONE time_zone_id) STORED,
time_zone_id text NULL
);
For comparison, SQL Server does consider AT TIME ZONE to be
non-deterministic, and therefore does not allow it in stored generated
columns (it does allow it in non-stored ones).
Shay
