Postgres Case sensitivity

Will Adkin will.adkin at lineal.co.uk
Fri Jun 14 17:32:58 EDT 2019 

Thanks both, yep, we've got a bit more work to do...

I've been reading up on ILIKE vs UPPER and there are performance implications - if we want the field indexed we can create a custom UPPER index which will be used on the search, I'm not sure if ILIKE can still use indexes to find records or if it needs to do a full table search every time. In our app there are definitely cases where we need to avoid Postgres scanning the entire table so we will have to do a bit of testing before we move on from here...


Best regards

Will


         <http://www.lineal.co.uk/>        

 <https://twitter.com/linealsoftware>   <https://www.facebook.com/linealsoftware?fref=ts&ref=br_tf>   <https://plus.google.com/112982815000763473226/about>   <https://www.linkedin.com/company/1859834?trk=tyah&trkInfo=clickedVertical%3Acompany%2Cidx%3A1-1-1%2CtarId%3A1437729256906%2Ctas%3Alineal%20s>		
Will Adkin  / Software Development Manager  
will at lineal.co.uk <mailto:will at lineal.co.uk>
Lineal Software Solutions  
01271 375999  
Commercial House, The Strand
Barnstaple, Devon, EX31 1EU  
www.lineal.co.uk <http://www.lineal.co.uk/>


This e-mail message may contain confidential or legally privileged information and is intended only for the use of the intended recipient(s). Any unauthorized disclosure, dissemination, distribution, copying or the taking of any action in reliance on the information herein is prohibited. E-mails are not secure and cannot be guaranteed to be error free as they can be intercepted, amended, or contain viruses. Anyone who communicates with us by e-mail is deemed to have accepted these risks. Lineal is not responsible for errors or omissions in this message and denies any responsibility for any damage arising from the use of e-mail. Any opinion and other statement contained in this message and any attachment are solely those of the author and do not necessarily represent those of the company.


> On 14 Jun 2019, at 20:31, Doug Easterbrook <doug at artsman.com> wrote:
> 
> hi Will, 
> 
> I agree with Andy.    you are going to have to do something
> 
> me, I like  ‘iLike’   since I find people searching for text have become accustomed to ‘contains’ since they can’t remember if it was upper, lower, or at the end of the string
> 
> 
> there are some limitation of CItext in the documentation — I don’t know what the limitations of means since some are dependant on language.   I would imagine it works.
> 
> https://www.postgresql.org/docs/current/citext.html
> 
> 
> 
> you might also be able to coerce the search as in   company::citext = ‘lineal', but thats no different than the upper or lower functions - you have to modify sql.
> 
> 
> one of the things we did in our app is to make helpers for a number of the things we want in where clauses… eg
> 
> 
> text   select A from b
> text   where  date = [helper.$sqldate(dateField)]
> text    and    company [helper.$sqllike(variable)]
> 
> that way you can modify the helpers to be ilike, or add in escape codes, or remove quotes, etc as need be.
> 
> 
> 
> but I’d still bite the bullet and go with iLike or upper, depending on how your application behaves.
> 
> 
> 
> Doug Easterbrook
> Arts Management Systems Ltd.
> mailto:doug at artsman.com
> http://www.artsman.com
> Phone (403) 650-1978
> 
>> On Jun 14, 2019, at 9:02 AM, Andy Hilton <andyh at totallybrilliant.com> wrote:
>> 
>> Will
>> 
>> Check the ‘UPPER’ function in postgres - that may give you waht you need - in either way you will need specific sql whether using ilike or the upper function……
>> 
>> Andy
>> 
>> Andy Hilton
>> Totally Brilliant Software Inc
>> Phone (US) : (863) 858 4000 
>> Phone (UK) : 0207 193 8582
>> Web : www.totallybrilliant.com <http://www.totallybrilliant.com/> 
>> Helpdesk : http://totallybrilliant.kayako.com
>> Email : andyh at totallybrilliant.com
>> 
>>> On Jun 14, 2019, at 7:10 AM, Will Adkin via omnisdev-en <omnisdev-en at lists.omnis-dev.com> wrote:
>>> 
>>> 
>>> Hi List
>>> 
>>> At Lineal we've just spent the past year and a half converting customer from Frontbase to Postgres and we've just discovered a problem - Postgres does not seem to support case insensitive collations!
>>> 
>>> With FB we had the ability to specify a case insensitive coalition on every string column, which meant that searches on codes did not have to be case sensitive. WHERE company_name = 'lineal' would return a record with company_name = 'Lineal' and vice versa
>>> 
>>> No so now...
>>> 
>>> I'm aware the ILIKE function returns a case insensitive match, but before we embark on a massive project to replace most of our SQL Where clauses (Swapping '=' with iLIKE) is there another way that would be better?
>>> 
>>> Another option would be to replace all VARCHAR columns with CITEXT across the board...not a simple undertaking and CITEXT does not seem to be a core datatype, I'm not sure how reliable it would be?
>>> 
>>> Right now we're implementing ILIKE in places that need it most... anyone else come across this problem and came up with a neat way to get around it?
>>> 
>>> 
>>> 
>>> Best regards
>>> 
>>> Will
>>> 
>>> 
>>>        <http://www.lineal.co.uk/>        
>>> 
>>> <https://twitter.com/linealsoftware>   <https://www.facebook.com/linealsoftware?fref=ts&ref=br_tf>   <https://plus.google.com/112982815000763473226/about>   <https://www.linkedin.com/company/1859834?trk=tyah&trkInfo=clickedVertical%3Acompany%2Cidx%3A1-1-1%2CtarId%3A1437729256906%2Ctas%3Alineal%20s>		
>>> Will Adkin  / Software Development Manager  
>>> will at lineal.co.uk <mailto:will at lineal.co.uk>
>>> Lineal Software Solutions  
>>> 01271 375999  
>>> Commercial House, The Strand
>>> Barnstaple, Devon, EX31 1EU  
>>> www.lineal.co.uk <http://www.lineal.co.uk/>
>>> 
>>> 
>>> This e-mail message may contain confidential or legally privileged information and is intended only for the use of the intended recipient(s). Any unauthorized disclosure, dissemination, distribution, copying or the taking of any action in reliance on the information herein is prohibited. E-mails are not secure and cannot be guaranteed to be error free as they can be intercepted, amended, or contain viruses. Anyone who communicates with us by e-mail is deemed to have accepted these risks. Lineal is not responsible for errors or omissions in this message and denies any responsibility for any damage arising from the use of e-mail. Any opinion and other statement contained in this message and any attachment are solely those of the author and do not necessarily represent those of the company.
>>> 
>>> 
>>> _____________________________________________________________
>>> Manage your list subscriptions at http://lists.omnis-dev.com
>>> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com 
>> 
>> _____________________________________________________________
>> Manage your list subscriptions at http://lists.omnis-dev.com
>> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com 
> 
> _____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com
> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com

More information about the omnisdev-en mailing list