Skip site navigation (1) Skip section navigation (2)

expression index on date_trunc

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 (view raw or flat)
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

Responses

pgsql-general by date

Next:From: Tom HartDate: 2008-01-30 21:10:07
Subject: Mailing list archives/docs project
Previous:From: Tom HartDate: 2008-01-30 20:16:02
Subject: Re: postgresql book - practical or something newer?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group