Case-insensitive pattern matching in PostgreSQL

 

 

(Updated on 2024-11-27) Case-insensitive search is a much-requested feature, partly (I suspect) to maintain compatibility with Microsoft SQL Server. There are several solutions to the problem, one of which is to use case-insensitive ICU collations. This works like a charm, except if you want to perform pattern matching. So let's have a closer look at the problem and at possible solutions.

Alternatives for case-insensitive search

There are three known solutions to case-insensitive search in PostgreSQL:

Explicit conversion with lower() or upper()

A query that uses this method would look as follows:

This can be made fast with a B-tree index on lower(col), but has two disadvantages:

• the solution is implemented at the application level, that is, you have to custom-tailor the query for case-insensitive search
• if the database column contains long values, the whole value has to be converted to lower case, even if only a few characters have to be compared, which leads to bad performance

Using the citext extension

The extension citext provides a data type citext, which stands for “case-insensitive text”. The table is defined as

and the query is as simple as

That is simple and convenient, but has some disadvantages as well:

• there is no data type civarchar, so you can only implement that with a check constraint
• performance for longer values can also be bad, because citext internally calls lower(col COLLATE "default") before comparing the values
• regular expression matching is not case insensitive, and you have to use the case insensitive operator ~* explicitly

Using case-insensitive ICU collations

If you are using PostgreSQL v12 or better, and PostgreSQL was configured --with-icu, you can define a case-insensitive collation like this:

“Deterministic” means that equality can be established by byte-for-byte equality comparison. In that sense, case-insensitive collations are non-deterministic, since 'Case' = 'case', even though they are not encoded as the same bytes.

The table would be defined as

and the query again is straightforward:

For more details about ICU collations, read my article on that topic.

The trouble with pattern matching and case-insensitive collations

Case-insensitive collations are the most elegant solution for the problem. There are two shortcomings with this approach:

• even though PostgreSQL v15 allows you to use ICU collations as the default database collation, this does not extend to case-insensitive collations, so you still have to explicitly specify the collation for each column definition
• you cannot use case-insensitive collations with pattern matching in PostgreSQL versions below v18

The first problem is merely an annoyance, but the second problem will require more thought. Here are two examples that exhibit the problem:

Why is that not supported?

The difficult case of German soccer

The ICU documentation details why correct case-insensitive pattern matching is difficult. A good example is the German letter “ß”, which traditionally doesn't have an upper-case equivalent. So with good German collations (the collation from the GNU C library is not good in that respect), you will get a result like this:

Now what would be the correct result for the following query in a case-insensitive collation?

You could argue that it should be TRUE, because that's what you'd get for upper('Fußball') LIKE 'FUS%'. On the other hand,

so you could just as well argue that the result should be FALSE. The ICU library goes with the second solution for simplicity. Either solution would be difficult to implement in PostgreSQL, so we had given up on that issue until PostgreSQL v18. Compare this quote from the mailing list:

ICU also provides regexp matching, but not collation-aware, since
character-based patterns don't play well with the concept of collation.
About a potential collation-aware LIKE, it looks hard to implement,
since the algorithm currently used in like_match.c seems purely
character-based. AFAICS there's no way to plug calls to usearch_*
functions into it, it would need a separate redesign from scratch.

Case insensitive pattern matching in PostgreSQL v18

PostgreSQL commit 85b7efa1cd introduced pattern matching with LIKE for non-deterministic collations. How did that patch solve the conundrum from the previous section? Well, Peter Eisentraut figured out that LIKE is actually pretty well-defined by the SQL standard, and that the standard decrees that the pattern string be split on the patterns, and each substring has to match.

So with the “Fußball” example, the pattern string would be split into FUS and %, and FUS doesn't match “Fußball”, so pattern matching with LIKE would result in FALSE.

A semiotic aside

There is no universally accepted authority for correct German. For example, German speaking Swiss did away with the ß in the twentieth century. On the other hand, the “Rat für deutsche Rechtschreibung” (Committee for German Spelling) introduced an upper case letter ẞ in 2017, but this attempt to simplify the problem has been widely ignored by the German speaking world. Simplifying German, what an outrageous idea!

The end result is even more confusing than before:

A solution for case-insensitive pattern matching

We need a practical solution for the problem of case-insensitive pattern matching. (PostgreSQL v18 solves the fundamental problem, but there is no index support to make queries fast.) Of course we could use lower() or cast to citext, but that would re-introduce the performance problems for long strings. So we evade the problem by explicitly using a different collation. For performance reasons, and to avoid the brain-twist of German soccer, we cast to the binary collation, which compares words character for character:

Note that for this to work as expected, we have to use the case insensitive versions of LIKE and the regular expression matching operator ~.

Of course, this solution is not as simple as we would wish: again, the application has to write queries that explicitly specify case insensitive processing. Another difficulty is performance: while case sensitive pattern matching can be supported with B-tree indexes, case-insensitive pattern matching requires a trigram index:

Such an index can speed up both of the above statements, but it can grow large and is slower to update than a normal B-tree index.

A performance test for case-insensitive comparisons

For this test, I used tables with ten million rows and a column with a random string of 320 characters. There was no index on the column. In the table perf_citext, the column is defined as citext. In perf_lower, the data type is text, and comparison is made using lower(). Table perf_coll uses text with the collation english_ci as defined above. The database used was PostgreSQL v15 with US English collations. All tables were cached in shared buffers.

Performance comparison of case-insensitive search techniques

	WHERE ... = 'abcd'	WHERE ... LIKE 'abcd%'	WHERE ... COLLATE "C" ILIKE 'abcd%'	WHERE ... COLLATE "C" ~* 'abcd%' (~ for lower())
citext	540 ms	536 ms	1675 ms	2500 ms
lower()	9000 ms	9000 ms	3000 ms	3800 ms
english_ci	830 ms	ERROR	2000 ms	1940 ms

In this test, citext comes out ahead, but case-insensitive collations are a decent runner-up. Using lower() does not perform well.

Conclusion

Pattern matching is not implemented for case-insensitive collations in PostgreSQL, and it is a difficult question what the correct behavior would be in this case. We have a decent workaround by explicitly using case-insensitive pattern matching operators and the binary collation, but the situation is definitely far from perfect.

• For an update on ICU collations, see this blog: ICU Collations Against PostgreSQL Data Corruption
• If you are interested in further blogs on PostgreSQL topics, check out my blog on how to cancel long-running queries.

---

In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Twitter, Facebook, or LinkedIn.