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?
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 |