On Fri, 13 May 2022 at 12:47, alias <postgres.rocks@gmail.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