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

Re: expression index on date_trunc

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

In response to

pgsql-general by date

Next:From: Gregory StarkDate: 2008-01-30 22:36:16
Subject: Re: expression index on date_trunc
Previous:From: Reece HartDate: 2008-01-30 22:29:12
Subject: Re: Oracle Analytical Functions

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