Re: BUG #16020: ICU Collations querys - Mailing list pgsql-bugs

Hi, 

Sorry I dont see other way to send my issue or question, and I assumed that as It is something new (ICU Collations) for case and accet insensitive it would be better to do in this forum.

The questions is...that if I do the following script:

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?

Thanks in advance.

El mié., 25 sept. 2019 a las 22:09, Peter Eisentraut (<peter.eisentraut@2ndquadrant.com>) escribió:

On 2019-09-25 08:51, PG Bug reporting form wrote:
> The following bug has been logged on the website:
>
> Bug reference:      16020
> Logged by:          Marina Garrido Sanchez
> Email address:      garridosanchezmarina@gmail.com
> PostgreSQL version: 12beta4
> Operating system:   Windows
> Description:       
>
> When I use a particular ICU Collation query with locale =
> 'es-ES-u-ks-level1' and deterministic = false for having case- and accet-
> insensitive, I can use similiar, like and ilike as the documentation said,
> but how can I do a parcial search without this operators?

This doesn't sound like a bug, so it's inappropriate for this forum.

Also, please provide some more detail, including exact queries and what
you are hoping for as a result.

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