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
expressionanyway and mark it as immutable.
So, what is the problem here?
Regards,
Sven
