PostgreSQL LIKE Vs ILIKE: The Ultimate Guide To Pattern Matching

Pattern matching is a cornerstone of database querying. Whether you are building a search bar for an e-commerce platform, filtering user accounts by email domain, or generating reports from millions of unstructured text rows, finding specific text patterns is a daily requirement. In PostgreSQL, two of the most frequently used operators for this task are LIKE and ILIKE.

While they may look nearly identical on the surface, choosing the wrong one can lead to critical search bugs or severe application performance bottlenecks. Understanding how these operators behave, how they conform to relational database standards, and how they interact with index structures is essential for any database administrator or software developer working with PostgreSQL.

Understanding the Core Differences Between LIKE and ILIKE

The primary distinction between LIKE and ILIKE lies in how they handle character casing during evaluation. The LIKE operator is the standard SQL pattern matcher, and it is strictly case-sensitive. When you execute a query using LIKE, PostgreSQL compares the character strings byte-by-byte or character-by-character based on their exact casing. This means searching for the string "Admin" will fail to match "admin", "ADMIN", or any other casing variation. This behavior is standard across almost all relational database management systems (RDBMS) that adhere to ANSI SQL specifications.

Conversely, ILIKE is a custom PostgreSQL-specific extension. The "I" in ILIKE stands for "insensitive," indicating that the operator performs case-insensitive pattern matching. When using ILIKE, PostgreSQL automatically normalizes the casing of both the column value and the search pattern before performing the comparison. This allows a search for "%admin%" to successfully match "Admin", "ADMIN", and "aDmIn".

While ILIKE provides incredible convenience for user-facing applications—where search inputs are rarely formatted consistently—it comes with trade-offs. Because ILIKE is a PostgreSQL proprietary feature, using it makes your database schema and query syntax less portable to other systems like MySQL, Oracle, or Microsoft SQL Server. If your application might migrate to another database engine in the future, relying heavily on ILIKE can complicate the transition.

Syntactical Breakdown and Wildcard Usage

Both LIKE and ILIKE utilize the same wildcard characters to define patterns within text columns. These wildcards allow you to build flexible search criteria.

• The Percent Sign (%): Represents zero, one, or multiple characters. For instance, searching for 'auth%' matches any string that starts with "auth", such as "authority", "authorize", or simply "auth".
• The Underscore (_): Represents exactly one single character. Searching for 't_m' would match "tim" or "tom", but not "team".

To put this into perspective, consider a database table named customers with a column named last_name. If you run the query SELECT * FROM customers WHERE last_name LIKE 'sm_th';, PostgreSQL will return rows containing "smith" or "smoth", but it will completely ignore "Smith" or "SMITH" due to the case-sensitive nature of the LIKE operator.

If you rewrite that query as SELECT * FROM customers WHERE last_name ILIKE 'sm_th';, PostgreSQL will successfully return "smith", "Smith", "SMITH", and "Smoth". This case-folding behavior is highly dependent on the active database collation and locale settings, as PostgreSQL references these system libraries to determine which characters are considered equivalent across different case registers.

Performance Comparison: Indexing LIKE vs ILIKE

When querying small tables, the performance difference between LIKE and ILIKE is negligible. However, as your tables grow to hundreds of thousands or millions of rows, raw pattern matching can cause massive performance degradation due to full table scans. Understanding how indexing applies to both operators is crucial for maintaining a responsive database.

Standard B-Tree Indexes and Prefix Matching

A standard B-tree index on a text column can only optimize LIKE queries under very specific conditions. Specifically, the search pattern must be a prefix search, meaning the wildcard character is at the end of the string (e.g., LIKE 'target%'). In this scenario, PostgreSQL can traverse the B-tree index efficiently to find the matching range of values. If the wildcard is placed at the beginning of the string (e.g., LIKE '%target'), the index cannot be utilized, and PostgreSQL must resort to a sequential table scan.

With ILIKE, standard B-tree indexes are generally bypassed entirely, even for prefix searches. Because ILIKE is case-insensitive, a standard index organized by binary or locale-specific character order cannot be used directly to find matching values.

Optimizing LIKE and ILIKE with Functional and Trigram Indexes

