date_trunc on date is immutable?

From: Kian Wright <kian(dot)wright(at)senioreducators(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: date_trunc on date is immutable?
Date: 2009-12-24 23:36:49
Message-ID: e88f31fb0912241536u48d65a41j14072b4c034d3266@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Andrus 2009-12-24 23:47:23 Re: How to add month.year column validation
Previous Message Scott Marlowe 2009-12-24 22:53:26 Re: Optimizing data layout for reporting in postgres