Postgresql Index creation for Timestamp in a given Timezone

184 Views Asked by At

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.

0

There are 0 best solutions below