To speed up case-insensitive searches without using ILIKE, developers historically used functional indexes combined with the standard LIKE operator. By creating an index on the lowercase representation of a column:

CREATE INDEX idx_users_lower_email ON users (LOWER(email));

You can then execute queries using LOWER(email) LIKE LOWER('InputString%'). This strategy allows PostgreSQL to leverage the functional B-tree index, resulting in lightning-fast execution times while maintaining compatibility with standard SQL operators.

For complex pattern matching where wildcards are placed at both ends of the search pattern (e.g., ILIKE '%keyword%'), standard B-tree indexes are useless. In these scenarios, PostgreSQL developers leverage the pg_trgm (trigram) extension. Trigrams break strings down into three-character chunks. By enabling this extension and creating a GIN (Generalized Inverted Index) or GiST (Generalized Search Tree) index:

CREATE INDEX idx_users_email_trgm ON users USING gin (email gin_trgm_ops);

PostgreSQL can perform incredibly fast substring searches, allowing both LIKE '%keyword%' and ILIKE '%keyword%' queries to execute in milliseconds rather than seconds.

Direct Comparison: LIKE vs ILIKE

Alternative Pattern Matching Methods in PostgreSQL

While LIKE and ILIKE are ideal for simple substring searches, PostgreSQL provides more advanced tools for sophisticated text processing.

Regular Expressions (POSIX)

If your patterns require complex logic, such as checking for specific digit counts or optional characters, PostgreSQL supports POSIX regular expressions. The operator ~ is used for case-sensitive regex matching, while ~* is used for case-insensitive regex matching. For example, phone ~ '^[0-9]{3}-[0-9]{3}-[0-9]{4}$' ensures a phone number column perfectly matches a specific format.

Full-Text Search (FTS)

For searching through massive blocks of prose, articles, or product descriptions, both LIKE and ILIKE fall short. PostgreSQL Full-Text Search parses documents into tokens, normalizes them into lexemes (such as converting "running" and "runs" to the root word "run"), and matches them using the @@ operator with tsvector and tsquery data types. FTS is highly optimized, supports linguistic ranking, and handles stop words automatically, making it the superior choice for document searches.

Frequently Asked Questions

Is ILIKE supported in other databases like MySQL or SQL Server?

No, ILIKE is a proprietary PostgreSQL extension. Other databases have their own ways of handling case insensitivity. For example, MySQL is often case-insensitive by default depending on the table collation, while SQL Server uses collation settings (e.g., SQL_Latin1_General_CP1_CI_AS where "CI" stands for Case Insensitive) to control this behavior globally or per query.

What is the performance impact of using ILIKE over LIKE?

On unindexed columns, ILIKE is slightly slower than LIKE because PostgreSQL must convert the characters to a uniform case during evaluation. On indexed columns, the performance difference can be massive if you do not have appropriate expression indexes or trigram indexes in place to support the case-insensitive search.

How do I escape wildcard characters like % and _ in a search?

If you need to search for a literal percent sign or underscore, you must use the ESCAPE clause. By default, the backslash (\) is the escape character. For example, to search for the string "10%", you would write LIKE '10\%' or specify a custom escape character using LIKE '10#%' ESCAPE '#'.

Are there operator symbols that correspond to LIKE and ILIKE?

Yes, PostgreSQL translates LIKE to the ~~ operator internally, and ILIKE is translated to ~~*. While you can write queries using these operators, it is generally recommended to stick to LIKE and ILIKE for readability and maintenance purposes.

Can I use LIKE and ILIKE with non-text data types?

No, both operators require text-based inputs. If you attempt to use them on integer, UUID, or date columns, PostgreSQL will throw a type mismatch error. You must explicitly cast the non-text column to text first, for example: created_at::text LIKE '2023-%'.

Take Control of Your Database Performance

Writing efficient queries is the backbone of any highly scalable application. If your database is slowing down due to sluggish search queries, unindexed wildcards, or misconfigured pattern matching, our database consulting team can help. We specialize in query optimization, indexing strategies, and PostgreSQL architecture tuning. Contact us today to schedule a database health audit and ensure your platform is running at peak efficiency.