Re: expression index on date_trunc

Hi,

is is possible to create an expression index based on the date_trunc function?

Working with PostgreSQL 8.2, I'm trying to create an index using:

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
SQL state: 42P17

I'd like to use the index to speed up a query that does a "group by" on the day part of a timestamp to lump all the stuff that happens in the same day together (I have a timestamp because the information in the table is coming from an appserver logfile and the date/time component of each line provides a full timestamptz (always the same tz!)).

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.

Thanks,

Andy

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

<a(dot)redhead(at)openinternetsolutions(dot)com> writes:

> CREATE INDEX request_day_idx ON moksha_sm_request (date_trunc('day', request_received));
...
> 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.

date_trunc(timestamp with time zone) is not immutable because it depends what
your current time zone is. That is, if you change what time zone you're in a
timestamp with time zone could appear to change from one day to another.

However date_trunc(timestamp without time zone) is immutable. So I think what
you have to do is build your index on:

date_trunc('day', request_received AT TINE ZONE 'GMT')

or whatever time zone you're interested in. That will get you the day part of
that timestamp at that time zone (because it first casts it to a timestamp
without time zone for the time zone you specify).

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!