On Thu, Apr 25, 2024 at 7:31 AM Celia McInnis <celia.mcin...@gmail.com> wrote:
> 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)