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';
