create index on a jsonb timestamp field?

From: Larry Rosenman <ler(at)lerctr(dot)org>
To: pgsql-sql(at)postgresql(dot)org
Subject: create index on a jsonb timestamp field?
Date: 2019-05-18 22:53:35
Message-ID: 78a5d1232bb5ef5797a8ae6e1f23543f@lerctr.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

I'm playing with DNSTAP (dnstap.info) data and loading it into a
database for analysis.

when I try to create an index on the query_time field of the json
structure I get:

ler=# select id,data->'message'->>'query_time' from dns_query limit 2;
id | ?column?
----+-----------------------------
2 | 2019-05-13T01:35:59.822984Z
3 | 2019-05-13T01:35:59.829801Z
(2 rows)

ler=# select id,(data->'message'->>'query_time')::timestamptz from
dns_query limit 2;
id | timestamptz
----+-------------------------------
2 | 2019-05-12 20:35:59.822984-05
3 | 2019-05-12 20:35:59.829801-05
(2 rows)

ler=#

ler=# create index dns_query_time_idx on dns_query(((data -> 'message'
->> 'query_time')::text::timestamptz));
ERROR: functions in index expression must be marked IMMUTABLE

Is there any easy way to do this? Or, what would the experts recommend
here?

--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 214-642-9640 E-Mail: ler(at)lerctr(dot)org
US Mail: 5708 Sabbia Dr, Round Rock, TX 78665-2106

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Larry Rosenman 2019-05-19 00:19:34 Re: create index on a jsonb timestamp field?
Previous Message rajan 2019-05-18 16:35:50 Re: getting permission denied error for user2 while proper privileges are present