Użyj moduł unaccent do tego-co jest zupełnie inne od tego, do czego linkujesz.

Unaccent jest słownikiem wyszukiwania tekstu, który usuwa akcenty (znaki diakrytyczne znaki) z leksemes.

Zainstaluj raz na bazę danych za pomocą:

CREATE EXTENSION unaccent;

Jeśli pojawi się błąd w stylu:

Błąd: nie można otworzyć pliku kontroli rozszerzenia "/usr / share / postgresql / 9.x / extension / unaccent.control": brak takiego pliku lub katalog

Zainstaluj pakiet contrib na serwerze bazy danych zgodnie z Instrukcją w tej powiązanej odpowiedzi:

• błąd podczas tworzenia unaccent extension w PostgreSQL

Zawiera między innymi funkcję unaccent(), której możesz użyć w swoim przykładzie (gdzie LIKE wydaje się nie być potrzebna).

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

Indeks

Aby użyć indeksu dla tego rodzaju zapytań, Utwórz indeks na wyrażeniu. Jednakże , Postgres akceptuje tylko funkcje IMMUTABLE dla indeksów. Jeśli funkcja może zwrócić inny wynik dla tego samego wejścia, indeks może się po cichu złamać.

unaccent() tylko STABLE Nie IMMUTABLE

Niestety, unaccent() jest tylko STABLE, a nie IMMUTABLE. Zgodnie z ten wątek na pgsql-bugs, wynika to z trzech powodów:

To zależy od zachowania słownika.
1. nie ma połączenia przewodowego z tym słownikiem.
2. dlatego też zależy od prądu search_path, który może się łatwo zmienić.

Niektóre tutoriale w sieci instruują, aby po prostu zmienić zmienność funkcji na IMMUTABLE. Ta metoda brute-force może pęknąć w pewnych warunkach.

Inni sugerują prostą IMMUTABLE funkcję wrappera (tak jak ja sam w przeszłości).

Trwa dyskusja czy zrobić wariant z dwoma parametrami IMMUTABLE który wyraźnie deklaruje używany słownik. Czytaj tutaj lub tutaj .

Inną alternatywą byłby ten moduł z niezmienną unaccent() funkcją Musicbrainz , dostarczoną na Githubie. Sam tego nie testowałem. Myślę, że wymyśliłem lepszy pomysł:

Najlepsze na razie

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 jest to schemat, w którym zainstalowano rozszerzenie (public jest domyślne).

wcześniej dodałem SET search_path = public, pg_temp do funkcji-dopóki nie odkryłem, że słownik może być również klasyfikowany według schematu, , który obecnie (pg 10) nie jest udokumentowany . Ta wersja jest nieco krótsza i około dwa razy szybsza w moich testach na pg 9.5 i pg 10.

Zaktualizowana wersja nadal nie pozwala funkcji inlining ponieważ funkcje zadeklarowane IMMUTABLE nie mogą wywoływać funkcji nieujemnych w ciele, aby na to pozwolić. Nie ma znaczenia dla wydajności, podczas gdy korzystamy z indeks wyrażeń na tej funkcji IMMUTABLE:

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

Bezpieczeństwo programów klienckich zostało zaostrzone w Postgres 10.3 / 9.6.8 itd. Musisz aby schema-qualify function and dictionary były pokazane, gdy są używane w dowolnych indeksach. Zobacz:

• 'Słownik wyszukiwania tekstu "unaccent" nie istnieje' wpisy w dzienniku postgres, rzekomo podczas automatycznej analizy

Dostosuj swoje zapytania do indeksu (aby planista zapytań mógł go użyć):

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

Nie potrzebujesz funkcji w odpowiednim wyrażeniu. Możesz dostarczyć unactened ciągi jak 'Joao' bezpośrednio.

Ligatury

W Postgres 9.5 lub starszych ligatury takie jak' œ 'lub' ß ' muszą być rozszerzane ręcznie (jeśli tego potrzebujesz), ponieważ unaccent() zawsze zastępuje a single letter:

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

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

Pokochasz ten update to unaccent W Postgres 9.6:

Rozszerz contrib/unaccent standardowy plik unaccent.rules, aby obsłużyć wszystkie znaki diakrytyczne znane Unicode i rozwiń ligatury poprawnie (Thomas Munro, Léonard Benedetti)

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

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

Dopasowanie wzorca

Dla LIKE lub ILIKE z dowolnymi wzorami, połącz to z moduł pg_trgm W PostgreSQL 9.1 lub nowszym. Utwórz indeks wyrażenia trygram GIN (zwykle preferowany) lub GIST. Przykład dla ginu:

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

Może być używany do zapytań typu:

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

• różnica między indeksem GiST i GIN

Istnieją prostsze rozwiązania dla tylko lewych zakotwiczonych wzorów. Więcej o dopasowywaniu wzorów i wydajność:

• dopasowanie wzorca do podobnych, podobnych do lub wyrażeń regularnych w PostgreSQL

pg_trgm zawiera również użyteczne operatory dla "podobieństwa" (%) i "odległości" (<->).

Indeksy Trygramowe obsługują również proste wyrażenia regularne z ~ et al. and case niewrażliwe wzór pasujący do ILIKE:

• PostgreSQL accent + case insensitive search