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