¿PostgreSQL admite intercalaciones “insensibles al acento”?

Esta es la solución más completa que te podemos compartir, pero primero estúdiala detenidamente y valora si es compatible a tu trabajo.

Solución:

Utilizar el módulo poco acento para eso, que es completamente diferente de lo que está vinculando.

unaccent es un diccionario de búsqueda de texto que elimina los acentos (signos diacríticos) de los lexemas.

Instale una vez por base de datos con:

CREATE EXTENSION unaccent;

Si recibe un error como:

ERROR: could not open extension control file
"/usr/share/postgresql//extension/unaccent.control": No such file or directory

Instale el paquete contrib en su servidor de base de datos como se indica en esta respuesta relacionada:

• Error al crear una extensión inacentuada en PostgreSQL

Entre otras cosas, proporciona la función unaccent() puede usar con su ejemplo (donde LIKE parece no necesario).

SELECT *
FROM   users
WHERE  unaccent(name) = unaccent('João');

Índice

Para usar un índice para ese tipo de consulta, cree un índice en la expresión. Sin embargo, Postgres solo acepta IMMUTABLE funciones para índices. Si una función puede devolver un resultado diferente para la misma entrada, el índice podría romperse silenciosamente.

unaccent() solamente STABLE no IMMUTABLE

Desafortunadamente, unaccent() es solo STABLE, no IMMUTABLE. Según este hilo sobre pgsql-bugs, esto se debe a Tres razones:

1. Depende del comportamiento de un diccionario.
2. No hay una conexión cableada a este diccionario.
3. Por tanto, también depende de la corriente search_path, que puede cambiar fácilmente.

Algunos tutoriales en la web indican que simplemente modifique la volatilidad de la función para IMMUTABLE. Este método de fuerza bruta puede romperse en determinadas condiciones.

Otros sugieren un sencillo IMMUTABLE función de envoltura (como lo hice yo mismo en el pasado).

Hay un debate en curso sobre si hacer la variante con dos parámetros. IMMUTABLE que declara explícitamente el diccionario utilizado. Leer aquí o aquí.

Otra alternativa sería este módulo con un INMUTABLE unaccent() función de Musicbrainz, proporcionada en Github. No lo he probado yo mismo. Creo que se me ha ocurrido un mejor idea:

Lo mejor por ahora

Este enfoque es más eficiente que otras soluciones flotando alrededor, y más seguro.
Crear un IMMUTABLE Función contenedora SQL que ejecuta el formulario de dos parámetros con función y diccionario calificados por esquema cableado.

Dado que anidar una función no inmutable deshabilitaría la función en línea, basarla en una copia de la función C, (falsa) declarada IMMUTABLE así como. Su solamente El propósito es ser utilizado en el contenedor de funciones SQL. No está diseñado para usarse solo.

La sofisticación es necesaria ya que no hay forma de cablear el diccionario en la declaración de la función C. (Requeriría hackear el código C en sí). La función contenedora SQL hace eso y permite que ambas funciones se inserten y índices de expresión.

CREATE OR REPLACE FUNCTION public.immutable_unaccent(regdictionary, text)
  RETURNS text LANGUAGE c IMMUTABLE PARALLEL SAFE STRICT AS
'$libdir/unaccent', 'unaccent_dict';

CREATE OR REPLACE FUNCTION public.f_unaccent(text)
  RETURNS text LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT AS
$func$
SELECT public.immutable_unaccent(regdictionary 'public.unaccent', $1)
$func$;

Soltar PARALLEL SAFE de ambas funciones para Postgres 9.5 o anterior.

public siendo el esquema donde instaló la extensión (public es el predeterminado).

La declaración de tipo explícita (regdictionary) defiende contra ataques hipotéticos con variantes sobrecargadas de la función por parte de usuarios malintencionados.

_{Anteriormente, abogué por una función de envoltura basada en el STABLE función unaccent() enviado con el módulo de unccent. Esa función deshabilitada en línea. Esta versión se ejecuta diez veces más rápido que la simple función de contenedor que tenía aquí antes.
Y eso ya era dos veces más rápido que la primera versión que agregó SET search_path = public, pg_temp a la función, hasta que descubrí que el diccionario también puede ser calificado por esquema. Aún así (Postgres 12) no es demasiado obvio a partir de la documentación.}

Si carece de los privilegios necesarios para crear funciones C, ha vuelto a la segunda mejor implementación: una IMMUTABLE envoltura de función alrededor de la STABLEunaccent() función proporcionada por el módulo:

CREATE OR REPLACE FUNCTION public.f_unaccent(text)
  RETURNS text AS
$func$
SELECT public.unaccent('public.unaccent', $1)  -- schema-qualify function and dictionary
$func$  LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT;

Finalmente, el índice de expresión hacer consultas rápido:

CREATE INDEX users_unaccent_name_idx ON users(public.f_unaccent(name));

Recuerda recrear índices que involucre esta función después de cualquier cambio en la función o el diccionario, como una actualización de versión importante en el lugar que no recrearía índices. Todos los lanzamientos principales recientes tenían actualizaciones para el unaccent módulo.

Adapte las consultas para que coincidan con el índice (para que el planificador de consultas las utilice):

SELECT * FROM users
WHERE  f_unaccent(name) = f_unaccent('João');

No necesitas la función en la expresión correcta. Allí también puede suministrar cadenas sin acento como 'Joao' directamente.

La función más rápida no se traduce en consultas mucho más rápidas utilizando el índice de expresión. Eso opera con valores precalculados y ya es muy rápido. Pero el mantenimiento del índice y las consultas no utilizan el beneficio del índice.

La seguridad de los programas cliente se ha reforzado con Postgres 10.3 / 9.6.8, etc. necesitar para calificar el esquema de la función y el nombre del diccionario como se demuestra cuando se usa en cualquier índice. Ver:

• ‘No existe el diccionario de búsqueda de texto “no acento”‘ entradas en el registro de postgres, supuestamente durante el análisis automático

Ligaduras

En Postgres 9.5 o mayor ligaduras como ‘Œ’ o ‘ß’ deben expandirse manualmente (si lo necesita), ya que unaccent() siempre sustituye a soltero carta:

SELECT unaccent('Œ Æ œ æ ß');

unaccent
----------
E A e a S

Te encantará esta actualización para poco acento en Postgres 9,6:

Ampliar contrib/unaccentestándar de unaccent.rules archivo para manejar todos los diacríticos conocidos por Unicode, y expandir ligaduras correctamente (Thomas Munro, Léonard Benedetti)

El énfasis audaz es mío. Ahora obtenemos:

SELECT unaccent('Œ Æ œ æ ß');

unaccent
----------
OE AE oe ae ss

La coincidencia de patrones

Para LIKE o ILIKE con patrones arbitrarios, combine esto con el módulo pg_trgm en PostgreSQL 9.1 o posterior. Cree un trigrama GIN (normalmente preferible) o un índice de expresión GIST. Ejemplo de GIN:

CREATE INDEX users_unaccent_name_trgm_idx ON users
USING gin (f_unaccent(name) gin_trgm_ops);

Se puede utilizar para consultas como:

SELECT * FROM users
WHERE  f_unaccent(name) LIKE ('%' || f_unaccent('João') || '%');

Los índices GIN y GIST son más costosos de mantener que los btree simples:

• Diferencia entre el índice GiST y GIN

Hay soluciones más simples para patrones anclados a la izquierda. Más sobre la coincidencia de patrones y el rendimiento:

• Coincidencia de patrones con LIKE, SIMILAR TO o expresiones regulares en PostgreSQL

pg_trgm también proporciona operadores útiles para “similitud” (%) y “distancia” (<->).

Los índices de trigram también admiten expresiones regulares simples con ~ et al. y insensible a mayúsculas y minúsculas patrón a juego con ILIKE:

• Acento de PostgreSQL + búsqueda que no distingue entre mayúsculas y minúsculas

No, PostgreSQL no admite intercalaciones en ese sentido

PostgreSQL no admite intercalaciones como esa (insensible al acento o no) porque ninguna comparación puede devolver igual a menos que las cosas sean iguales en binario. Esto se debe a que internamente introduciría muchas complejidades para cosas como un índice hash. Por esta razón las colaciones en su sentido más estricto solo afecta el pedido y no igualdad.

Soluciones alternativas

Diccionario de búsqueda de texto completo que no incluye lexemas.

Para FTS, puede definir su propio diccionario usando unaccent,

CREATE EXTENSION unaccent;

CREATE TEXT SEARCH CONFIGURATION mydict ( COPY = simple );
ALTER TEXT SEARCH CONFIGURATION mydict
  ALTER MAPPING FOR hword, hword_part, word
  WITH unaccent, simple;

Que luego puede indexar con un índice funcional,

-- Just some sample data...
CREATE TABLE myTable ( myCol )
  AS VALUES ('fóó bar baz'),('qux quz');

-- No index required, but feel free to create one
CREATE INDEX ON myTable
  USING GIST (to_tsvector('mydict', myCol));

Ahora puede consultarlo de manera muy simple

SELECT *
FROM myTable
WHERE to_tsvector('mydict', myCol) @@ 'foo & bar'

    mycol    
-------------
 fóó bar baz
(1 row)

Ver también

• Creación de una búsqueda que no distingue entre mayúsculas y minúsculas y acento / diacríticos en un campo

Sin acento por sí mismo.

los unaccent El módulo también se puede usar solo sin integración FTS, para eso, consulte la respuesta de Erwin

Estoy bastante seguro de que PostgreSQL se basa en el sistema operativo subyacente para la intercalación. Eso lo hace admite la creación de nuevas intercalaciones y la personalización de intercalaciones. Sin embargo, no estoy seguro de cuánto trabajo podría ser para ti. (Podría ser bastante).

Si estás de acuerdo, eres capaz de dejar un artículo acerca de qué le añadirías a este tutorial.

---

---

Utiliza Nuestro Buscador