I'm trying to create an index on the month and year of a date field (in 8.3), and I'm getting the "functions in index expression must be marked IMMUTABLE" error message.
I thought dates were immutable, and didn't think that DATE_TRUNC did anything to change that. These all fail: create index enrollments_app_recvd_month_idx on enrollments ( date_trunc('month', appl_recvd_date) ); create index enrollments_app_recvd_month_idx on enrollments ( (date_trunc('month', appl_recvd_date) at time zone 'pst') ); create index enrollments_app_recvd_month_idx on enrollments ( to_char(appl_recvd_date, 'YYYYMM') ); create index enrollments_app_recvd_month_idx on enrollments ( (to_char(extract(year from appl_recvd_date), '0000') || to_char(extract( month from appl_recvd_date), '00')) );
After much experimentation, I finally was able to get this to work: create index enrollments_app_recvd_month_idx on enrollments ( (cast(extract(year from appl_recvd_date) as text) || cast(extract(month from appl_recvd_date) as text)) );
I am guessing to_char is mutable because the format string could use a locale specific character, and PG doesn't bother to check the format string when determining whether a function call is immutable. But I'm lost on why date_trunc is mutable, especially after applying a specific time zone. Am I missing something here?