6

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?

Solomon Rutzky
  • 65,256
  • 6
  • 135
  • 267
Banu Akkus
  • 199
  • 2
  • 9
  • https://www.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. – pifor Apr 29 '20 at 15:23

1 Answers1

6

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.

Solomon Rutzky
  • 65,256
  • 6
  • 135
  • 267
  • I tried your code but error message is the same. – Banu Akkus Apr 30 '20 at 09:50
  • @BanuAkkus Did you look at the working demo on db<>fiddle? What _exactly_ did you try? – Solomon Rutzky Apr 30 '20 at 15:13
  • 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. – Banu Akkus Apr 30 '20 at 17:46
  • @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. – Solomon Rutzky Apr 30 '20 at 18:12
  • Yes, there is only one query. I am testing on test environment. If I solve "LIKE" issue, I will use on production. – Banu Akkus Apr 30 '20 at 18:26
  • @BanuAkkus What does the following query return: `select collisdeterministic from pg_collation where collname = 'tr-TR-x-icu'` ? – Solomon Rutzky Apr 30 '20 at 19:51
  • It returns "true" – Banu Akkus May 01 '20 at 20:57
  • Is there anything else should I do? @solomon-rutzky – Banu Akkus May 04 '20 at 19:15
  • @BanuAkkus I'm not sure. I don't have any other PostgreSQL v12 to play with other than what's on db<>fiddle. I'm not a PostgreSQL expert. I know quite a bit about collations, but I mostly work with SQL Server so I'm not as familiar with the nuances of other RDBMSs. All I know is that I reproduced the error on db<>fiddle using your example code, then did what I thought might work and it appeared to work. If it doesn't work on your system, perhaps your column is using a different encoding, or maybe there is a different setting at the DB, server, or connection levels? – Solomon Rutzky May 05 '20 at 06:20
  • I tested on Amazon RDS postgresql instance v12 it worked fine. But it does not compare properly. select name from testtable1 WHERE name LIKE '%a%' COLLATE "tr-TR-x-icu" works fine and return result but LIKE '%A%' returns nothing. – Banu Akkus May 05 '20 at 14:41
  • please check on https://dbfiddle.uk/?rdbms=postgres_12&fiddle=9b382be48e5c62692f352e550290555c – Banu Akkus May 05 '20 at 15:40
  • @BanuAkkus For that, at minimum you could simply use `ILIKE` for case-insensitivity (example here: https://dbfiddle.uk/?rdbms=postgres_12&fiddle=63d489f9406b3c5ab7b831596959626b). But I am looking to see if there is another way that would also work with the `=` operator without needing to use `COLLATE` there as well. – Solomon Rutzky May 05 '20 at 15:52
  • "=" operator works fine but we need use "like" queries sometimes. Thank you for your support. – Banu Akkus May 05 '20 at 16:08
  • @BanuAkkus But in the current configuration, the `=` operator is _not_ case-*in*sensitive. Isn't that what you are looking for? Meaning, the following example shows that `WHERE name = 'aBc'` does not return any rows. Should it? https://dbfiddle.uk/?rdbms=postgres_12&fiddle=72ef317adfb60b019c7682b632fb2617 – Solomon Rutzky May 05 '20 at 16:10
  • of course it should return something. I got your point now. What should I do? – Banu Akkus May 05 '20 at 16:19
  • @BanuAkkus I figured out how to create the case-insensitive collation. I have updated my answer which includes a link to a db<>fiddle example showing it working and includes explanations. – Solomon Rutzky May 05 '20 at 16:50
  • Awesome, thanks ! – Banu Akkus May 07 '20 at 10:37