[GENERAL] ERROR: functions in index expression must be marked IMMUTABLE

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.


So, what is the problem here?


Regards,
Sven