Поддерживает ли PostgreSQL сортировку без учета акцента?

Question

В Microsoft SQL Server можно указать параметры сортировки без учета акцента (для базы данных, таблицы или столбца), что означает, что это возможно для запроса, подобного

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

найти строку с Joao название.

Я знаю, что можно удалить акценты из строк в PostgreSQL, используя функцию contrib unaccent_string, но мне интересно, поддерживает ли PostgreSQL эти "нечувствительные к акценту" сортировки, чтобы SELECT выше будет работать.

Answer 1

Для этого используйте модуль unaccent, который полностью отличается от того, на что вы ссылаетесь.

unaccent - это словарь текстового поиска, который удаляет ударения (диакритические знаки) из лексем.

Установите один раз для каждой базы данных с:

CREATE EXTENSION unaccent;

Если вы получаете сообщение об ошибке вроде:

ОШИБКА: не удалось открыть файл управления расширением "/usr/share/postgresql/9.x/extension/unaccent.control": такого файла или каталога нет

Установите пакет contrib на сервере базы данных, как указано в следующем ответе:

• Ошибка при создании unaccent расширения на PostgreSQL

Помимо всего прочего, он обеспечивает функцию unaccent() вы можете использовать с вашим примером (где LIKE кажется не нужен).

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

Индекс

Чтобы использовать индекс для этого типа запроса, создайте индекс для выражения. Однако Postgres принимает только IMMUTABLE функции для индексов. Если функция может вернуть другой результат для того же ввода, индекс может молча оборваться.

unaccent() только STABLE не IMMUTABLE

К несчастью, unaccent() только STABLEне IMMUTABLE, Согласно этой теме о pgsql-ошибках, это связано с тремя причинами:

1. Это зависит от поведения словаря.
2. В этом словаре нет проводной связи.
3. Поэтому также зависит от текущего search_path, который может легко измениться.

Некоторые учебные пособия в Интернете дают указание изменить волатильность функции на IMMUTABLE, Этот метод грубой силы может сломаться при определенных условиях.

Другие предлагают простой IMMUTABLE функция обертки(как я сам делал в прошлом).

Сейчас ведутся споры о том, стоит ли делать вариант с двумя параметрамиIMMUTABLE который явно объявляет используемый словарь. Читайте здесь или здесь.

Другой альтернативой будет этот модуль с IMMUTABLEunaccent() функция Musicbrainz, предоставляемая на Github. Сам не проверял. Я думаю, что придумаллучшую идею:

Лучше всего сейчас

Я предлагаю подход, который по крайней меретак же эффективен, как и другие решения, но более безопасный: создайте функцию-оболочку с двухпараметрической формой и "жестко соедините" схему для функции и словаря:

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;

publicсхема, в которой вы установили расширение (public по умолчанию).

Ранее я добавилSET search_path = public, pg_tempк функции - пока я не обнаружил, что словарь тоже может быть дополнен схемой, что в настоящее время (стр. 10) не документировано. Эта версия немного короче и примерно в два раза быстрее в моих тестах на стр. 9.5 и стр. 10.

Обновленная версия по-прежнему не позволяет вставлять функции, потому что функции объявленыIMMUTABLE не может вызывать неизменяемые функции в теле, чтобы позволить это. Вряд ли имеет значение для производительности, пока мы используем индекс выражения на этомIMMUTABLEфункция:

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

Безопасность для клиентских программ была усилена с помощью Postgres 10.3 / 9.6.8 и т. Д. Вамнеобходимо указать квалифицирующую схему функцию и словарь, как показано при использовании в любых индексах. Увидеть:

• "словарь текстового поиска" unaccent "не существует" записей в журнале postgres, предположительно во время автоматического анализа

Адаптируйте ваши запросы, чтобы они соответствовали индексу (чтобы планировщик запросов мог его использовать):

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

Вам не нужна функция в правильном выражении. Вы можете поставить строки без акцента, как'Joao'непосредственно.

Лигатуры

В Postgres9.5 или более ранних версиях лигатуры, такие как "Œ" или "ß", должны быть расширены вручную (если вам это нужно), так какunaccent()всегда заменяетодну букву:

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

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

Вам понравится это обновление до unaccent в Postgres9.6:

простиратьсяcontrib/unaccentстандартunaccent.rulesфайл для обработки всех диакритических знаков, известных Unicode, иправильного расширения лигатур(Томас Манро, Леонард Бенедетти)

Жирный акцент мой. Теперь мы получаем:

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

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

Сопоставление с образцом

ЗаLIKEили жеILIKE с произвольными шаблонами, объедините это с модулем pg_trgmв PostgreSQL 9.1 или более поздней версии. Создайте триграмму GIN (обычно предпочтительно) или индекс выражения GIST. Пример для GIN:

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

Может использоваться для запросов, таких как:

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

Индексы GIN и GIST более дороги в обслуживании, чем обычный btree:

• Разница между GiST и индексом GIN

Существуют более простые решения только для левого якоря. Подробнее о сопоставлении с образцом и производительности:

• Сопоставление с шаблоном LIKE, SIMILAR TO или регулярными выражениями в PostgreSQL

pg_trgmтакже предоставляет полезные операторы для "сходства" (% ) и "расстояние" (<-> )

Индексы триграмм также поддерживают простые регулярные выражения с ~ и другие. и регистронезависимое сопоставление с ILIKE:

• PostgreSQL акцент + регистронезависимый поиск

Answer 2

Нет, PostgreSQL не поддерживает сопоставления в этом смысле

PostgreSQL не поддерживает подобные сортировки (без учета акцента или без), потому что никакое сравнение не может вернуть равное, если вещи не являются бинарными. Это связано с тем, что внутренне это создало бы много сложностей для таких вещей, как хэш-индекс. По этой причине сопоставления в их самом строгом смысле влияют только на порядок, а не на равенство.

обходные

Полнотекстовый поиск словарь, который Unaccents лексемы.

Для FTS вы можете определить свой собственный словарь, используя 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;

Который вы можете затем индексировать с помощью функционального индекса,

-- 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));

Теперь вы можете запросить его очень просто

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

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

Смотрите также

• Создание нечувствительного к регистру и нечувствительного к акценту / диакритике поиска в поле

Сам по себе не акцент.

unaccent Модуль также может использоваться сам по себе без интеграции с FTS, для этого проверьте ответ Эрвина

Answer 3

Я почти уверен, что PostgreSQL полагается на базовую операционную систему для сопоставления. Он поддерживает создание новых параметров сортировки и их настройку. Я не уверен, сколько работы это может быть для вас, хотя. (Может быть довольно много.)