MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII;
 format=flowed
Content-Transfer-Encoding: 7bit
Date: Sat, 18 May 2019 19:19:34 -0500
From: Larry Rosenman <ler@lerctr.org>
To: pgsql-sql@postgresql.org
Subject: Re: create index on a jsonb timestamp field?
In-Reply-To: <78a5d1232bb5ef5797a8ae6e1f23543f@lerctr.org>
References: <78a5d1232bb5ef5797a8ae6e1f23543f@lerctr.org>
Message-ID: <f3a52b76c19564a78ce2b9fa8cb299d3@lerctr.org>
User-Agent: Roundcube Webmail/1.3.9
Precedence: bulk

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@lerctr.org
US Mail: 5708 Sabbia Dr, Round Rock, TX 78665-2106