From: | a(dot)redhead(at)openinternetsolutions(dot)com |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | expression index on date_trunc |
Date: | 2008-01-30 21:06:13 |
Message-ID: | 17486740.1457461201727173140.JavaMail.servlet@kundenserver |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Hart | 2008-01-30 21:10:07 | Mailing list archives/docs project |
Previous Message | Tom Hart | 2008-01-30 20:16:02 | Re: postgresql book - practical or something newer? |