Case insensitive Select from PostgresSQL

I want to perform a text search on my Postgress table and I want the search to be case -Insensitive.

I have seen some examples of case-insensitive searches using the UPPER or LOWER command, but this would consume a lot of resources.
Doesn't Postgress have option for the SELECT cmd to ignore text case ?

Thank you.
sidwelleAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johnsoneSenior Oracle DBACommented:
I would use a function based index, documented here.  Create an index on either LOWER or UPPER of the column.  Then make sure that you use that function in your query.  That should significantly improve the performance.  You would still use the function on both sides of the equality, so the query still looks like:

where lower(col) = lower('aBc')

But, rather than doing a full table scan, it should just scan the function based index.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sidwelleAuthor Commented:
The DB belongs to an application that we have in house. I don't have the option to change or add anything, only query.

Using 'Lower' worked, just didn't think it was the best solution.
I just thought the postgress would have had a built in option to query case insensitive.

Thanks
0
johnsoneSenior Oracle DBACommented:
If you cannot create an index, then I would look at ILIKE or regular expressions.  Those can be found here.  However, I don't think there is going to be much of a performance difference with using LOWER, but you can try.
0
sidwelleAuthor Commented:
Thanks for the help.

The 'lower' cmd didn't slow the query down as much as I thought it would.

Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.