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
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
