Can't create an index on a jsonb field with date

Question

I have a jsonb column event in a table. I am trying to create an index

CREATE INDEX ON table(((events->'START'->> 'date')::timestamp AT TIME ZONE 'PST'));

but it is throwing error functions in index expression must be marked IMMUTABLE

After passing it a timezone it should make it immutable, but I am not sure why it's still throwing an error.

Answer 1

The cast to timestamp is your problem. It is not IMMUTABLE because the function used accepts arguments like now.

If you are certain that your data contain only regular timestamps and no such values, you can define your own IMMUTABLE LANGUAGE sql function that wraps the type cast.

You can use such a function in your query and index it. If there are values for which the cast truly isn't immutable, your index will become corrupted.