Pregunta ¿PostgreSQL admite colaciones "insensibles a los acentos"?


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 Joao nombre.

Sé que es posible quitar acentos de cadenas en PostgreSQL usando el unaccent_string función contrib, pero me pregunto si PostgreSQL admite estas intercalaciones "insensibles al acento" por lo que SELECT arriba funcionaría.


74
2018-06-12 21:19


origen


Respuestas:


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

ACICENTE es un diccionario de búsqueda de texto que elimina acentos (diacrítico   signos) 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": ningún archivo de este tipo   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() puedes usarlo con tu 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, crea 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 STABLEno IMMUTABLE. De acuerdo a este hilo en pgsql-bugs, esto es debido 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 corriente search_path, que puede cambiar fácilmente

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

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

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

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

Mejor por ahora

Propongo un enfoque que sea al menos tan eficiente como otras soluciones flotando, 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 público.unaccent('public.unaccent', $1)  -- schema-qualify function and dictionary
$func$  LANGUAGE sql IMMUTABLE;

public siendo el esquema donde instaló la extensión (publices el 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 función en línea porque las funciones declaradas IMMUTABLE no puede 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 este IMMUTABLE función:

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 más viejo ligaduras como 'Œ' o 'ß' tienen que expandirse manualmente (si es necesario), ya que unaccent() siempre sustituye a soltero carta:

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

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

Amarás esta actualización a acento en Postgres 9.6:

Ampliar contrib/unaccentestándar de s unaccent.rules archivo para manejar todo   signos diacríticos conocidos por Unicode, y expandir 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

por 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 útiles operadores de "similitud" (%) y "distancia" (<->).

Los índices Trigram también admiten expresiones regulares simples con ~ et al. y case insensible coincidencia de patrones con ILIKE:


143
2018-06-13 01:51



Estoy bastante seguro de que PostgreSQL depende del sistema operativo subyacente para la intercalación. Eso hace apoyo creando nuevas colacionesy personalizar colaciones. Sin embargo, no estoy seguro de cuánto trabajo podría ser para ti. (Podría ser bastante)


2
2018-06-12 21:54



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 colaciones en su sentido más estricto solo afecta el orden y no igualdad

Soluciones provisionales

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

Para FTS, puede definir su propio diccionario utilizando 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.

los unaccent módulo también se puede usar solo sin integración FTS, para ese control La respuesta de Erwin


0
2018-05-30 01:33