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?
Re: create index on a jsonb timestamp field? |
Список | 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 по дате отправления: