Re: ERROR: functions in index expression must be marked IMMUTABLE

On Wed, Mar 25, 2015 at 3:54 PM, naveen kumar <mnaveendba2(at)gmail(dot)com> wrote:

> Hello Experts..
>
> postgres=# create index idx_calls_call_datetime on calls using btree
> ((call_datetime::date));
> ERROR: functions in index expression must be marked IMMUTABLE
>
> bit confused with above error, can any one please help me on this, and if
> possible let me explain when this error occurs and what it does. Thanks in
> advance.
>

​A function used in creating an index must solely rely upon its parameter
inputs (or internal definition, of course) during evaluation. It cannot
rely upon any external configuration. Casting to date (from unknown or
timestamptz) requires knowledge of timezone, a system-level (i.e. external
to the function) configuration and so cannot be part of an index expression.

​Typically with timestamptz you first need to convert them to timestamp
using a known, usually UTC, timezone. The conversion from timestamp (no
tz) to date is immutable. This can be done with "AT TIME ZONE": SELECT
now() AT TIME ZONE 'UTC';

​David J.