ALTER TABLE my_table
    ADD date date GENERATED ALWAYS AS ((timezone( 'Asia/Kolkata'::text,
       COALESCE( timestamp_with_timezone_1, timestamp_with_timezone_2 ) ))::date) STORED;

I get the following error

[42P17] ERROR: generation expression is not immutable

I have tried using '+5:30' instead of 'Asia/Kolkata' I have tried using at time zone directly with the column, like this:

ALTER TABLE my_table
ADD COLUMN date date GENERATED ALWAYS AS (
  COALESCE(
    (timestamp_with_timezone_1 AT TIME ZONE 'Asia/Kolkata')::date,
    (timestamp_with_timezone_2 AT TIME ZONE 'Asia/Kolkata')::date
  )
) STORED; 

Throws same error.

Can anyone suggest a solution.

ALTER TABLE my_table
    ADD date date GENERATED ALWAYS AS ((timezone( 'Asia/Kolkata'::text,
       COALESCE( timestamp_with_timezone_1, timestamp_with_timezone_2 ) ))::date) STORED;

I get the following error

[42P17] ERROR: generation expression is not immutable

I have tried using '+5:30' instead of 'Asia/Kolkata' I have tried using at time zone directly with the column, like this:

ALTER TABLE my_table
ADD COLUMN date date GENERATED ALWAYS AS (
  COALESCE(
    (timestamp_with_timezone_1 AT TIME ZONE 'Asia/Kolkata')::date,
    (timestamp_with_timezone_2 AT TIME ZONE 'Asia/Kolkata')::date
  )
) STORED; 

Throws same error.

Can anyone suggest a solution.

• sql
• postgresql
• postgres15

• 1 N.B.: I could only reproduce your error when initial columns were without time zone: are you sure timestamp_with_timezone_1 and _2 have one? – Guillaume Outters Commented Mar 17 at 14:51
• Yes, please clarify what the data types of the columns are. – Laurenz Albe Commented Mar 17 at 15:04
• Both columns are as stated..timestamp with timezone. – Shh Commented Mar 18 at 3:25

1 Answer 1

Do set your time zone after the COALESCE (and it's shorter to write!):

ALTER TABLE my_table
    ADD date date GENERATED ALWAYS AS
    (COALESCE( timestamp_with_timezone_1, timestamp_with_timezone_2 ) AT TIME ZONE 'Asia/Kolkata')
    STORED;

See it in this fiddle.