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

Thank You so much David..

got it, before creating index, I need to make it as known function.

Thanks & Regards,
M Naveen Kuamr,
PostgreSQL Database Administrator,
Mobile Number: +91 7755929449.

On Thu, Mar 26, 2015 at 4:39 AM, David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> 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.
>
>