| From: | Johann Spies <johann(dot)spies(at)gmail(dot)com> |
|---|---|
| To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
| Subject: | How to build a btree index with integer values on jsonb data? |
| Date: | 2018-12-06 13:43:17 |
| Message-ID: | CAGZ55DSm4kGiBSJaPtTk6L7hp6xDFKaKThDLmXd_4cCFFFJe5Q@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
How can I transform the following definition to index pubyear as
integer and not text?
CREATE INDEX pubyear_idx
ON some_table_where_data_field_is_of_type_jsonb USING btree
((((((data -> 'REC'::text) -> 'static_data'::text) ->
'summary'::text) -> 'pub_info'::text) ->> '@pubyear'::text) COLLATE
pg_catalog."default");
While I can cast the value in a SELECT statement to integer I have
been able to do the same while creating the index.
Why btree index? I want to do queries like
select stuff from sometable where pubyear between 2015 and 2018;
Regards
Johann
--
Because experiencing your loyal love is better than life itself,
my lips will praise you. (Psalm 63:3)
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Rene Romero Benavides | 2018-12-06 14:46:21 | Re: order of reading the conf files |
| Previous Message | Pavel Stehule | 2018-12-06 11:21:33 | Re: debugging intermittent slow updates under higher load |