Hello everybody, I'd like to implement a btree date index from json input data. >>># \d docs Table "public.docs" Column | Type | Modifiers --------+---------+--------------------------------------------------- id | integer | not null default nextval('docs_id_seq'::regclass) meta | jsonb | Indexes: "docs_pkey" PRIMARY KEY, btree (id) So, I did:
>>># create index docs_birthdate_idx ON docs using btree (((meta->>'birthdate')::date)); ERROR: functions in index expression must be marked IMMUTABLE
Searching the Internet for a solution, I tried several variants of this:
>>># create index docs_birthdate_idx ON docs using btree ((to_timestamp(meta->>'birthdate', 'YYYY-MM-DD') at time zone 'UTC')); ERROR: functions in index expression must be marked IMMUTABLE
Years ago, I circumvented it by creating an immutable function. This, though, just hides the errors since I would use the mutable expression anyway and mark it as immutable.
