tutorial to_tsvector to_tsquery full fts against sql postgresql localization indexing pattern-matching

sql - to_tsvector - to_tsquery



¿PostgreSQL admite colaciones "insensibles a los acentos"? (3)

No, PostgreSQL no admite colaciones en ese sentido

PostgreSQL no admite intercalaciones como esa (insensible a los acentos o no) porque ninguna comparación puede devolver igual a menos que las cosas sean binarias. Esto es porque internamente introduciría muchas complejidades para cosas como un índice hash. Por esta razón, las colaciones en su sentido más estricto solo afectan el orden y no la igualdad.

Soluciones provisionales

Diccionario de búsqueda de texto completo que unaccents lexemes.

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 puedes 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

Unaccent por sí mismo.

El módulo de unaccent también se puede usar solo sin integración FTS, para eso echa un vistazo a la respuesta de Erwin

En Microsoft SQL Server, es posible especificar una intercalación "insensible al acento" (para una base de datos, tabla o columna), lo que significa que es posible para una consulta como

SELECT * FROM users WHERE name LIKE ''João''

para encontrar una fila con un nombre Joao .

Sé que es posible quitar los acentos de las cadenas en PostgreSQL usando la función de contribución unaccent_string , pero me pregunto si PostgreSQL admite estas intercalaciones "insensibles al acento" para que el SELECT anterior funcione.

Utiliza el módulo de acento para eso, que es completamente diferente de lo que estás enlazando.

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

Instalar una vez por base de datos con:

CREATE EXTENSION unaccent;

Si obtiene un error como:

ERROR: no se pudo abrir el archivo de control de extensión "/usr/share/postgresql/9.x/extension/unaccent.control": no existe dicho archivo o directorio

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

Entre otras cosas, proporciona la función unaccent() que puede usar con su ejemplo (donde LIKE parece no ser 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 funciones IMMUTABLE para índices. Si una función puede devolver un resultado diferente para la misma entrada, el índice podría romperse silenciosamente.

unaccent() solo STABLE no IMMUTABLE

Desafortunadamente, unaccent() es solo STABLE , no IMMUTABLE . De acuerdo con este hilo en pgsql-bugs , esto se debe a tres razones:

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

Algunos tutoriales en la web indican simplemente alterar la volatilidad de la función a IMMUTABLE . Este método de fuerza bruta puede romperse bajo ciertas condiciones.

Otros sugieren una función de contenedor IMMUTABLE simple (como yo mismo lo hice en el pasado).

Existe un debate continuo sobre si hacer que la variante con dos parámetros IMMUTABLE declare explícitamente el diccionario usado. Lea here o here .

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

Mejor por ahora

Propongo un enfoque que sea al menos tan eficiente como otras soluciones que flotan, pero más seguro : cree una función de envoltura con la forma de dos parámetros y "hard-wire" el esquema para la función y el diccionario:

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

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

Anteriormente, había agregado SET search_path = public, pg_temp a la función, hasta que descubrí que el diccionario también puede ser calificado por esquema, que actualmente (página 10) no está documentado . Esta versión es un poco más corta y aproximadamente el doble de rápida en mis pruebas en la página 9.5 y la página 10.

La versión actualizada aún no permite la alineación de funciones porque las funciones declaradas IMMUTABLE pueden no llamar a funciones no inmutables en el cuerpo para permitir eso. Poco importa el rendimiento mientras hacemos uso de un índice de expresión en esta función IMMUTABLE :

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

Adapte sus consultas para que coincidan con el índice (para que el planificador de consultas pueda usarlo):

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

No necesita la función en la expresión correcta. Puede suministrar cadenas sin acentos como ''Joao'' directamente.

Ligaduras

En Postgres 9.5 o versiones anteriores, las ligaduras como ''Œ'' o ''ß'' deben expandirse manualmente (si es necesario), ya que unaccent() siempre sustituye una sola letra:

SELECT unaccent(''Œ Æ œ æ ß''); unaccent ---------- E A e a S

Le encantará esta actualización de acento en Postgres 9.6 :

Extienda el archivo estándar unaccent.rules contrib/unaccent para manejar todos los diacríticos conocidos por Unicode, y expanda las ligaduras correctamente (Thomas Munro, Léonard Benedetti)

Negrita énfasis 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 GIN trigram (generalmente preferible) o un índice de expresión GIST. Ejemplo para GIN:

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

Puede ser utilizado para consultas como:

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

Los índices GIN y GIST son más caros de mantener que el btree simple:

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

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

Los índices Trigram también admiten expresiones regulares simples con ~ et al. ILIKE patrones insensibles a mayúsculas y minúsculas con ILIKE :