Database and Migration Insights

Case-insensitive search is one of the most common issues I encounter when helping customers migrate from Oracle or SQL Server to PostgreSQL. Unlike Oracle (with NLS_SORT) or SQL Server (with case-insensitive collations), PostgreSQL does not natively support case-insensitive search.

Developers typically rely on workarounds such as:

• Wrapping comparisons with LOWER() or UPPER()
• leverage citext data type (not recommended)
• Leveraging ICU-based nondeterministic collations

Why citext is not recommended.
Reference – https://www.postgresql.org/docs/current/citext.html

In this post, I’ll focus on nondeterministic collations and their behavior with LIKE or ILIKE operators.

Understanding Nondeterministic Collations

In PostgreSQL, a nondeterministic collation lets text comparisons ignore case/accents, enabling flexible searches like case-insensitive equality.

creating sample collation to support case insensitive search.

CREATE COLLATION collate_ci (provider = icu, deterministic = false, locale = 'en-US-u-ks-level2');

Let’s create a sample table with columns using nondeterministic collation:

create table testcollation(col1 text collate collate_ci); 
insert into testcollation values ('a');
insert into testcollation values ('A');

select * from testcollation where col1 = 'a';
 col1 
------
 a
 A
(2 rows)

Using nondeterministic collations helps us implement case-insensitive comparison with equality operators. However, let’s examine how this works with LIKE or ILIKE operators for wildcard-based searches.

The Problem: Wildcard Operator Limitations

While nondeterministic collations enable case-insensitive searches, they fail when used with wildcard operators (LIKE or ILIKE) because this functionality is not supported in PostgreSQL versions prior to 18:

postgres=> select * from testcollation where col1 like 'a%';
ERROR:  nondeterministic collations are not supported for LIKE

PostgreSQL 17 does not support LIKE/ILIKE with nondeterministic collations.
Let’s explore two workarounds.

Solution Approaches

We will examine two alternative approaches to mitigate the limitation of nondeterministic collations when using wildcard operators.

Option 1 :- Using buildin “C” Collation with ilike

PostgreSQL offers multiple built-in collations for different use cases. You can view available collations using the following psql command:

postgres=# \dOS+

List of collations
┌────────────┬────────────────────────┬──────────┬─────────────┬─────────────┐
│   Schema   │  Name │ Provider │  Collate  │ Ctype  │  Locale │ ICU Rules │ Deterministic? │
├────────────┼────────────────────────┼──────────┼───────────┤
│ pg_catalog │ C   │ libc     │ C     │ C   │    │    │ yes            │
│ pg_catalog │ POSIX  │ libc  │ POSIX │ POSIX    │   │   │ yes            │
........

In PostgreSQL, the C collation is one of the built-in collations that comes from the underlying C library (libc).

The Workaround: Override the collation with “C” and use it in SQL statements that require the LIKE operator. Such occurrences can be identified in views or procedural code and fixed proactively as part of the migration process.

To support case-insensitive searches, map the LIKE operator to ILIKE while applying the “C” collation:

postgres=# select * from testcollation where col1 collate "C" ilike 'a%';
┌──────┐
│ col1 │
├──────┤
│ a    │
│ A    │
└──────┘
(2 rows)

Option 2: Using Custom Operators with Internal “C” Collation

PostgreSQL’s extensibility allows us to create custom operators. By defining our own, we can extend the behavior of LIKE and ILIKE to overcome this limitation.

Understanding Internal Operators: When we use the LIKE operator, it is internally transformed to ~~ as shown in the execution plan. Similarly, the ILIKE operator uses ~~* internally.

Similarly, we can identify operators for ILIKE operators as well, i..e ~~*

Creating Custom Functions: To create a custom operator in PostgreSQL, we first need supporting functions. PostgreSQL provides built-in functions that we can leverage to implement our own comparison logic.

Sample function samples using texticlike to exhibit case insensitive search.

