Disconnecting case sensitive in Postgres

To perform a Postgres search regardless of the case, just change like to ilike

Your search looks like this:

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

In addition to the like operator, as Denis pointed out, we also have the regular expressions in PostgreSQL , which make searching very easy and allows you to "turn off" the sensitive case. To understand better we will use the table below example:

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

Regular Expressions:

  

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

Your difference from like is just that, you have a higher search capacity, you can search by patterns or strings, unlike like , which has a limitation for complex character search.

To use Regular Expressions, it is necessary to know some of the meta characters that are found when using Regex. I will not show them here, but it is worth learning .

Below are the regular expression operators.

• ~ - Corresponds to regular expression, case sensitive;
• ~* - Corresponds to regular expression, not case-sensitive;
• !~ - Does not match the regular expression, case sensitive;
• !~* - Does not match the regular expression, not case-sensitive.

Let's look at an example where we want to search for the word 'Joao Pedro' in our table. Note that in the search we want to search for the string with no difference of upper and lower case characters.

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

-- Retorno:

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

I'm not sure, my opinion, but I believe that when looking for case-insensitive the database places the desired word for lowercase and goes into each record by placing the word of the same in lower case and comparing to see if it is the same the desired one.

Operator like:

like is well known and does not require presentation. To use like in your queries is case-insensitive, just use the ilike operator. Think like this 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 expression vs. like:

Performance is relative to each situation. If you have a complex search using regular expression it will make the search much easier and bring much more performance to query . If you just need to do a simple search, from small words or just a name, like can be a great ally.