From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Geoff Winkless <pgsqladmin(at)geoff(dot)dj>, "Sven R(dot) Kunze" <srkunze(at)mail(dot)de> |
Cc: | Postgres General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: ERROR: functions in index expression must be marked IMMUTABLE |
Date: | 2017-02-26 16:09:39 |
Message-ID: | 7f47d2f6-0672-40f3-308d-6d50193160c8@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 02/26/2017 07:56 AM, Geoff Winkless wrote:
> On 26 February 2017 at 10:09, Sven R. Kunze <srkunze(at)mail(dot)de
> <mailto:srkunze(at)mail(dot)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(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Geoff Winkless | 2017-02-26 16:15:13 | Re: ERROR: functions in index expression must be marked IMMUTABLE |
Previous Message | Geoff Winkless | 2017-02-26 15:56:22 | Re: ERROR: functions in index expression must be marked IMMUTABLE |