ILIKE with Case Insensitive Data
We look at the ILIKE operator and see why it may not be well suited for doing full case-insensitive string comparisons. This episode is brought to you by Hashrocket, expert consultants in PostgreSQL - learn more at https://hashrocket.com and www.pgcasts.com
Transcript:
In previous episodes, we looked at different ways of working with
case-insensitive data. We wanted to query for user records by their email
addresses in a case-insensitive way without sacrificing performance.
One approach was to use the `lower()` function for comparisons in conjunction
with a functional index. Another approach was to pull in the `citext` module.
Both of these approaches get the job done and they have pretty similar
performance characteristics. However, they required us to jump through some
hoops by either creating an additional index or pulling in an extension. You
may be wondering, wouldn't it be easier to just use the `ilike` operator for
case-insensitive string comparisons?
Let's see.
We already have a `users` table with 10,000 records.
```sql
\d users
select * from users limit 10;
```
First, let's perform an `explain analyze` when querying for a user record by
email.
```sql
explain analyze select * from users where lower(email) = lower('[email protected]');
```
And now, let's do a similar query ditching the uses of the `lower()`
function and instead using the `ilike` operator.
```sql
explain analyze select * from users where email ilike '[email protected]';
```
The `ilike` query is going to perform much more slowly. It forces Postgres
to do a full sequential scan. This is because it is not able to utilize our
functional b-tree index. It wouldn't be able to utilize the more basic
b-tree index either. The `ilike` operator and its friends are intended for
pattern matching comparisons, so they are not able to take advantage of
b-tree indexes.
For these kinds of full case-insensitive string comparisons we are better
off using the previously discussed approaches.
That said, you'd think we'd be able to do indexed queries when working with the
pattern matching operators. The trigram module and a gist index can help us
there, but that is a topic for another episode.
Until then, may your data be consistent and your queries performant.
Resources
[PostgreSQL's Pattern Matching
Operators](http://www.postgresql.org/docs/curren...)
[Citext for emails](/episodes/13/citext-for-emails)
[Working With Case-Insensitive
Data](/episodes/8/working-with-case-insensitive-data) and [More
Case-Insensitive Data](/episodes/9/more-case-insensitive-data)
Watch video ILIKE with Case Insensitive Data online without registration, duration hours minute second in high quality. This video was added by user PG Casts by Hashrocket 10 August 2016, don't forget to share it with your friends and acquaintances, it has been viewed on our site 1,285 once and liked it 15 people.