From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Larry Rosenman <ler(at)lerctr(dot)org> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: create index on a jsonb timestamp field? |
Date: | 2019-05-19 01:17:35 |
Message-ID: | 29956.1558228655@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Larry Rosenman <ler(at)lerctr(dot)org> writes:
> when I try to create an index on the query_time field of the json
> structure I get:
> 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
Yeah, because the timestamptz input function has dependencies on
both the datestyle and timezone GUCs. Given that your input is
ISO-format with explicit time zone, you don't really care about
either of those things, but the mutability check doesn't know that.
> Is there any easy way to do this? Or, what would the experts recommend
> here?
The sanest way to deal with this IMO is to make a column containing
the extracted timestamp, which you could maintain with a trigger,
and then index that. You could alternatively make a custom function
that you (mis?)label as immutable, but your queries would have to
use that same function in order to get matched to the index, so
I dunno about that being a user-friendly approach.
BTW, I'd had the idea that the GENERATED option in PG v13 would allow
setting up this sort of case without bothering with a handwritten trigger,
but it seems not:
regression=# create table foo(data jsonb, ts timestamptz GENERATED ALWAYS AS ((data->>'ts')::timestamptz) stored);
psql: ERROR: generation expression is not immutable
I wonder if that's really necessary to insist on?
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | RAJIN RAJ K | 2019-05-19 16:00:23 | Table as argument in postgres function |
Previous Message | Larry Rosenman | 2019-05-19 00:19:34 | Re: create index on a jsonb timestamp field? |