lundi 20 avril 2015

Create timestamp index from JSON on PostgreSQL

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.

Aucun commentaire:

Enregistrer un commentaire