Disconnect case sensitive in Postgres

Question

When I make the command select * from tabela where name like '%teste%' the line with the name TesTe returns, but the same does not happen in postgres, I do not want to put lower(name) to work, how can I disable case sensitive in a column?

Answer 1

To perform a search in Postgres disregarding upper or lower case, just change the like for ilike

Your search gets like this:

select * from tabela where name ilike '%teste%'

Answer 2

Besides the operator like, as Denis pointed out we also have the regular expressions in Postgresql, that make it much easier to search and allow you to "turn off" the case sensitive. To better understand let’s use the table below example:

# ------------+-----------
#    name     | username
# ------------+-----------
#  Joao Pedro | Joaopedro
# ------------+-----------
#  joao pedro | joaopedro
# ------------+-----------
# (2 linhas)

Regular Expressions:

A regular (or Regex) expression provides a concise and flexible form of identify character strings of interest, such as characters particular words or patterns of characters.

Its difference from the like is just this, you have a greater search ability, can search for patterns or strings, unlike the like, which has limitation for complex character searches.

To use Regular Expressions, it is necessary to know some meta characters that are found in the use of Regex. I won’t show them here, but it’s worth the learning.

Down here are regular expression operators.

• ~ - Corresponds to regular expression, differentiating upper and lower case;
• ~* - Matches regular expression, no case differentiation;
• !~ - Does not match regular expression, differentiating between upper and lower case;
• !~* - Does not match regular expression, no case differentiation.

Let’s see an example where we want to look for the word 'Joao Pedro' within our table. Note that in the search we want to search for the string without difference of uppercase and lowercase characters.

SELECT name FROM <tabela> WHERE name ~* 'Joao Pedro'

-- Retorno:

# ------------|
#    name     |
# ------------|
#  Joao Pedro |
# ------------|
#  joao pedro |
# ------------|
# (2 linhas)

I am not sure, my opinion, but I believe that when searching without difference of uppercase and lowercase the database puts the desired word to lowercase and goes in each record putting the word of the same in lowercase and comparing to see if it is equal to desired.

Operator like:

The like is very well known and needs no introduction. To use the like in your queries without difference of uppercase and lowercase just use the operator ilike. Think like that to make it easier:

• like - Sensitive (Sensitive)
• ilike - Insensitive (Insensitive)

See the following query:

SELECT name FROM users WHERE name ilike 'joao pedro'

It will return the same result as the regular expression example. Its only difference is in the operator.

Performance - Regular vs like expression:

The performance is relative to each situation. If you have a complex search using regular expression will greatly facilitate the search and bring much more performance to the query. If you just need to do a simple search, of, small words or just by a name like can be a great ally.