On 02/26/2017 07:56 AM, Geoff Winkless wrote: > On 26 February 2017 at 10:09, Sven R. Kunze <srkunze@xxxxxxx > <mailto:srkunze@xxxxxxx>>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.klaver@xxxxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
