Create time index from JSON in PostgreSQL
I have a PostgreSQL table with a field named data
, that is, jsonb
with a large number of objects, I want to make an index to speed up queries. I am using multiple lines to validate the data (15 lines in total), but I don't want to have problems with queries in the future. I am fetching data from the Twitter API, so after a week I get about 10GB of data.
If I do a normal index
CREATE INDEX ON tweet((data->>'created_at'));
I get the text index if I do:
Create index on tweet((CAST(data->>'created_at' AS timestamp)));
I get
ERROR: functions in index expression must be marked IMMUTABLE
I tried to make it "inmutable" by setting the timezone with
date_trunc('seconds', CAST(data->>'created_at' AS timestamp) at time zone 'GMT')
but I am still getting the "immutable" error. So how can I execute the timestamp index from JSON? I know that I could make a simple date column because it will probably stay constant over time, but I want to know how.
source to share
This expression will also not be resolved in the index:
(CAST(data->>'created_at' AS timestamp) at time zone 'UTC')
This is not immutable, because the first cast depends on your parameter DateStyle
(by the way). It does not help to translate the result into UTC after the function call, the ambiguity has already crept in ...
the solution is a function that makes the cast unchanged by fixing the timezone (like @a_horse hinted at already ).
I suggest using to_timestamp()
instead of throwing (and it is also not IMMUTABLE
) to rule out some source of problems - DateStyle
is 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'));
See this related answer for a detailed explanation of this technique:
on this topic:
source to share