LIKE and ILIKE are used for pattern matching in PostgreSQL. LIKE is the SQL standard while ILIKE is a useful extension made by PostgreSQL.
To begin with, we will create a tiny table with few random string values.
CREATE TABLE string_collection ( string character varying )
% sign in a pattern matches any sequence of zero or more characters.
_ in a pattern matches any single character.
For instance, consider the following query:
SELECT string FROM string_collection WHERE string LIKE 'O%';
Following query illustrate the use of _:
SELECT string FROM string_collection WHERE string LIKE '_n_';
It is also possible to combine _ and % to get the desired result:
SELECT string FROM string_collection WHERE string LIKE '_n%';
SELECT string FROM string_collection WHERE string ILIKE 'O%';
The same effect can be achieved using the ‘lower()’ function and LIKE of PostgreSQL.
SELECT string FROM string_collection WHERE lower(string) LIKE ‘o%’;
A performance comparison of LIKE with lower vs ILIKE can be read here.
LIKE and ILIKE can be preceded with NOT to get the reverse effect:
SELECT string FROM string_collection WHERE string NOT LIKE 'O%';
A % without any other character matches all strings:
SELECT string FROM string_collection WHERE string LIKE '%';
To do so, we have to escape them using the default escape character – backslash.
SELECT string FROM string_collection WHERE string LIKE '%\%';
Underscores can also be mapped in the same way:
SELECT string FROM string_collection WHERE string LIKE '%\_%';
It matches any strings which contain an underscore.
~~ can be used instead of LIKE and also:
~~* for ILIKE
!~~ for NOT LIKE
!~~* for NOT ILIKE
Indexing and LIKE/LIKE
Indexes are used to fasten the search. PostgreSQL automatically create indexes for columns which are Primary Keys, Unique, etc. Or we can create indexes explicitly.
If an Index is available for the column, the LIKE utilizes it, if the pattern doesn’t start with % or _.
So, col LIKE ‘one%’ use index, while col LIKE ‘%one’ does not.
ILIKE make use of index if and only if the pattern start with a non-alphabetic character(character not affected by case conversions).
Bonus Point: Mirrored Indexes
As we discussed LIKE would not use Index to search if the pattern start with % or _.
A work around for this is following:
- Create a index on the column using the reverse() function.
- Search with reversed pattern.
For example, the following query doesn’t use indexes:
SELECT string FROM string_collection WHERE string LIKE '%wo';
Now, we create an index:
CREATE INDEX rev_idx ON string_collection (reverse(string));
And rewrite our query:
SELECT string FROM string_collection WHERE reverse(string) LIKE reverse('%wo');
Now it will use index.
That’s it. Thank You!
Latest posts by Vipin Raj (see all)
- Relation Between Games and Studies [Infographics] - April 3, 2017
- How to Access Ubuntu PC Files From Your Android Phone - February 7, 2016
- How to Install BSNL EVDO in Ubuntu / Mint in 3 Minutes - January 27, 2016