Celia McInnis <celia.mcinnis@xxxxxxxxx> writes: > 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 Probably not; I think all the available conversion functions respond to some combination of datestyle, lc_time, and timezone settings. (Type date doesn't depend on timezone, but that keeps you from using anything that shares functionality with timestamptz ... and your to_char call promotes the date to timestamptz.) I find your example not terribly compelling. Why expend storage space on such a column? If you're bound and determined to do it, writing a wrapper function that's labeled immutable should work: =# create function mytochar(date) returns text strict immutable parallel safe as $$ begin return to_char($1::timestamp, 'YYYY-Mon-DD'); end $$ language plpgsql; CREATE FUNCTION =# alter table junk add column chardate text GENERATED ALWAYS AS (mytochar(evtdate)) STORED; ALTER TABLE It's on you to be sure that the function actually is immutable, or at least immutable enough for your use-case. I believe my example is pretty safe: neither datestyle nor timezone should affect the timestamp-without-timezone variant of to_char(), and this particular format string doesn't depend on lc_time. regards, tom lane
