Re: How to build a btree index with integer values on jsonb data?

Johann Spies wrote:
> 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.

Replace

COLLATE pg_catalog."default"

with

::integer

> Why btree index? I want to do queries like
>
> select stuff from sometable where pubyear between 2015 and 2018;

Because b-tree indexes are perfect for >= and <=.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com