W Microsoft SQL Server można określić sortowanie „niewrażliwe na akcenty” (dla bazy danych, tabeli lub kolumny), co oznacza, że ​​jest możliwe dla zapytania takiego jak

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

aby znaleźć wiersz z Joaonazwą.

Wiem, że w PostgreSQL można usunąć akcenty z ciągów znaków za pomocą funkcji dodawania unaccent_string, ale zastanawiam się, czy PostgreSQL obsługuje te sortowania „niewrażliwe na akcenty”, aby SELECTpowyższe działało.

Użyj do tego modułu bez akcentu - który jest zupełnie inny niż to, do którego tworzysz link.

unaccent to słownik wyszukiwania tekstu, który usuwa akcenty (znaki diakrytyczne) z leksemów.

Zainstaluj raz na bazę danych za pomocą:

CREATE EXTENSION unaccent;

Jeśli pojawi się błąd, taki jak:

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

Zainstaluj pakiet Contrib na serwerze bazy danych, zgodnie z instrukcją w tej powiązanej odpowiedzi:

• Błąd podczas tworzenia nieakcentowanego rozszerzenia w PostgreSQL

Między innymi zapewnia funkcję, unaccent()której możesz użyć na swoim przykładzie (gdzie LIKEwydaje się, że nie jest potrzebna).

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

Indeks

Aby użyć indeksu dla tego rodzaju zapytania, utwórz indeks dla wyrażenia . Jednak Postgres akceptuje tylko IMMUTABLEfunkcje dla indeksów. Jeśli funkcja może zwrócić inny wynik dla tego samego wejścia, indeks może po cichu przerwać.

unaccent()tylko STABLEnieIMMUTABLE

Niestety unaccent()tylko STABLEnie IMMUTABLE. Zgodnie z tym wątkiem dotyczącym błędów pgsql , wynika to z trzech powodów:

1. Zależy to od zachowania słownika.
2. Nie ma stałego połączenia z tym słownikiem.
3. Zależy to zatem również od prądu search_path, który może się łatwo zmieniać.

Niektóre samouczki w sieci WWW zalecają zmianę zmienności funkcji na IMMUTABLE. Ta metoda brutalnej siły może się zepsuć w pewnych warunkach.

Inni sugerują prostą IMMUTABLEfunkcję opakowującą (tak jak ja robiłem to w przeszłości).

Trwa debata, czy utworzyć wariant z dwoma parametrami, IMMUTABLE który jawnie deklaruje używany słownik. Przeczytaj tutaj lub tutaj .

Inną alternatywą byłoby moduł z niezmiennymi unaccent()funkcji przez Musicbrainz , umieszczonych na Github. Sam tego nie testowałem. Myślę, że wpadłem na lepszy pomysł :

Na razie najlepsze

Takie podejście jest bardziej wydajne niż inne rozwiązania i bezpieczniejsze .
Utwórz funkcję IMMUTABLEopakowującą SQL wykonującą formularz dwuparametrowy z okablowaną funkcją kwalifikowaną schematu i słownikiem.

Od gniazdowania non-niezmienny funkcję spowodowałoby wyłączenie funkcji inline, baza go na kopii C-funkcji (fałszywej) ogłosił IMMUTABLErównież. Jego jedynym celem jest użycie w opakowaniu funkcji SQL. Nie jest przeznaczony do samodzielnego użytku.

Wymagane jest wyrafinowanie, ponieważ nie ma sposobu na trwałe połączenie słownika w deklaracji funkcji C. (Wymagałoby to zhakowania samego kodu C.) Funkcja opakowująca SQL robi to i umożliwia zarówno wstawianie funkcji, jak i indeksy wyrażeń.

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

Usuń PARALLEL SAFEz obu funkcji dla Postgres 9.5 lub starszych.

publicjest schematem, w którym zainstalowałeś rozszerzenie ( publicjest to ustawienie domyślne).

Jawna deklaracja typu ( regdictionary) chroni przed hipotetycznymi atakami z przeciążonymi wariantami funkcji ze strony złośliwych użytkowników.

_{Wcześniej opowiadałem się za funkcją opakowującą opartą na STABLEfunkcji unaccent()dostarczanej z modułem unaccent. Ta wyłączona funkcja inlining . Ta wersja wykonuje dziesięć razy szybciej niż prosta funkcja opakowująca, którą miałem tutaj wcześniej.
Było to już dwa razy szybsze niż pierwsza wersja, która została dodana SET search_path = public, pg_tempdo funkcji - dopóki nie odkryłem, że słownik również może być kwalifikowany według schematu. Jednak (Postgres 12) nie jest zbyt oczywiste z dokumentacji.}

