On Wed, May 31, 2017 at 12:18 PM, armand pirvu <armand.pi...@gmail.com>
wrote:
>
> For the example mentioned
>
> SELECT *
> FROM cfg_files_data
> WHERE cfg_files_data.show_id = 32
> AND cfg_files_data.file_id = 123
> AND lower((file_data_record ->> 'Company')) = lower('CompuTestSystems');
> ;
>
>
> create index cfg_files_data_record_idx on cfg_files_data (show_id,
> file_id,
>
> lower(file_data_record::text));
>
>
> Not sure why the index is ignored
>
Because "lower((file_data_record ->> 'Company'))" is not the same
as "lower(file_data_record::text)"
> But is is possible to go for a broader search, aka being able to search by
> any key:value , efficient and case insensitive ? What am I missing in
> this picture ?
>
>
Use a trigger to maintain an all lower case copy of the json
file_data_record and use the copy for predicates while using the original
for select-list outputs.
David J.
