Queries de caracteres acentuados en Postgres

[Mario Benitez]

Que tal estimadísimos,

Mi duda con Postgres 8.4, es cómo diablos puedo ejecutar un select, donde un campo (p.e: nombre) conincida con 'maría' y me arroje los resultados:

MARIA
MARÍA
maria
maría

Alguien ya resolvió esto en postgres?

Saludos y gracias de antemano.

[Mario Benitez]

 Perdón por la imprecisión, más bien sería realizar un select con la cadena 'maria', para obtener los resultados:

MARIA
MARÍA
maria
maría

Escuché de la implementación de una función que sustituya los cartacteres acentuados por sus equivalentes sin acentos, lo cual no es una opción debido al retardo que implica en la búsqueda, ya que las tablas llegan a tener hasta 1.5 millones de registros con al rededor de 200 campos.

En mi postgres, el valor de 'lc_callate' es 'es_MX.UTF-8'. Alguna pista?

Saludos.

---

From: mariob...@hotmail.com
To: ayuda...@googlegroups.com
Subject: Queries de caracteres acentuados en Postgres
Date: Mon, 4 Apr 2011 15:59:44 +0000

[Paynalton]

en teoría, puedes usar like.

select * from tabla where nombre like maria

con eso le devuelve resultados ignorando acentos y mayúsculas. Para mayor rango puedes usar:

select * from tabla where nombre like %maria%

eso te devolverá además a "ana maria", "jose maría" "mariano".. etc.

Si un ave no rompe su huevo morirá antes de nacer.
Nosotros somos el ave y el mundo es nuestro huevo.
POR LA REVOLUCIÓN DEL MUNDO!!!!

Ciudad de México

--
Has recibido este mensaje porque estás suscrito a Grupo "ayuda-linux"
de Grupos de Google.
Si quieres publicar en este grupo, envía un mensaje de correo
electrónico a ayuda...@googlegroups.com
Para anular la suscripción a este grupo, envía un mensaje a
ayuda-linux...@googlegroups.com
Para obtener más opciones, visita este grupo en
http://groups.google.es/group/ayuda-linux?hl=es. o http://www.compunauta.com/ayuda/

[Ixcoatl Perez]

Los acentos son han sido y seran una lata siempre, la unica verdad es
que los gringos inventaron las computadoras y cualquier cosa mas alla
del caracter 127 da problemas.

Llevo usando postgres mas de 10 anios y mi opcion ha sido siempre
evitar acentos, enies y demas mounstrosidades.

Aun asi, puedes intentar varias cosas:

http://www.flexiguided.de/publications.pgcollkey.en.html
http://www.blackbagops.net/tag/postgresql/

Si alguna te funciona avisanos para sabes a que atenernos. Hace mucho
que no lo intento de nuevo, espero que ya haya algun soporte nativo
para resolver este asunto.

Saludos!

El día 4 de abril de 2011 11:08, Mario Benitez
<mariob...@hotmail.com> escribió:

[Mario Benitez]

En postgres, las siguientes búsquedas no pueden ignorar acentos ni mayúscuales. Salvo el caso de las mayúsculas para la tercea consulta.

select * from usuarios where username = 'maria';
select * from usuarios wehre username like 'maria';
select * from usuarios wehre username ilike 'maria';        #PEEEEERO no utiliza índices

Ninguna podrán obtener los resultados

Maria
María
MARIA
MARÍA
MaRiA

Sigo investigando, gracias de antemano.

---

From: cxesc...@gmail.com
Date: Mon, 4 Apr 2011 12:00:06 -0500
Subject: Re: [AYUDA] RE: Queries de caracteres acentuados en Postgres
To: ayuda...@googlegroups.com

[Gustavo Guillermo Perez]

El Lunes 04 Abril 2011, escribió:
> Que tal estimadísimos,
>
> Mi duda con Postgres 8.4, es cómo diablos puedo ejecutar un select, donde
> un campo (p.e: nombre) conincida con 'maría' y me arroje los resultados:
>
> MARIA
> MARÍA
> maria
> maría

tal vez con SIMILAR TO.

> Alguien ya resolvió esto en postgres?

Te recomiendo que si tu proyecto es nuevo lo analices, alomejor no te conviene
utilizar postgresql sino MySQL, no estoy a favor de uno o del otro, sino que
mysql en tu caso encuentra con 'maria' todos tus casos como los listaste.

Saludos.

> Saludos y gracias de antemano.

--
Gustavo Guillermo Perez
http://www.compunauta.com
http://www.compunauta.net
http://anuncios.compunauta.net

[Mario Benitez]

Que tal Gustavo,

Nuestro problema es que usamos realmente muchos constraints en las tablas, y MySQL simplemente se arrana cual vaca en medio de la carretera con estas características.

