From: | "Daniel Verite" <daniel(at)manitou-mail(dot)org> |
---|---|
To: | a(dot)redhead(at)openinternetsolutions(dot)com |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: expression index on date_trunc |
Date: | 2008-01-30 22:32:47 |
Message-ID: | f6b42095-5fe2-4f6a-8c49-6bd49badb3ad@mm |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
A Redhead wrote:
> CREATE INDEX request_day_idx ON moksha_sm_request
(date_trunc('day', request_received));
>
> I get the error message:
>
> ERROR: functions in index expression must be marked IMMUTABLE
[...]
> I'd be grateful if someone could point out what part of the statement
is not IMMUTABLE
> or how I could mark my create index statement as being immutable.
The retrieved value of request_received depends on your current
timezone, and so does the result of date_trunc, that would be why it's
not immutable.
If you don't need that behavior, you can shift your timestamptz to a
fixed timezone, both in your index and in your queries, as in:
CREATE INDEX request_day_idx ON moksha_sm_request
(date_trunc('day', request_received at time zone 'Europe/Paris'));
--
Daniel
PostgreSQL-powered mail user agent and storage:
http://www.manitou-mail.org
From | Date | Subject | |
---|---|---|---|
Next Message | Gregory Stark | 2008-01-30 22:36:16 | Re: expression index on date_trunc |
Previous Message | Reece Hart | 2008-01-30 22:29:12 | Re: Oracle Analytical Functions |