On 02/26/2017 08:15 AM, Geoff Winkless wrote:
On 26 February 2017 at 16:09, Adrian Klaver <[email protected]
<mailto:[email protected]>>wrote:
On 02/26/2017 07:56 AM, Geoff Winkless wrote:
> On 26 February 2017 at 10:09, Sven R. Kunze <[email protected]
<mailto:[email protected]>
> <mailto:[email protected] <mailto:[email protected]>>>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
[email protected]
--
Sent via pgsql-general mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