Por ejemplo, acabo de implementar la función:

CREATE OR REPLACE FUNCTION sp_ascii(character varying)
RETURNS text AS
$BODY$
SELECT TRANSLATE
($1,
'áàâãäéèêëíìïóòôõöúùûüÁÀÂÃÄÉÈÊËÍÌÏÓÒÔÕÖÚÙÛÜçÇ',
'aaaaaeeeeiiiooooouuuuAAAAAEEEEIIIOOOOOUUUUcC');
$BODY$
LANGUAGE 'sql' IMMUTABLE;

La cual sustituye todo caracter acentuado, tanto en la cadena de búsquda como en el campo en el cual se realiza la misma. Posteriormente, creando el índice (que termina en más de 10 minutos porque la tabla tiene casi 12 millones de registros):

CREATE INDEX ksp_ascii_lastname ON clients (lower(sp_ascii(lastname)));

Para aventarle el query:

SELECT * FROM clients WHERE lower(sp_ascii(lastname)) LIKE lower(sp_ascii('pérez'));

Que me responde (en casi dos minutos):

'47168 rows found'

.... Esto no suena a una respuesta profesional aceptable. Pero tengo la esperanza de poder hacer un tuning a Postgres.

Algun tip al respecto?. Saludos.-

[Gustavo Guillermo Perez]

Claro que sí, es opensource, puedes modificar el código fuente en C para que
te transforme las comparaciones, por una función tuya que sustituya la de
simplemente ==, eso te daría muchísima velocidad y podrías ponerlo como una
opción en postgresql a la hora de construirlo y hacer público el parche.!!!
esa es la idea de que postgresql y mysql son opensource, que puedes
modificarlos.!!!!

Respecto a los constraints, MySQL tiene varios drivers internos de
almacenamiento y no todos lo soportan es cierto eso no quiere decir que no
tenga soporte, al crear las tablas uno elije que tipo de storage, sabiendo
pro/cons puedes elegir entre velocidad/journaling/constraints etc.

para hacer esto te recomendaría buscar una biblioteca común de utf8 para que
conviertas las cadenas a utf8, luego las pases a minúsculas a ambas y por
último antes de hacer la comparación, reemplazar íÍïÏì por i antes de ejecutar
==.
También deberías modificar las funciones que actualizan y modifican el índice
para indexar las versiones minúsculas y con los caracteres convertidos, siendo
que no vas a alterar el registro original, solo la versión del char con el que
indexas y comparas.

Saludos.

[Ixcoatl Perez]

Esta opcion:

CREATE OR REPLACE FUNCTION asciify(unicode text) RETURNS text AS $$
DECLARE
translated text;
BEGIN
translated := '';
FOR i in 1..(char_length(unicode)) LOOP
translated := translated || chr(ascii(substring(unicode,i,1))%128);
END LOOP;
RETURN translated;
END;
$$ LANGUAGE plpgsql;

No es mala porque en realidad el codigo no se interpreta cada vez que
se hace la comparacion. Cuando creas la funcion se compila y se guarda
en codigo objeto (o posiblemente maquina), haciendo que su ejecucion
sea tan rapida como las funciones nativas.

No la haz probado?

Saludos!

El día 4 de abril de 2011 16:42, Gustavo Guillermo Perez
<gus...@compunauta.com> escribió:

[Gustavo Guillermo Perez]

El Lunes 04 Abril 2011, Ixcoatl Perez escribió:
> Esta opcion:
>
> CREATE OR REPLACE FUNCTION asciify(unicode text) RETURNS text AS $$
> DECLARE
> translated text;
> BEGIN
> translated := '';
> FOR i in 1..(char_length(unicode)) LOOP
> translated := translated || chr(ascii(substring(unicode,i,1))%128);
> END LOOP;
> RETURN translated;
> END;
> $$ LANGUAGE plpgsql;
>
> No es mala porque en realidad el codigo no se interpreta cada vez que
> se hace la comparacion. Cuando creas la funcion se compila y se guarda
> en codigo objeto (o posiblemente maquina), haciendo que su ejecucion
> sea tan rapida como las funciones nativas.

Si se ejecuta por cada comparación, ya que si tienes almacenado algo con
acento en una tabla y usan o no usan acentos en tu búsqueda estás forzado a
pasar la función por cada registro para sustituir los caracteres especiales
antes de comparar.

http://www.sendadevida.org
http://anuncios.compunauta.net

[Ixcoatl Perez]

Si, asi es.

Lo que queria decir es la velocidad de ejecucion por cada registro es
comprable a la de un 'like' , por decir algo. Ambos tienen que hacer
sustituciones internas de un buffer en la memoria para cada llamada a
la funcion.

