Re: jsonb case insensitive search

On Wed, May 31, 2017 at 12:18 PM, armand pirvu <armand(dot)pirvu(at)gmail(dot)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.