Re: BUG #16020: ICU Collations querys

On 2019-09-26 17:14, Marina Garrido Sanchez wrote:
> create collation ca_insensitive (provider = icu, locale =
> 'es-ES-u-ks-level1', deterministic = false;
>
> create table users (
> nombre text collate "ca_insensitive" primary key unique,
> apellidos text collate "ca_insensitive",
> direccion text
> );
>
> insert into user values ("jávier", "gonzález", "BBB")
> insert into user values ("Javier", "Gonzalez", "BBB")
>
> and if I do the query:
>
> select * from users where users.apellidos ilike '%Gonz%';
>
> I get the error that *ilike *does not support nondeterministic
> operation, for that, my question is How can I do partial match search
> with case- and accet- insensitive in postgres? or Which operator can I
> use to do the search?

You can run your query with an explicit deterministic collation applied,
for example:

select * from users where users.apellidos ilike '%Gonz%' collate "C";

or "und-x-icu" or whatever.

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services