Asi las cosas tu programa puede ser que no se vea afectado grandemente
por esta situacion.

De cualquier manera, si estas hablando de sacar millones de registros
de una tabla, la transposicion de unos cuantos bytes sera el menor de
tus cuellos de botella.

Saludos

El día 4 de abril de 2011 16:50, Gustavo Guillermo Perez

[Gustavo Guillermo Perez]

El Lunes 04 Abril 2011, Ixcoatl Perez escribió:
> Si, asi es.
>
> Lo que queria decir es la velocidad de ejecucion por cada registro es
> comprable a la de un 'like' , por decir algo. Ambos tienen que hacer
> sustituciones internas de un buffer en la memoria para cada llamada a
> la funcion.
>
> Asi las cosas tu programa puede ser que no se vea afectado grandemente
> por esta situacion.
>
> De cualquier manera, si estas hablando de sacar millones de registros
> de una tabla, la transposicion de unos cuantos bytes sera el menor de
> tus cuellos de botella.

Por ello insisto que si modificas la función de postgresql que hace el índice
y lo haga con los valores reemplazados mientras se insertan, y que la
comparación reemplace en el núcleo de postgresql los valores de búsqueda y por
registro no tendrías que llamar a una función que te aseguro que por ser un
procedimiento embebido debe hacer más de 3 function calls por registro.

:p

[Paynalton]

oye, y no podrás usar acaso un regexp para refinar tus búsquedas??

http://www.postgresql.org/docs/8.3/static/functions-matching.html

segun esto puedes usar la función similar to:

select * from tabla where nombre similar to maria


que además soporta expresiones regulares


si así solo no te funciona puedes por ejemplo a tu cadena de búsqueda reemplazarle cada caracter por una regla como a="[aAáÁäÁ]" m="[mM]" y así antes de integrarla a tu query, ademásde convertirla a la codificación de carácteres que estes manejando en tus tablas.

Si un ave no rompe su huevo morirá antes de nacer.
Nosotros somos el ave y el mundo es nuestro huevo.
POR LA REVOLUCIÓN DEL MUNDO!!!!

Ciudad de México

[Mario Benitez]

Pudiera ser, pero por ejemplo

Si la cadena de búsqueda es 'maría', y en la cual se sustituye 'í' por 'i', si tendremos de regreso los resultados:

maría
MARÍA
María
maria

... peeeero, si la cadena de búsqueda es 'maria', no habrá nada sustituíble, por lo que los resultados de la búsqueda serán:

maria

Ahora estoy probando habilitar TSearch2 en postgres. Les cuento a ver que tal.

Saludos.

---

From: cxesc...@gmail.com
Date: Mon, 4 Apr 2011 18:29:08 -0500

Subject: Re: [AYUDA] Queries de caracteres acentuados en Postgres

To: ayuda...@googlegroups.com

[Paynalton]

mmm, pero con regexp, la busqueda maria quedaría convertida a:

select * from tabla where nombre similar to [mM][aAáÁäÄ][rR][iIíÍïÏ][aAáÁäÄ]

lo cual haría match con:

María

maria

Marïa

mARIA

incluso podrías poner:

[mM][aAáÁäÄ4][rR][iIíÍïÏ1][aAáÁäÄ4]

y coinsidiría con:

M4r14

Por eso me encantan las expresiones regulares jejjeje.

Si un ave no rompe su huevo morirá antes de nacer.
Nosotros somos el ave y el mundo es nuestro huevo.
POR LA REVOLUCIÓN DEL MUNDO!!!!

Ciudad de México

[Ixcoatl Perez]

La opcion de Paynalton es la que mas me gusta.

Es extremadamente facil de implementar del lado del cliente o servidor
y en cualquier lenguaje.
No necesitas mas que las vocales si usas un like y por supuesto, los
numeros son solo un lujo (A = 4).

Lo malo es que las expresiones regulares cuestan CPU, y son caras ! :D
Pero la trivialidad de la solucion lo justifica.

Saludos

[chacait]

Mario, no conozco el postgres, pero si es parecido al sql, por lo que
estoy viendo, fijate con la siguiente consulta:
Select * from tabla
Where Nombre like lower 'maria'
Abrazo

[Mario Benitez]

Si, digamos que las expresiones regulares funcionan bien, pero por ejemplo, una prueba con el ejemplo de regexp tarda en responder 78475ms. Que es más de un minutito.

De que lo encuentra lo encuentra, al igual que con la función sp_ascii, pero sigue siendo mortalmente lento.

Estoy implementando TSearch2 (full text searching), se alteró la tabla anexando una columna tipo tsvector, y después a esta se le insertaron los valores del campo de búsqueda. El proceso lleva dos horas corriendo (anexando los valores al nuevo campo y reindexando), a ver en un rato a ver que sucede.

