create index on a jsonb timestamp field?

Поиск
Список
Период
Сортировка
От Larry Rosenman
Тема create index on a jsonb timestamp field?
Дата
Msg-id 78a5d1232bb5ef5797a8ae6e1f23543f@lerctr.org
обсуждение исходный текст
Ответы Re: create index on a jsonb timestamp field?  (Larry Rosenman <ler@lerctr.org>)
Re: create index on a jsonb timestamp field?  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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@lerctr.org
US Mail: 5708 Sabbia Dr, Round Rock, TX 78665-2106



В списке pgsql-sql по дате отправления:

Предыдущее
От: rajan
Дата:
Сообщение: Re: getting permission denied error for user2 while properprivileges are present
Следующее
От: Larry Rosenman
Дата:
Сообщение: Re: create index on a jsonb timestamp field?