I want to create an index on a column which stores a timestamp inside a JSON value in Postgres. The index needs to be converted to a timezone as well. This is the query I need to run,
SELECT count(*) FROM reservations
WHERE (((json #>> ‘{details, attributes, checkIn}‘)::timestamptz
at time zone
(json #>> ‘{details, attributes, destinationTimeZone}’)
)) >= ‘2020-03-17’
AND (((json #>> ‘{details, attributes, checkIn}‘)::timestamptz
at time zone
(json #>> ‘{details, attributes, destinationTimeZone}’)
)) < ‘2020-04-01’;
I tried creating indexes as:
1)
CREATE INDEX CONCURRENTLY hotelUuid_checkIn_index3 ON reservations
(TO_TIMESTAMP(json ->> '{details,attributes,checkIn}')::timestamptz);
error: "functions in index expression must be marked IMMUTABLE"
2)
CREATE INDEX CONCURRENTLY hotelUuid_checkIn_index3 ON reservations
(TO_TIMESTAMP(json ->> '{details,attributes,checkIn}'));
error: "No function matches the given name and argument types. You might need to add explicit type casts."
3)
CREATE INDEX CONCURRENTLY hotelUuid_checkIn_index3 ON reservations
(((json ->> '{details,attributes,checkIn}')::timestamptz) AT TIME ZONE 'America/SanTiego');
error: "No function matches the given name and argument types. You might need to add explicit type casts"
I am aware that this is not the correct way to create index, since it will be an overhead but at this point of time i do not want to change my data insertion logic. That's why I need to create an index.
I am using Postgresql 9.4.8 version.