Sorry I pasted in the wrong code, I had wanted a column with the character version of the date (ie., YYYY-Mon-DD). Steve Baldwin's hack pointed me in the right direction. Here is the example:
create temporary table junk as select now()::date as evtdate; SELECT 1 alter table junk add column chardate text GENERATED ALWAYS AS (cmm_date_to_char(evtdate)) STORED; select * from junk; evtdate | chardate ------------+------------- 2024-04-24 | 2024-Apr-24 (1 row) where cmm_date_to_char is defined as: create or replace function cmm_date_to_char(i_date in date) returns text immutable language sql as $$ select to _char(i_date, 'YYYY-Mon-DD') $$; Thanks! On Wed, Apr 24, 2024 at 5:54 PM Tom Lane <t...@sss.pgh.pa.us> wrote: > Celia McInnis <celia.mcin...@gmail.com> 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 >
