PostgreSQL은 "악센트를 구분하지 않는"데이터 정렬을 지원합니까?

PostgreSQL은 "악센트를 구분하지 않는"데이터 정렬을 지원합니까?

---

Microsoft SQL Server에서는 "악센트를 구분하지 않는"데이터 정렬 (데이터베이스, 테이블 또는 열)을 지정할 수 있습니다. 이는 다음과 같은 쿼리가 가능함을 의미합니다.

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

Joao이름이 있는 행을 찾습니다 .

unaccent_string contrib 함수를 사용하여 PostgreSQL의 문자열에서 악센트를 제거 할 수 있다는 것을 알고 있지만 PostgreSQL이 이러한 "악센트를 구분하지 않는"데이터 정렬을 지원하는지 궁금합니다 SELECT.

---

링크하는 것과 완전히 다른 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)은 악의적 인 사용자에 의한 오버로드 된 함수 변형으로 가상의 공격을 방어합니다.

_{Previously, I advocated a wrapper function based on the STABLE function unaccent() shipped with the unaccent module. That disabled function inlining. This version executes ten times faster than the simple wrapper function I had here earlier.
And that was already twice as fast as the first version which added SET search_path = public, pg_temp to the function - until I discovered that the dictionary can be schema-qualified, too. Still (Postgres 12) not too obvious from documentation.}

If you lack the necessary privileges to create C functions, you are back to the second best implementation: An IMMUTABLE function wrapper around the STABLE unaccent() function provided by the module:

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;

Finally, the expression index to make queries fast:

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

Remember to recreate indexes involving this function after any change to function or dictionary, like an in-place major release upgrade that would not recreate indexes. Recent major releases all had updates for the unaccent module.

Adapt queries to match the index (so the query planner will use it):

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

You don't need the function in the right expression. There you can also supply unaccented strings like 'Joao' directly.

The faster function does not translate to much faster queries using the expression index. That operates on pre-computed values and is very fast already. But index maintenance and queries not using the index benefit.

Security for client programs has been tightened with Postgres 10.3 / 9.6.8 etc. You need to schema-qualify function and dictionary name as demonstrated when used in any indexes. See:

• 'text search dictionary “unaccent” does not exist' entries in postgres log, supposedly during automatic analyze

Ligatures

In Postgres 9.5 or older ligatures like 'Œ' or 'ß' have to be expanded manually (if you need that), since unaccent() always substitutes a single letter:

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

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

You will love this update to unaccent in Postgres 9.6:

Extend contrib/unaccent's standard unaccent.rules file to handle all diacritics known to Unicode, and expand ligatures correctly (Thomas Munro, Léonard Benedetti)

Bold emphasis mine. Now we get:

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

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

Pattern matching

For LIKE or ILIKE with arbitrary patterns, combine this with the module pg_trgm in PostgreSQL 9.1 or later. Create a trigram GIN (typically preferable) or GIST expression index. Example for GIN:

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

Can be used for queries like:

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

GIN and GIST indexes are more expensive to maintain than plain btree:

• Difference between GiST and GIN index

There are simpler solutions for just left-anchored patterns. More about pattern matching and performance:

• Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

pg_trgm also provides useful operators for "similarity" (%) and "distance" (<->).

Trigram indexes also support simple regular expressions with ~ et al. and case insensitive pattern matching with ILIKE:

• PostgreSQL accent + case insensitive search

---

No, PostgreSQL does not support collations in that sense

PostgreSQL does not support collations like that (accent insensitive or not) because no comparison can return equal unless things are binary-equal. This is because internally it would introduce a lot of complexities for things like a hash index. For this reason collations in their strictest sense only affect ordering and not equality.

Workarounds

Full-Text-Search Dictionary that Unaccents lexemes.

For FTS, you can define your own dictionary using 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;

Which you can then index with a functional index,

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

You can now query it very simply

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

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

See also

• Creating a case-insensitive and accent/diacritics insensitive search on a field

Unaccent by itself.

The unaccent module can also be used by itself without FTS-integration, for that check out Erwin's answer

---

I'm pretty sure PostgreSQL relies on the underlying operating system for collation. It does support creating new collations, and customizing collations. I'm not sure how much work that might be for you, though. (Could be quite a lot.)

참고URL : https://stackoverflow.com/questions/11005036/does-postgresql-support-accent-insensitive-collations