Saludos.

---

From: cxesc...@gmail.com
Date: Mon, 4 Apr 2011 22:14:46 -0500

[Mario Benitez]

Que tal,

Esta consulta no me regresaría las conincidencias acentuadas. :(

Sigo luchando con TSearch2.

Saludos.



> Date: Mon, 4 Apr 2011 20:01:15 -0700
> Subject: [AYUDA] Re: Queries de caracteres acentuados en Postgres
> From: jgvi...@gmail.com
> To: ayuda...@googlegroups.com

[Gustavo Guillermo Perez]

El Martes 05 Abril 2011, Mario Benitez escribió:
> Que tal,
>
> Esta consulta no me regresaría las conincidencias acentuadas. :(
>
> Sigo luchando con TSearch2.

Esto se ve interesante, parece que ya lo intentaron por esas fechas proponer,
en que habrá quedado?

http://archives.postgresql.org/pgsql-hackers/2008-05/msg00923.php

Saludos.

> Saludos.
>
> > Date: Mon, 4 Apr 2011 20:01:15 -0700
> > Subject: [AYUDA] Re: Queries de caracteres acentuados en Postgres
> > From: jgvi...@gmail.com
> > To: ayuda...@googlegroups.com
> >
> > Mario, no conozco el postgres, pero si es parecido al sql, por lo que
> > estoy viendo, fijate con la siguiente consulta:
> > Select * from tabla
> > Where Nombre like lower 'maria'
> > Abrazo
> >
> > On 4 abr, 12:59, Mario Benitez <mariobeni...@hotmail.com> wrote:
> > > Que tal estimadísimos,
> > >
> > > Mi duda con Postgres 8.4, es cómo diablos puedo ejecutar un select,
> > > donde un campo (p.e: nombre) conincida con 'maría' y me arroje los
> > > resultados:
> > >
> > > MARIA
> > > MARÍA
> > > maria
> > > maría
> > >
> > > Alguien ya resolvió esto en postgres?
> > >
> > > Saludos y gracias de antemano.

--

[Gustavo Guillermo Perez]

El Martes 05 Abril 2011, Mario Benitez escribió:

> Que tal,
>
> Esta consulta no me regresaría las conincidencias acentuadas. :(
>
> Sigo luchando con TSearch2.

Esa propuesta está en la lista de POR HACER:
http://wiki.postgresql.org/wiki/Todo:Collate#Possible_Problems
Saludos denuevo

> Saludos.
>
> > Date: Mon, 4 Apr 2011 20:01:15 -0700
> > Subject: [AYUDA] Re: Queries de caracteres acentuados en Postgres
> > From: jgvi...@gmail.com
> > To: ayuda...@googlegroups.com
> >
> > Mario, no conozco el postgres, pero si es parecido al sql, por lo que
> > estoy viendo, fijate con la siguiente consulta:
> > Select * from tabla
> > Where Nombre like lower 'maria'
> > Abrazo
> >
> > On 4 abr, 12:59, Mario Benitez <mariobeni...@hotmail.com> wrote:
> > > Que tal estimadísimos,
> > >
> > > Mi duda con Postgres 8.4, es cómo diablos puedo ejecutar un select,
> > > donde un campo (p.e: nombre) conincida con 'maría' y me arroje los
> > > resultados:
> > >
> > > MARIA
> > > MARÍA
> > > maria
> > > maría
> > >
> > > Alguien ya resolvió esto en postgres?
> > >
> > > Saludos y gracias de antemano.

--

[Mario Benitez]

Sí, hasta la versión 9.1. El problema es que hay que migrar ya o seguir para siempre con la nueva versión con MSSQL.

Voy a recompilar con un parche del collate. Les cuento a ver que sucede.

Saludos y gracias, seguimos ....

[Mario Benitez]

El problema, en términos de costo está más o menos así;

- en una tabla normal con casi 12'000,000 de registros con 195 campos ...

query con regexp: 246698 milisegundos
query normal: 2379 milisegundos

Digamos que el punto es desde luego, encontrar las tuplas correspondientes, más sin embargo es factor desicivo encontrarlas en mucho menor tiempo.

Snowball, pg_ascii(lower('cadena')), tsearch2 ... se fueron con sus zapatillas de ballet.

Ya terminé con la compilación de ICU, que es una dependencia para pg_collkey en la recompilación de postgres. Les cuento a ver que tal.

Saludos.




> Date: Mon, 4 Apr 2011 22:24:00 -0500

> Subject: Re: [AYUDA] Queries de caracteres acentuados en Postgres

> From: ixcoatl.pe...@gmail.com
> To: ayuda...@googlegroups.com