링크하는 것과 완전히 다른 unaccent 모듈 을 사용하십시오 .

unaccent는 어휘에서 악센트 (분음 부호)를 제거하는 텍스트 검색 사전입니다.

다음을 사용하여 데이터베이스 당 한 번 설치합니다.

CREATE EXTENSION unaccent;

다음과 같은 오류가 발생하는 경우 :

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

이 관련 답변에 지시 된대로 데이터베이스 서버에 contrib 패키지를 설치합니다.

• 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있습니다. 여기 또는 여기에서 읽으 십시오 .

또 다른 대안은 Github에서 제공 하는 Musicbrainzunaccent() 의 IMMUTABLE 함수 가있는이 모듈입니다 . 직접 테스트하지 않았습니다. 나는 내가 가지고 올 것 같아요 더 나은 아이디어 :

지금은 최고

이 접근 방식은 다른 솔루션이 떠 다니는 것보다 더 효율적이고 더 안전 합니다. 하드 와이어 스키마 한정 함수 및 사전을 사용하여 두 매개 변수 형식을 실행
하는 IMMUTABLESQL 래퍼 함수를 만듭니다 .

변경 불가능한 함수를 중첩하면 함수 인라인이 비활성화되므로 C 함수 (가짜)도 선언 된 복사본을 기반 IMMUTABLE으로합니다. 그것의 유일한 목적은 SQL 함수 래퍼에 사용됩니다. 그 자체로는 사용할 수 없습니다.

C 함수 선언에서 사전을 고정 할 방법이 없기 때문에 정교함이 필요합니다. (C 코드 자체를 해킹해야합니다.) SQL 래퍼 함수는이를 수행하고 함수 인라인 및 표현식 인덱스를 모두 허용 합니다.

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

PARALLEL SAFEPostgres 9.5 이전 버전의 경우 두 기능에서 모두 삭제합니다 .

public확장을 설치 한 스키마입니다 ( public기본값).

명시 적 유형 선언 ( regdictionary)은 악의적 인 사용자에 의한 오버로드 된 함수 변형으로 가상의 공격을 방어합니다.

_{이전에, 나는 기반으로 래퍼 함수를 옹호 STABLE기능 unaccent()unaccent 모듈과 함께 제공. 기능 인라인을 비활성화했습니다 . 이 버전은 이전에 여기에서 사용했던 간단한 래퍼 함수보다 10 배 빠르게 실행 됩니다.
그리고 그것은 이미 SET search_path = public, pg_temp함수에 추가 된 첫 번째 버전보다 두 배나 빨랐 습니다. 딕셔너리도 스키마 검증이 가능하다는 것을 발견하기 전까지는 말입니다. 여전히 (Postgres 12) 문서에서 너무 명확하지 않습니다.}

경우 : 당신이 C 함수를 만드는 데 필요한 권한이 부족, 당신은 다시 두 번째 최고의 구현에 있습니다 IMMUTABLE주위에 함수 래퍼 STABLE unaccent()모듈에 의해 제공되는 기능 :

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;

마지막으로 쿼리를 빠르게 만드는 표현식 인덱스 :

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

인덱스 를 다시 만들지 않는 인플레 이스 메이저 릴리스 업그레이드와 같이 함수 나 사전을 변경 한 후에는이 함수와 관련된 인덱스 를 다시 만들어야합니다. 최근 주요 릴리스에는 모두 unaccent모듈에 대한 업데이트가 있습니다 .

인덱스와 일치하도록 쿼리를 조정합니다 (쿼리 플래너가 사용하도록 함).

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

올바른 표현식에는 함수가 필요하지 않습니다. 거기에서 'Joao'직접 처럼 악센트가없는 문자열을 제공 할 수도 있습니다.

더 빠른 함수는 index 표현식을 사용하는 훨씬 더 빠른 쿼리로 변환되지 않습니다 . 그것은 미리 계산 된 값에서 작동하며 이미 매우 빠릅니다. 그러나 인덱스 유지 관리 및 인덱스를 사용하지 않는 쿼리는 이점이 있습니다.

클라이언트 프로그램에 대한 보안은 Postgres 10.3 / 9.6.8 등으로 강화되었습니다 . 인덱스에서 사용할 때 입증 된대로 함수와 사전 이름을 스키마 한정 해야 합니다. 보다:

• postgres 로그에 ‘텍스트 검색 사전 “unccent”가 존재하지 않음’항목, 자동 분석 중

합자

Postgres 9.5 또는 ‘Œ’또는 ‘ß’와 같은 이전 합자는 unaccent()항상 단일 문자를 대체 하므로 수동으로 확장해야합니다 (필요한 경우) .

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

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

Postgres 9.6 에서 unaccent 에 대한 이 업데이트를 좋아할 것입니다 .

확장 contrib/unaccent의 표준 unaccent.rules유니 코드에 알려진 모든 발음 구별 부호를 처리하기 위해 파일을, 그리고 제대로 합자을 확장 (토마스 먼로, 레너드 베네 데티)

대담하게 강조합니다. 이제 우리는 다음을 얻습니다.

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

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

패턴 매칭

내용 LIKE또는 ILIKE임의의 패턴으로 상기 모듈이 결합 pg_trgmPostgreSQL의 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또한 “유사성”( %) 및 “거리”( <->)에 대한 유용한 연산자를 제공합니다 .

Trigram 인덱스는 ~et al. 및 대소 문자를 구분하지 않는 패턴 일치 ILIKE:

• PostgreSQL 악센트 + 대소 문자를 구분하지 않는 검색

답변

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)