Обсуждение: ERROR: functions in index expression must be marked IMMUTABLE
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.
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.
Thanks & Regards,
M Naveen Kuamr,
PostgreSQL Database Administrator,
M Naveen Kuamr,
PostgreSQL Database Administrator,
Mobile Number: +91 7755929449.
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.
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,
M Naveen Kuamr,
PostgreSQL Database Administrator,
Mobile Number: +91 7755929449.
On Thu, Mar 26, 2015 at 4:39 AM, David G. Johnston <david.g.johnston@gmail.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.