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
