Search Postgresql Archives

Re: jsonb case insensitive search

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




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

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


I see, missed it 

Cause this works
create index fooidx on cfg_files_data (show_id, file_id, lower(file_data_record ->> 'Company'));

The idea is that I would like to avoid having an index for each key possibly wanted to search, but rather say have the whole record then search by the key and get the key value, thus having one index serving multiple purposes so to speak


I looked at JSQuery but does not seem that I can have a composite index like fooidx ???!




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.


Uhh that would be let’s just say less than optimal. But maybe JSON itself is not intended to be used this way ? Or the functionality is just not there yet ?


Thanks
Armand




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux