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

On 02/26/2017 08:15 AM, Geoff Winkless wrote:
> On 26 February 2017 at 16:09, Adrian Klaver <adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>>wrote:
>
>     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>
>     > <mailto: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?
>
> ​Of course I meant "WITH TIMEZONE" here, finger slippage.

That does not work either:

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


> ​
>
>     My attempts at working the OP's problem passed through that:
>
> ​​Apologies, I don't have that reply in the thread in my mailbox.

No apologies needed I had not posted my attempts at that point. It was
more me thinking out loud.

> ​
>
>     test=> create index docs_birthdate_idx ON docs using btree
>     (((meta->>'birthdate')::timestamp));
>     ERROR:  functions in index expression must be marked IMMUTABLE
>
>
> ​ Isn't the point that casting to ::timestamp will still keep the
> timezone?  Hence casting to "without timezone".
>
>     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
>
>
> ​I can imagine that without a cast, depending on the way birthdate is
> stored, it may behave differently to a cast index for ordering.
>
> Geoff


--
Adrian Klaver
adrian.klaver@aklaver.com