Postgres Case sensitivity

[Bastiaan Olij]

Hi Will,


What you can do is create your search index with upper(COLUMN), then you can do upper(company_name) = 'LINEAL' and just upper case your search criteria.
That said, we use ilike in many scenarios seeing we rarely search on an exact match. 

Kindest Regards,


Bastiaan Olij
Head of development - Instinct Systems: The JobBag People
Ground Floor, 48 Chandos Street
St Leonards NSW 2065
Australia


Phone: +61 2 8115 8000
Direct: +61 2 8115 8003
Mobile: +61 4 321 44833
bastiaan.olij at instinctsystems.com.au
http://www.jobbag.com



 From:   Will Adkin via omnisdev-en <omnisdev-en at lists.omnis-dev.com> 
 To:   OmnisDev List - English <omnisdev-en at lists.omnis-dev.com> 
 Cc:   Will Adkin <will.adkin at lineal.co.uk> 
 Sent:   6/14/2019 9:10 PM 
 Subject:   Postgres Case sensitivity 


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