Possible improvement to the generated columns doc
От | Bzzzz |
---|---|
Тема | Possible improvement to the generated columns doc |
Дата | |
Msg-id | 20191209043452.6525812b@msi.defcon1.lan обсуждение исходный текст |
Список | pgsql-novice |
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
В списке pgsql-novice по дате отправления: