Hi list,
I recently ran into a slight problem that wasn't so slight at the moment;
it was about extracting parts of a timestamp with time zone to speed up
calculations with pre-calculated columns instead of calculating those on
each access.
To be short, I had something like :
CREATE TABLE schedule.reservation(
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
ts_resa TIMESTAMPTZ NOT NULL,
year INTEGER GENERATED ALWAYS AS
(EXTRACT(year FROM ts_resa)) STORED,
[…]
Which, was of course refused by Pg :
ERROR: generation expression is not immutable
Sooo, as usual, I let it stew a couple of days and then, the morning of
the 3rd day, I had a vision of Tom Lane ridding a jpeg (or may be a png)
of a blue elephant with a large white beard, just like Santa (Tom, not
the elephant) and wearing a green bikini.
At first sight, I was horrified because green and bleu are clashing
colors, but at the end, I came with this :
CREATE TABLE schedule.reservation(
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
ts_resa TIMESTAMPTZ NOT NULL,
year INTEGER GENERATED ALWAYS AS
(EXTRACT(year FROM ts_resa AT TIME ZONE 'Europe/Paris')) STORED,
[…]
that solved the problem (and of course I do not need such an extraction
from another time zone.)
So, may be adding such an example in the doc could help people meeting
the same issue as it should often be the case when needing part(s) fo a
timestamp with time zone for a large number of rows.
Jean-Yves