To avoid code changes while preserving case-insensitive functionality, we can create a custom schema with operators that override ~~, the internal operator used by LIKE:

Sample code on creating custom operator, for more information check out here.

create schema customoperator;

CREATE OR REPLACE FUNCTION customoperator.custom_texticlike(text, text) RETURNS boolean
    LANGUAGE sql IMMUTABLE STRICT
    AS $_$SELECT pg_catalog.texticlike($1 collate "C",$2 collate "C");$_$;

CREATE OR REPLACE FUNCTION customoperator.custom_texticnotlike(text, text) RETURNS boolean
    LANGUAGE sql IMMUTABLE STRICT
    AS $_$SELECT pg_catalog.texticnlike($1 collate "C",$2 collate "C");$_$;

CREATE OPERATOR customoperator.~~ (
    FUNCTION = customoperator.custom_texticlike,
    LEFTARG = text,
    RIGHTARG = text,
    COMMUTATOR = OPERATOR(customoperator.~~),
    NEGATOR = OPERATOR(customoperator.!~~)
);

CREATE OPERATOR customoperator.!~~ (
    FUNCTION = customoperator.custom_texticnotlike,
    LEFTARG = text,
    RIGHTARG = text,
    COMMUTATOR = OPERATOR(customoperator.!~~),
    NEGATOR = OPERATOR(customoperator.~~)
);

Important Consideration:
Search Path Precedence When overriding built-in operators, we must consider the precedence of pg_catalog within the search_path. The search_path tells PostgreSQL which schemas to search when you reference an object without a schema name. By default, the system schema pg_catalog—which contains all built-in functions and operators—is always included.

To make our custom operators take priority over the built-in ones, we must place our custom schema before pg_catalog in the search_path:

Setting custom search_path with customoperator schema precede pg_catalog.

postgres=# set search_path to customoperator,pg_catalog, "$user", public;

Once the search_path is updated, any LIKE operator in SQL will automatically use the custom operator and its function. In effect, every LIKE clause is transparently handled as a case-insensitive match with “C” collation:

postgres=# explain select * from testcollation where col1 like 'a%';
┌──────────────────────────────────────────────────────────────────┐
│                            QUERY PLAN                            │
├──────────────────────────────────────────────────────────────────┤
│ Seq Scan on testcollation  (cost=0.00..27.00 rows=1360 width=32) │
│   Filter: texticlike((col1)::text, 'a%'::text COLLATE "C")       │
└──────────────────────────────────────────────────────────────────┘
(2 rows)

Comparison of Solutions:-

Final Recommendations

• Use Option 1 (Collate “C” + ILIKE) for production environments — it’s safer and more explicit Consider
• Option 2 (custom operators) only if code changes are impractical — due to the global impact on search_path
• Plan to re-test with PostgreSQL 18 once it becomes generally available — for native support

Note: Overwriting search_path to change pg_catalog precedence can have minor impacts on all sessions and is not generally recommended as best practice.

Looking Ahead: PostgreSQL 18 Enhancements

PostgreSQL 18 introduces significant enhancements related to collations, particularly around nondeterministic collations:

• New CASEFOLD collation feature to further assist with case-insensitive matches
• Native support for LIKE comparisons on text columns that use nondeterministic (case-insensitive) ICU collations
• Simplified pattern matching on case-insensitive text, making it more consistent across operations

These improvements will make pattern matching on case-insensitive text much simpler and eliminate the need for the workarounds described in this post.

Conclusion

While PostgreSQL’s current limitations with nondeterministic collations and LIKE operators present challenges for case-insensitive searches, both workarounds provide viable solutions. Choose the approach that best fits your migration strategy and maintenance requirements, keeping in mind the upcoming native support in PostgreSQL 18.

Reference 

https://aws.amazon.com/blogs/database/manage-case-insensitive-data-in-postgresql/(PostgreSQL 15)