create index on a jsonb timestamp field?
Larry Rosenman <[email protected]> Sat, 18 May 2019 17:53:35 -0500
| Newsgroups | gmane.comp.db.postgresql.sql |
|---|---|
| Message-ID | <[email protected]> |
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: [email protected] US Mail: 5708 Sabbia Dr, Round Rock, TX 78665-2106