On 26 February 2017 at 16:09, Adrian Klaver <adrian.kla...@aklaver.com> wrote:
> On 02/26/2017 07:56 AM, Geoff Winkless wrote: > > On 26 February 2017 at 10:09, Sven R. Kunze <srku...@mail.de > > <mailto:srku...@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. > My attempts at working the OP's problem passed through that: > > Apologies, I don't have that reply in the thread in my mailbox. > 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
