ICU also provides regexp matching, but not collation-aware, sinceĬharacter-based patterns don’t play well with the concept of collation.Ībout a potential collation-aware LIKE, it looks hard to implement, Compare this quote from the mailing list: Either solution would be difficult to implement in PostgreSQL, so we have given up on that issue for the time being. The ICU library goes with the second solution for simplicity. So you could just as well argue that the result should be FALSE. SELECT lower('FUSSBALL' COLLATE 'de-DE-x-icu') You could argue that it should be TRUE, because that’s what you’d get for upper('Fußball') LIKE 'FUS%'.

Now what would be the correct result for the following query in a case-insensitive collation? SELECT upper('Fußball' COLLATE 'de-DE-x-icu') 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: A good example is the German letter “ß”, which traditionally doesn’t have an upper-case equivalent. The ICU documentation details why correct case-insensitive pattern matching is difficult. Why is that not supported? The difficult case of German soccer SELECT id, long FROM perf_coll WHERE long ~ '^abcd' ĮRROR: nondeterministic collations are not supported for regular expressions SELECT id, long FROM perf_coll WHERE long LIKE 'abcd%' ĮRROR: nondeterministic collations are not supported for LIKE Here are two examples that exhibit the problem: The first problem is merely an annoyance, but the second problem will require more thought. You cannot use case-insensitive collations with pattern matching.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.There are two shortcomings with this approach: The trouble with pattern matching and case-insensitive collationsĬase-insensitive collations are the most elegant solution for the problem. If you are using PostgreSQL v12 or better, and PostgreSQL was configured -with-icu, you can define a case-insensitive collation like this:įor more details about ICU collations, read my article on that topic. regular expression matching is not case insensitive, and you have to use the case insensitive operator ~* explicitly.performance for longer values can also be bad, because citext internally calls lower(col COLLATE 'default') before comparing the values.there is no data type civarchar, so you can only implement that with a check constraint.That is simple and convenient, but has some disadvantages as well: The extension citext provides a data type citext, which stands for “case-insensitive text”. 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.the solution is implemented at the application level, that is, you have to custom-tailor the query for case-insensitive search.This can be made fast with a B-tree index on lower(col), but has two disadvantages: WHERE lower(col) = lower('search string') There are three known solutions to case-insensitive search in PostgreSQL: Explicit conversion with lower() or upper()Ī query that uses this method would look as follows: So let’s have a closer look at the problem and at possible solutions. This works like a charm, except if you want to perform pattern matching. There are several solutions to the problem, one of which is to use case-insensitive ICU collations.

Case insensitive citext collation icu like pattern matching performance postgresql trigramĬase-insensitive search is a much-requested feature, partly (I suspect) to maintain compatibility with Microsoft SQL Server.

Postgresql similar