agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Larry Rosenman <ler@lerctr.org>
To: pgsql-sql@postgresql.org
Subject: create index on a jsonb timestamp field?
Date: Sat, 18 May 2019 17:53:35 -0500
Message-ID: <78a5d1232bb5ef5797a8ae6e1f23543f@lerctr.org> (raw)
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
view thread (5+ messages) latest in thread
Message-ID: <78a5d1232bb5ef5797a8ae6e1f23543f@lerctr.org>
Permalink: ../78a5d1232bb5ef5797a8ae6e1f23543f@lerctr.org/
Also on: postgresql.org/message-id/78a5d1232bb5ef5797a8ae6e1f23543f@lerctr.org
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ler@lerctr.org
Subject: Re: create index on a jsonb timestamp field?
In-Reply-To: <78a5d1232bb5ef5797a8ae6e1f23543f@lerctr.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox