martes, 8 de diciembre de 2009

Búsquedas por campos de texto en PostgreSQL

(Nota: Para las pruebas se utilizó PostgreSQL 8.3.x)

El problema

Supongamos que nos piden hacer una búsqueda flexible por nombre de cliente. La idea es que el usuario pueda ingresar como criterio de búsqueda un nombre o parte del nombre sin necesidad de considerar mayúsculas, minúsculas y letras acentuadas.

Alternativas de solución
Lo más sencillo, aunque incorrecto, sería hacer una simple evaluación de igualdad con el operador “=”
SELECT * FROM clientes WHERE nombre = ‘MARTIN’


Esta estrategia es básica y tiene muchas limitaciones. En principio, no busca por parte del nombre y no encontraría un cliente que se llame “SAN MARTIN”.
PostgreSQL provee algunas funciones de comparación de cadenas de texto más poderosas que son útiles para estos casos. Veamos las más comunes y utilizadas.

LIKE
Es un estándar para comparación de cadenas de texto en SQL. Permite comparar una cadena de texto con un determinado patrón. El formato más utilizado para una comparación flexible utiliza % para representar cero o más caracteres:

Algunos ejemplos:
‘martin’ LIKE ‘martin’ > Verdadero (igual a operador =)
‘martin’ LIKE ‘%rt%’ > Verdadero (%...% permite detectar coincidencia parcial de textos)
‘san martin’ LIKE ‘%martin%’ > Verdadero
‘San Martin’ LIKE ‘%martin%’ > Falso
‘San Martín’ LIKE ‘%martin%’ > Falso

La limitación de LIKE para lo que queremos hacer es que resulta sensible a mayúsculas y minúsculas, lo que hace muy estricta la búsqueda.

ILIKE
ILIKE no es un estándar SQL sino una extensión de PostgreSQL. Se puede utilizar para hacer una búsqueda que no tome en cuenta mayúsculas y minúsculas (case insensitive) y por lo tanto es más flexible. Siguiendo con el ejemplo anterior:

‘San Martin’ ILIKE ‘%martin%’ > Verdadero
‘San Martín’ ILIKE ‘%martin%’ > Falso
‘San Martín’ ILIKE ‘%martín%’ > Verdadero

El problema es que ILIKE es insensible a mayúsculas y minúsculas pero no ignora caracteres acentuados y por eso la anteúltima comparación devuelve falso. O sea, ILIKE considera "e" igual a "E", pero no considera "é" igual a "e". No realiza una equiparación entre letras acentuadas y no acentuadas.

TO_ASCII
Para resolver este problema podemos utilizar, en ciertas circunstancias, la función TO_ASCII, que nos permite convertir las letras acentuadas antes de realizar la comparación. TO_ASCII convierte todas las vocales acentuadas en su equivalente no acentuado. Ejemplo: TO_ASCII(‘San Martín’) devuelve "San Martin". Con ello las letras acentuadas dejan de generar inconvenientes en las comparaciones.

Ejemplo de uso:
TO_ASCII(‘San Martín’) ILIKE TO_ASCII(‘%martin%’) > Verdadero

Si volvemos al ejemplo del principio, con el siguiente SELECT:
SELECT * FROM clientes WHERE TO_ASCII(nombre) ILIKE TO_ASCII(‘%martin%’)

Vamos a recuperar clientes de nombre:
MARTIN, SAN MARTIN, San Martin, Martín, San Martín, Etc.

Que es lo que estábamos buscando. Ahora bien, esta práctica función solo soporta la conversión a ASCII desde encodings LATIN1, LATIN2, LATIN9, y WIN1250. Hasta hace un tiempo esto no era un problema porque el encoding más habitual de las bases de datos era LATIN1. El problema se presenta cuando la base de datos está codificada con UTF8, algo cada vez más habitual, porque TO_ASCII falla y no nos permite realizar búsquedas que ignoren los acentos. La cuestión es: ¿Existe algo semejante a TO_ASCII con bases de datos UTF8?

Comparación que ignore acentos con encoding UTF8
El tema no parece sencillo desde el punto de vista técnico. En principio y por lo que se puede observar en los foros técnicos no está previsto que PostgreSQL incorpore en el corto plazo alguna función nativa que resuelva el problema (algunos mencionan un nuevo módulo contrib que va a permitir remover los acentos a partir de la versión 8.5, pero es todo extraoficial).

Existen muchas propuestas en los foros, en algunos casos pequeñas variaciones sobre la misma idea. La mejor solución que encontré para resolver el problema es crear una función que reemplace caracteres acentuados por sus equivalentes no acentuados y usarla en la comparación en lugar de TO_ASCII.

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

'aaaaaeeeeiiiooooouuuuAAAAAEEEEIIIOOOOOUUUUcC');
$BODY$
LANGUAGE 'sql';

Y se usa del siguiente modo:
SELECT * FROM clientes WHERE SP_ASCII(nombre) ILIKE SP_ASCII(‘%martin%’)

Esto ha funcionado correctamente con bases de datos encoding UTF8 recuperando:
MARTIN, SAN MARTIN, San Martin, Martín, San Martín, Etc.

Una nota sobre rendimiento. Con la función SP_ASCII la recuperación de información se vuelve un poco más lenta. No es algo muy notable pero puede incidir si la base de datos es muy grande. Para hacer más rápida la búsqueda se puede limitar la conversión de caracteres a los que resultan relevantes en cada caso. Por ejemplo, en la base de datos usada para pruebas solo se encuentran algunas letras acentuadas por lo que fueron eliminados el resto de los casos de la función de conversión que quedó reducida a:

SELECT TRANSLATE($1, 'áéíóúÁÉÍÓÚçÇ','aeiouAEIOUcC');

Con este sencillo expediente la búsqueda se realiza en la mitad del tiempo. Se debería probar en cada caso y hacer las evaluaciones correspondientes. Como alternativa, en los foros algunos usuarios recomiendan crear un índice funcional para mejorar la performance.

Referencias

Más información sobre LIKE, ILIKE y otras formas de comparación de cadenas (SIMILAR TO, POSIX) en:
http://www.postgresql.org/docs/8.3/interactive/functions-matching.html

Información sobre TO_ASCII y sus limitaciones:
http://www.postgresql.org/docs/current/interactive/functions-string.html

Discusión en la comunidad de PostgreSQL sobre el tema, en potugués (“problema com acentos no UTF8”):
http://www.mail-archive.com/pgbr-geral@listas.postgresql.org.br/msg15630.html

Versiones de la “función de conversión” para UTF8:
http://www.mail-archive.com/pgbr-geral@listas.postgresql.org.br/msg15632.html
http://wiki.postgresql.org/wiki/Strip_accents_from_strings,_and_output_in_lowercase

Función de conversión en perl:
http://www.mail-archive.com/pgsql-general@postgresql.org/msg128804.html

Blog en francés que trata la misma problemática:
http://code18.blogspot.com/2009/03/comparaison-de-texte-postgresql.html

Artículo muy completo sobre UNICODE:
http://www.joelonsoftware.com/articles/Unicode.html

18 comentarios:

  1. Muy buen Post, el tema se explica muy claro, Saludos !!

    ResponderEliminar
  2. Felicidades!

    Una exposición brillante.

    Saludos!

    ResponderEliminar
  3. muy bueno eh felicitaciones

    ResponderEliminar
  4. me ha sido de gran ayuda, gracias por el esfuerzo!!

    ResponderEliminar
  5. excelente aporte. gracias

    ResponderEliminar
  6. En 9.0 te puedes ahorrar todo esto usando el módulo contrib "unaccent"

    ResponderEliminar
  7. gracias maestro, ha quedado muy claro!!!

    ResponderEliminar
  8. Excelente!!
    Muchas Gracias :D

    ResponderEliminar
  9. Muy bueno el post, muchisimas gracias sirvio un monton

    ResponderEliminar
  10. No se si copiaste esta informacion de otro blog o alguien te la copio a ti.... porque los veo identicos............

    ResponderEliminar
    Respuestas
    1. Hola Leonardo,
      Me habrán copiado, porque es un artículo que escribí hace unos años. Donde encontraste algo igual? Gracias por el comentario.
      Saludos!
      Román

      Eliminar
  11. Aquí otro que se beneficia de tu aportación. Muchas gracias.

    ResponderEliminar
  12. Muchas gracias por tu aporte... Justo lo que buscaba y muy bien explicado

    ResponderEliminar
  13. Excelente explicación!!!

    ResponderEliminar