create temporary table junk as select now()::date as evtdate;
alter table junk add column chardate text GENERATED ALWAYS AS (to_char(evtdate,'YYYY-Mon-DD')) STORED;
ERROR: generation expression is not immutable
Maybe this is a hack but..
b2bcreditonline=# create temporary table junk as select now()::date as evtdate; SELECT 1 b2bcreditonline=# alter table junk add column chardate text GENERATED ALWAYS AS (to_char(evtdate,'YYYY-Mon-DD')) STORED; ERROR: generation expression is not immutable b2bcreditonline=# create or replace function date_to_text(i_date in date) returns text immutable language sql as $$ select to_char(i_date, 'YYYY-MM-DD') $$; CREATE FUNCTION b2bcreditonline=# alter table junk add column chardate text GENERATED ALWAYS AS (date_to_text(evtdate)) STORED; ALTER TABLE b2bcreditonline=# select * from junk; evtdate | chardate ------------+------------ 2024-04-24 | 2024-04-24 (1 row)
