Re: [GENERAL] jsonb case insensitive search

On May 31, 2017, at 2:32 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:

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