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.

Possible duplicate of [PostgreSQL create index on cast from string to date](https://stackoverflow.com/questions/16405602/postgresql-create-index-on-cast-from-string-to-date) — , Nov 15 '19 at 13:07

score 1 · Answer 1 · answered Nov 15 '19 at 13:12

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.

score 0 · Answer 2 · answered Nov 21 '20 at 17:04

Augmenting Laurenz Albe's answer with my own to be able to post code, I'm using the following.

Assuming you have a table mytable with JSONB field thedata, which contains data like { "datetime": "2020-03-21T33:44:55.193843281Z ", ... }:

-- Create immutable UTC parsing function, see:
-- * https://stackoverflow.com/questions/58877503/cant-create-an-index-on-a-jsonb-field-with-date
-- * https://stackoverflow.com/questions/5973030/error-functions-in-index-expression-must-be-marked-immutable-in-postgres
-- It parses dates of format:
--     YYYY-MM-DDTHH:MM:SSZ
-- (with a 'T' between date and time, and a 'Z' at the end) for example:
--     2020-03-21T33:44:55Z
--     2020-03-21T33:44:55.193843281Z

CREATE OR REPLACE FUNCTION utc_to_timestamp(some_time text)
  RETURNS timestamp with time zone
AS
$BODY$
    select to_timestamp($1, 'YYYY-MM-DD"T"HH24:MI:SS"Z"');
$BODY$
LANGUAGE sql
IMMUTABLE;

-- Define the index:
CREATE INDEX mytable_expr_datetime on mytable (utc_to_timestamp(the_data ->> 'datetime'));

So for the table/field names in your question, you have to replace the_data ->> 'datetime' by events->'START'->> 'date', and mytable by events.

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

2 Answers2