From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Geoff Winkless <pgsqladmin(at)geoff(dot)dj> |
Cc: | "Sven R(dot) Kunze" <srkunze(at)mail(dot)de>, Postgres General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: ERROR: functions in index expression must be marked IMMUTABLE |
Date: | 2017-02-26 17:25:52 |
Message-ID: | 38e211b3-a4ba-c006-2dc3-745580a6e055@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 02/26/2017 08:15 AM, Geoff Winkless wrote:
> On 26 February 2017 at 16:09, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com
> <mailto:adrian(dot)klaver(at)aklaver(dot)com>>wrote:
>
> 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>
> > <mailto: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?
>
> 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(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2017-02-26 17:32:53 | Re: ERROR: functions in index expression must be marked IMMUTABLE |
Previous Message | Tom Lane | 2017-02-26 16:50:34 | Re: ERROR: functions in index expression must be marked IMMUTABLE |