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?
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.