From: | Larry Rosenman <ler(at)lerctr(dot)org> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: create index on a jsonb timestamp field? |
Date: | 2019-05-19 00:19:34 |
Message-ID: | f3a52b76c19564a78ce2b9fa8cb299d3@lerctr.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On 05/18/2019 5:53 pm, Larry Rosenman wrote:
> 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?
I found a work-around in making a column for query time, and populating
that in an UPDATE/INSERT trigger, and then making an index on that.
--
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2019-05-19 01:17:35 | Re: create index on a jsonb timestamp field? |
Previous Message | Larry Rosenman | 2019-05-18 22:53:35 | create index on a jsonb timestamp field? |