How to search a table in Postgres with different case?

Question

PostgreSQL

Good afternoon, the table contains the names and surnames of people. I do a table search from nodejs
and display it on the site. The problem is that if Ivan Ivanov is recorded, then the query with the text "ivan ivanov" will not find anything. If you store everything in lowercase, then on the site I will not be able to correctly display the full name with a capital letter. What to do?
I'm using: nodejs typeform

Answer 1

Try to lowercase everything using the LOWER() function ( You need to bring the field in which you are looking for and the search text entered by the user)
upd LOWER() use only in the request, the data itself does not need to be changed

Answer 2

There are several options, for example:
1. Make the WHERE condition full_name ilike %ivan% in the query. The key here is the "smart" ilike operator , which tells the search to be case-insensitive.
2. In the query, make the condition WHERE lower(full_name) like lower(% Ivan%) - about the same, but not so beautiful.
3. Use the CITEXT extension ( https://www.postgresql.org/docs/9.4/citext.html), it is convenient if you need to take into account, for example, uniqueness control in the key. In CITEXT columns, the expression '[email protected]' == '[email protected]'.