Create timestamp index from JSON on PostgreSQL

Question

I have a table on PostgreSQL with a field named data that is jsonb with a lot of objects, I want to make an index to speed up the queries. I'm using few rows to test the data (just 15 rows) but I don't want to have problems with the queries in the future. I'm getting data from the Twitter API, so with a week I get around 10gb of data.
If I make the normal index

CREATE INDEX ON tweet((data->>'created_at'));

I get a text index, if I make:

Create index on tweet((CAST(data->>'created_at' AS timestamp)));

I get

ERROR: functions in index expression must be marked IMMUTABLE

I've tried to make it "inmutable" setting the timezone with

date_trunc('seconds', CAST(data->>'created_at' AS timestamp) at time zone 'GMT')

but I'm still getting the "immutable" error. So, How can I accomplish a timestamp index from a JSON? I know that I could make a simple column with the date because probably it will remain constant in the time, but I want to learn how to do that.

The only workaround is to create your own function that does the casting (using the time zone), declare that function as immutable and then use *that* function for the index. — , Apr 20 '15 at 21:00

score 14 · Accepted Answer · edited May 06 '21 at 11:05

This expression won't be allowed in the index either:

(CAST(data->>'created_at' AS timestamp) at time zone 'UTC')

It's not immutable, because the first cast depends on your DateStyle setting (among other things). Doesn't help to translate the result to UTC after the function call, uncertainty has already crept in ...

The solution is a function that makes the cast immutable by fixing the time zone (like @a_horse already hinted).

I suggest to use to_timestamp() (which is also only STABLE, not IMMUTABLE) instead of the cast to rule out some source of trouble - DateStyle being one.

CREATE OR REPLACE FUNCTION f_cast_isots(text)
  RETURNS timestamptz AS
$$SELECT to_timestamp($1, 'YYYY-MM-DD HH24:MI')$$  -- adapt to your needs
  LANGUAGE sql IMMUTABLE;

Note that this returns timestamptz. Then:

CREATE INDEX foo ON t (f_cast_isots(data->>'created_at'));

Detailed explanation for this technique in this related answer:

Does PostgreSQL support "accent insensitive" collations?

Query on a time range ignoring the date of timestamps

Create timestamp index from JSON on PostgreSQL

1 Answers1

Linked

Related