PostgreSQL nondeterministic collations are not supported for LIKE

Question

I am using Postgresql v12. I created a collation like this:

CREATE COLLATION ci (provider = icu, locale = 'tr_TR', deterministic = false);

I used that collation in a table:

create table testtable1 (
 id serial primary key,
 name text COLLATE "ci"
);

And I inserted sample data:

insert into testtable1 values(3,'abc');

When I query that table using LIKE, it returns the following error:

select name from testtable1 WHERE name LIKE '%a%' 

ERROR: nondeterministic collations are not supported for LIKE
SQL state: 0A000

But I need to use LIKE. Is there any way to allow this?

• pifor: postgresql.org/docs/12/collation.html says "While nondeterministic collations give a more "correct" behavior, especially when considering the full power of Unicode and its many special cases, they also have some drawbacks. Foremost, their use leads to a performance penalty. Also, certain operations are not possible with nondeterministic collations, such as pattern matching operations. Therefore, they should be used only in cases where they are specifically wanted.": I understand that to use LIKE you need a deterministic collation.

Answer 1

Assuming that most of the time the custom, non-deterministic collation works for you and you only need something deterministic occasionally, then you can simply provide the deterministic collation when needed via the COLLATE clause. For example, in your case, you can use the tr-TR-x-icu collation as follows:

select name from testtable1 WHERE name LIKE '%a%' COLLATE "tr-TR-x-icu"

You can see a working demo of this, using your sample code posted in the question, on db<>fiddle.

Of course, that will be case-sensitive. For this you need to use the ILIKE operator which is case-insensitive. For example:

select name from testtable1 WHERE name ILIKE '%A%' COLLATE "tr-TR-x-icu"

But, the = operator is still case-sensitive because the collation as defined in the CREATE COLLATION statement did not alter the default sensitivities, and by default collations are everything-sensitive (which is not the same as binary collations, just FYI).

To create a collation that uses the "tr-TR" locale and is also case-insensitive, you need to specify the locale property in the CREATE COLLATION statement as follows:

locale = 'tr-TR-u-ks-level2'

To see that in action, please see: db<>fiddle example 2

Please see "Unicode Technical Standard #35: UNICODE LOCALE DATA MARKUP LANGUAGE (LDML), PART 5: COLLATION" for the full list of collation options.

And, please visit Collations Info for collation-related info. It's geared mostly towards SQL Server and .NET, but has some MySQL and PostgreSQL stuff, and the key concepts are generally the same across platforms and languages.

• Banu Akkus: I tried your code but error message is the same.
• Solomon Rutzky: @BanuAkkus Did you look at the working demo on db<>fiddle? What exactly did you try?
• Banu Akkus: I looked your demo on db<>fiddle. I changed my query as "select name from testtable1 WHERE name LIKE '%a%' COLLATE "tr-TR-x-icu" Error message is the same.
• Solomon Rutzky: @BanuAkkus Is that the only query running? Is that the entire query or is there another LIKE clause? The COLLATE clause is per each predicate / condition.
• Banu Akkus: Yes, there is only one query. I am testing on test environment. If I solve "LIKE" issue, I will use on production.