Search code examples
postgresqlimmutabilitycalculated-columnsgenerated-columns

How to use a timestamp with time zone column in expression for a generated column?

I have timestampz column called created, and I want to create generated columns for month, day and year from it using Postgres 12+ generated columns.

Since there is a timezone in timestampz, expressions like date_part(create, ..) and EXTRACT (month from created) fail with the error:

ERROR: generation expression is not immutable

I tried to convert to timestamp and fix the timezone, but both would still be considered mutable generation expressions:

  1. GENERATED ALWAYS AS (date_part('month', created::timestamp))
  2. GENERATED ALWAYS AS (date_part('month', created::timestamp AT TIME ZONE 'UTC'))

However, as indicated by Erwin Brandstetter in his comment on this answer, this works:

ALTER TABLE tbl
ADD COLUMN created_year numeric GENERATED ALWAYS AS 
    (date_part('month', created AT TIME ZONE 'UTC')) STORED;

Of course EXTRACT(month from created AT TIME ZONE 'UTC')) also works.

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?

Solution

  • Why does this work and not #2 above?

    Both #1 and #2 do not work because the cast from timestamptz to timestamp is not immutable. It depends on the current timezone setting. And generated columns only accept immutable expressions for obvious reasons. To note: timezone is not a "server setting", it's a setting for each individual session. The default is typically set in postgresql.conf, but clients can (and regularly will) set it as needed.

    On the other hand, date_part('month', created AT TIME ZONE 'UTC is immutable. Deriving UTC time (or the local time for any given time zone offset) always produces the same result, and the time zone is a given constant in this expression.

    The name of the data type timestamp with time zone is a bit misleading, unfortunately. A given time zone is not stored at all. It just serves as input modifier to compute the corresponding UTC time, which is stored internally. (And as output decorator, adjusted to the timezone of the requesting client.) See:

    Basics: