How to Write Case-Insensitive Queries in PostgreSQL

To match text without requiring the same letter case, use ILIKE for pattern searches, compare normalized values with LOWER or UPPER, or use a CITEXT column when case-insensitive equality is the column’s usual behavior. The right choice depends on whether you need a pattern, how the column is queried, and which indexes and locale settings apply.

The examples below use an animal table with id, age, and type columns, including a row whose type is Kitten. The SQL is illustrative and has not been run against a PostgreSQL server.

Use ILIKE for case-insensitive pattern matching

For case-insensitive equality, write type ILIKE 'kitten'. For a pattern that can appear within a value, use wildcards, such as type ILIKE '%kitten%'. % matches any sequence of characters, while _ matches one character. ILIKE is PostgreSQL’s case-insensitive counterpart to LIKE; its behavior follows the active locale.

LIKE remains case-sensitive in ordinary use, so type LIKE 'kitten' does not match Kitten under a case-sensitive collation. SIMILAR TO uses SQL regular-expression-like pattern syntax, while ILIKE uses the simpler LIKE wildcard syntax. They serve different pattern needs and are not interchangeable in every query. PostgreSQL’s ~~* operator is an alternative spelling for ILIKE.

Choose an exact pattern when you want equality-like matching. Add % or _ only when the search should match a broader pattern.

Normalize both sides of a comparison with LOWER or UPPER

For case-insensitive equality, apply the same case conversion to the column and the search value: LOWER(type) = LOWER('kitten'). You can instead use UPPER on both sides. LOWER is a function that converts text according to the database locale; INITCAP capitalizes words and is not a general substitute for case-insensitive comparison.

Case conversion and comparisons can depend on locale and collation, particularly for language-specific characters. Do not assume that converting text works only for English, or that every locale treats all characters identically. Choose and test the database locale and collation that fit the data and comparison rules.

Add indexes for case-insensitive searches

A regular index on type may not support a predicate on LOWER(type) as an equivalent index expression. For normalized equality queries, create an expression index with the same expression as the query, for example CREATE INDEX animal_type_lower_idx ON animal (LOWER(type));. An analogous UPPER(type) index can support a query that uses UPPER(type).

An index is useful only when its expression and operator support the query and the planner chooses it; it does not guarantee a faster query. PostgreSQL’s pg_trgm extension can provide GIN or GiST trigram indexes for supported LIKE and ILIKE searches. Whether that helps depends on the pattern, table, and workload. Check plans and timings with representative data before choosing an index.

If duplicate values ignoring case must be rejected, a unique expression index such as CREATE UNIQUE INDEX animal_type_lower_unique ON animal (LOWER(type)); can enforce that rule for values covered by the expression. This is a schema decision: confirm it matches the intended data rules before applying it.

Use CITEXT for case-insensitive columns

The CITEXT extension provides a case-insensitive text type for comparisons, so ordinary equality can match values without spelling the same case. Enable the extension with CREATE EXTENSION citext;, then declare the column as CITEXT, for example type CITEXT. A query such as SELECT id, age, type FROM animal WHERE type = 'kitten'; can then match a stored value of Kitten according to the type’s case-insensitive comparison behavior.

CITEXT is convenient when case-insensitive comparison is the default for a column. It is not automatically faster or less expensive than other approaches, and it does not remove the need to consider indexes, collation, and workload. The extension must be available and enabled in the database. Review its documented comparison behavior for your locale and data before adopting it.

For a portable illustration of the normalization idea, this Python example compares two ASCII spellings. It demonstrates Python string behavior only; it does not execute PostgreSQL SQL or establish database collation behavior.

stored_type = "Kitten"
search_type = "kitten"
print(stored_type.lower() == search_type.lower())

True