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. — user330315
– user330315, Commented Apr 20, 2015 at 21:00

djoyce · Accepted Answer · 2021-05-06 11:05:24Z

16

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

edited May 6, 2021 at 11:05

djoyce

3152 silver badges6 bronze badges

answered Apr 21, 2015 at 2:47

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jrz Over a year ago

SO should automatically float Erwin's answers to the top on any question with the postgresql tag

Collectives™ on Stack Overflow

Create timestamp index from JSON on PostgreSQL

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related