Re: generated column cast from timestamptz to timestamp not OK.

On Fri, 13 May 2022 at 12:47, alias <postgres(dot)rocks(at)gmail(dot)com> wrote:
> CREATE TABLE test_g (
> a timestamptz,
> b timestamp GENERATED ALWAYS AS (a::timestamp) STORED
> );
> then an error occurred.
>> ERROR: 42P17: generation expression is not immutable

Cast to timestamp uses current session time zone, current session time
zone is not inmutable.

Try forcing the time zone ( a at timezone $whatever ) ( or use a view
if you need it ).

> However the following 2 commands is ok.
...
>> b bigint GENERATED ALWAYS AS (a::bigint) STORED
...
>> b text GENERATED ALWAYS AS (a::text) STORED

Because conversion from int to bigint is inmutable, just extend sign
bit, and from varchar to text too, they are the same.

> I didn't get it. timestamptz changes then timestamp also changes. timestamp is part of timestamptz...

I think you are falling in the common misconception that a timestamp
is something like "YYYYMMDDhhmmss" and a timestamptz is the same plus
a time zone. They are not ( look at the docs, they both have the same
size ).

A timestamptz dessignates a point in time, is like a real number,
which is printed ( and read by default ) in the timezone of the user
session. It DOES NOT STORE A TIME ZONE. Its external ( text )
representation varies with the session parameters. Use set timezone
and play a bit to see it.

A timestamp is more or less the same, but is always printed/read as if
it where in the UTC timezone, and the time zone is not printed ( as it
is always the same ). But inside is also just a real number. It is
just a compact and convenient way of storing "YYYYMMDDhhmmss",
calculate the timepoint in that utc time and store it.

To convert between them in a inmutable way you need to specify how and
use the at tz operator.

Try it in a command line.

FOS