Jeśli nie masz uprawnień niezbędnych do tworzenia funkcji w C, wracasz do drugiej najlepszej implementacji: IMMUTABLEopakowania funkcji wokół STABLE unaccent()funkcji udostępnianej przez moduł:

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;

Na koniec indeks wyrażeń do szybkiego wykonywania zapytań :

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

Pamiętaj o ponownym utworzeniu indeksów obejmujących tę funkcję po każdej zmianie funkcji lub słownika, na przykład po uaktualnieniu głównej wersji w miejscu, które nie spowoduje odtworzenia indeksów. Wszystkie ostatnie główne wydania zawierały aktualizacje unaccentmodułu.

Dostosuj zapytania do indeksu (aby planista zapytań go użył):

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

Nie potrzebujesz funkcji w odpowiednim wyrażeniu. Można tam również dostarczyć sznurki bez akcentu, jak 'Joao'bezpośrednio.

Szybsza funkcja nie przekłada się na znacznie szybsze zapytania korzystające z indeksu wyrażenia . To działa na wstępnie obliczonych wartościach i jest już bardzo szybkie. Ale utrzymanie indeksu i zapytania nie korzystają z indeksu.

Zabezpieczeń dla programów klienckich zostały dokręcone PostgreSQL 10,3 / 9.6.8 itp Państwo potrzebują do schematu zakwalifikować funkcję i nazwę słownika, jak wykazano w przypadku stosowania w dowolnych pozycjach. Widzieć:

• „Nie ma w słowniku wyszukiwania tekstu„ brak akcentów ”w dzienniku postgres, rzekomo podczas automatycznej analizy

Ligatury

W Postgres 9.5 lub starszych ligaturach, takich jak `` Œ '' lub `` ß '', należy rozszerzyć ręcznie (jeśli jest to potrzebne), ponieważ unaccent()zawsze zastępuje jedną literę:

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

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

Spodoba ci się ta aktualizacja, która zniknęła w Postgres 9.6 :

Rozszerz contrib/unaccentstandardowy unaccent.rulesplik, aby obsługiwał wszystkie znaki diakrytyczne znane w Unicode i poprawnie rozszerzaj ligatury (Thomas Munro, Léonard Benedetti)

Odważne podkreślenie moje. Teraz otrzymujemy:

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

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

Dopasowanie wzorców

Dla LIKElub ILIKEz dowolnymi wzorcami, połącz to z modułem pg_trgmw PostgreSQL 9.1 lub nowszym. Utwórz trygram GIN (zazwyczaj preferowany) lub indeks wyrażenia GIST. Przykład dla WZ:

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

Może być używany do zapytań takich jak:

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

Indeksy GIN i GIST są droższe w utrzymaniu niż zwykłe btree:

• Różnica między GiST a indeksem GIN

Istnieją prostsze rozwiązania dla wzorów zakotwiczonych w lewo. Więcej o dopasowywaniu wzorców i wydajności:

• Dopasowywanie wzorców za pomocą LIKE, SIMILAR TO lub wyrażeń regularnych w PostgreSQL

pg_trgmzawiera również przydatne operatory dla „podobieństwa” ( %) i „odległości” ( <->) .

Indeksy Trigram obsługują również proste wyrażenia regularne z ~et al. i dopasowywanie wzorca bez rozróżniania wielkości liter z ILIKE:

• Akcent PostgreSQL + wyszukiwanie bez rozróżniania wielkości liter

Nie, PostgreSQL nie obsługuje sortowania w tym sensie

PostgreSQL nie obsługuje tego typu sortowania (bez uwzględniania akcentu lub nie), ponieważ żadne porównanie nie może zwrócić równości, chyba że rzeczy są binarnie równe. Dzieje się tak, ponieważ wewnętrznie wprowadziłoby to wiele złożoności dla rzeczy takich jak indeks skrótu. Z tego powodu zestawienia w najściślejszym znaczeniu mają wpływ tylko na porządek, a nie na równość.

Obejścia

Słownik wyszukiwania pełnotekstowego, który usuwa leksemy.

W przypadku FTS możesz zdefiniować własny słownik za pomocą 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;

Które możesz następnie zindeksować za pomocą indeksu funkcjonalnego,

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

Możesz teraz zapytać go w bardzo prosty sposób

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

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

Zobacz też

• Tworzenie wyszukiwania bez rozróżniania wielkości liter i bez akcentu / znaków diakrytycznych w polu

Sam w sobie nieakcentowany.

unaccentModuł może być również stosowany samodzielnie bez FTS-integracji, w tym wyjazd odpowiedź Erwina

Jestem prawie pewien, że PostgreSQL polega na podstawowym systemie operacyjnym do sortowania. To ma wspierać tworzenie nowych sortowania i dostosowywania ustawień sortowania . Nie jestem jednak pewien, ile może to być pracy dla Ciebie. (Może być całkiem sporo.)