Re: [GENERAL] ERROR: functions in index expression must be markedIMMUTABLE - Mailing list pgsql-general

On 02/26/2017 07:56 AM, Geoff Winkless wrote:
> On 26 February 2017 at 10:09, Sven R. Kunze <srkunze@mail.de
> <mailto:srkunze@mail.de>>wrote:
>
>     >>># create index docs_birthdate_idx ON docs using btree
>     (((meta->>'birthdate')::date));
>     ERROR:  functions in index expression must be marked IMMUTABLE
>
>     So, what is the problem here?
>
>
> ​Date functions are inherently not immutable because of timezones. Your
> solution of using to_timestamp doesn't help because it automatically
> returns a value in WITH TIMESTAMP. Do you get anywhere by using
> "::timestamp without time zone" instead, as suggested here?

My attempts at working the OP's problem passed through that:

test=> create index docs_birthdate_idx ON docs using btree (((meta->>'birthdate')::timestamp));
ERROR:  functions in index expression must be marked IMMUTABLE

This works:

test=> create index docs_birthdate_idx ON docs using btree ((meta->>'birthdate'));
CREATE INDEX

It is the act of casting that fails. Other then the OP's own suggestion of creating
a function that wraps the operation and marks it immutable I don't have a solution at
this time.

>
> https://www.postgresql.org/message-id/4E039D16.20704%40pinpointresearch.com
>
> Geoff
>
> ​


--
Adrian Klaver
adrian.klaver@aklaver.com