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?
