Difference between LIKE and ILIKE in PostgreSQL

Hey guys, What `s up.
I`m kossy.

In this article, I try to describe 「Difference between LIKE and ILIKE in PostgreSQL」.

Environments

PostgreSQL 12.5
Rails 6.0.3

Try to LIKE

First, read a document.

9.7.1. LIKE
string LIKE pattern [ESCAPE escape-character]
string NOT LIKE pattern [ESCAPE escape-character]
The LIKE expression returns true if the string matches the supplied pattern. (As expected, the NOT LIKE expression returns false if LIKE returns true, and vice versa. An equivalent expression is NOT (string LIKE pattern).)
If pattern does not contain percent signs or underscores, then the pattern only represents the string itself; in that case LIKE acts like the equals operator. An underscore (_) in pattern stands for (matches) any single character; a percent sign (%) matches any sequence of zero or more characters.
LIKE pattern matching always covers the entire string. Therefore, if it's desired to match a sequence anywhere within a string, the pattern must start and end with a percent sign.
To match a literal underscore or percent sign without matching other characters, the respective character in pattern must be preceded by the escape character. The default escape character is the backslash but a different one can be selected by using the ESCAPE clause. To match the escape character itself, write two escape characters.
Ref: https://www.postgresql.org/docs/current/functions-matching.html

Although not wrote in this document, LIKE searches are case-sensitive.

For example, following record in Database.

employees
id: 1,
first_name: "Ben",
last_name: "Wallace",

Run following Query

Employee.where('first_name LIKE ?', '%Ben%')
=>   Employee Load (3.4ms)  SELECT "employees".* FROM "employees" WHERE (first_name LIKE '%Ben%')
[#<Employee:0x000055b9718b3460
  id: 1,
  first_name: "Ben",
  last_name: "Wallace",

Got a record with “Ben”.

Then, Run Query with “ben”.

Employee.where('first_name LIKE ?', '%ben%')
=>   Employee Load (6.5ms)  SELECT "employees".* FROM "employees" WHERE (first_name LIKE '%ben%')
[]

Return a blank array because “LIKE” searches are case-sensitive.

Next, Run Query with “ILIKE”.

Employee.where('first_name ILIKE ?', '%ben%')
=>   Employee Load (6.9ms)  SELECT "employees".* FROM "employees" WHERE (first_name ILIKE '%ben%')
[#<Employee:0x000055b971bf6488
  id: 1,
  first_name: "Ben",
  last_name: "Wallace">]

In case of “ILIKE”, Got a record with lower case. “ILIKE” is case insensitive.

as a side note, MySQL’s LIKE is case-insensitive by default.

https://dev.mysql.com/doc/refman/5.6/en/pattern-matching.html

Conclusion

Use LIKE if you want the search to be case sensitive, and use ILIKE if you want the search to be case insensitive.

References

PostgreSQLにおけるLIKEとILIKEの違いを検証してみた - その辺にいるWebエンジニアの備忘録

9.7. Pattern Matching