[解決済み] PostgreSQLは "アクセントを考慮しない "照合順序をサポートしていますか？

2022-08-16 18:34:23

質問

Microsoft SQL Server では、（データベース、テーブル、または列に対して） "アクセント記号なし照合順序を指定することが可能です。

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

を持つ行を探すために Joao という名前になります。

PostgreSQLでは、文字列からアクセント記号を取り除くことができることは知っています。 unaccent_string を使用して文字列からアクセントを取り除くことができることは知っていますが、PostgreSQLがこれらのquot;accent insensitive"照合をサポートしているかどうか疑問に思っています。 SELECT は動作するのでしょうか？

どのように解決するのですか？

この場合 unaccentモジュール を追加しました。これは、あなたがリンクしているものとはまったく別のものです。

unaccent は、語彙からアクセント（発音記号）を除去するテキスト検索辞書です。記号) を除去するテキスト検索辞書です。

でデータベースごとに1回インストールします。

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拡張を作成する際にエラーが発生しました。

とりわけ、これは関数 unaccent() という関数が用意されています（ここで LIKE は必要ないようです)。

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

インデックス

このようなクエリにインデックスを使うには、以下のようにインデックスを作成します。 . しかし は、Postgresは IMMUTABLE 関数しか受け付けません。もし関数が同じ入力に対して異なる結果を返すことができれば、インデックスは黙って壊れてしまうかもしれません。

`unaccent()` のみ `STABLE` ない `IMMUTABLE`

残念ながら unaccent() だけです。 STABLE ではなく IMMUTABLE . ということは pgsql-bugs のこのスレッドによると、これは 3 の理由によるものです。

辞書の動作に依存する。
このディクショナリーへのハードワイヤードの接続はありません。
したがって、これはまた、現在の search_path にも依存し、簡単に変更できます。

いくつかのチュートリアルを変更するように指示するものもあります。 IMMUTABLE . このブルートフォースメソッドは、特定の条件下で壊れることがあります。

他の人が提案するのはシンプル IMMUTABLE ラッパー関数 (を使うことができます（過去に私自身がやったように）。

にするかどうかという議論が続いています。バリアントを2つのパラメータを持つ IMMUTABLE で、使用する辞書を明示的に宣言します。読むここでまたはここで .

別の方法として、このモジュールに IMMUTABLE unaccent() という関数で、Musicbrainz Githubで提供されています。自分ではテストしていない。私が思いついたのは より良いアイデア :

今のところベスト

この方法は より効率的で、より安全な方法です。 .

を作成します。 IMMUTABLE スキーマで修飾された関数と辞書を持つ2パラメータフォームを実行する SQL ラッパー関数を作成します。

不変でない関数をネストすると関数のインライン化ができなくなるので、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 SAFE を削除してください。

public は、拡張機能をインストールしたスキーマ ( public がデフォルトです)。

明示的な型宣言( regdictionary ) は、悪意のあるユーザによる関数のオーバーロードされた亜種による仮想的な攻撃を防御します。

_{前回、私はラッパー関数として
STABLE
関数
unaccent()
は unaccent モジュールと一緒に出荷されました。そのため、無効化された

関数インライン化

. このバージョンでは

10倍速い

を実現しています。

そしてそれは、最初のバージョンで
SET search_path = public, pg_temp
を関数に追加した最初のバージョンよりもすでに 2 倍速くなっていました。

まだ (Postgres 12) ドキュメントではあまり明らかではありません。}

もしの場合、C関数を作成するのに必要な権限がないため、2番目に良い実装に戻ります。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' のようなアクセントのない文字列を直接指定することもできます。

を使ったクエリの高速化にはつながりません。 式インデックス . これは事前に計算された値に対して操作するもので、すでに非常に高速です。しかし、インデックスのメンテナンスとインデックスを使用しないクエリが利益を得ます。

Postgres 10.3 / 9.6.8 などで、クライアントプログラムのセキュリティが強化されました。あなたは が必要です。 が必要です。また、インデックスで使用する場合は、関数と辞書名をスキーマ修飾する必要があります。参照してください。

テキスト検索辞書 "unaccent" が存在しない」というエントリーが postgres のログに残っています（おそらく自動解析中）。

合字

Postgresでは 9.5 またはそれ以前 のような合字は手動で展開する必要があります（必要な場合）。 unaccent() は常に シングル の文字に置き換えられます。

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

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

あなたが好きなのはへのこのアップデートは、unaccent を Postgres の 9.6 :

拡張 contrib/unaccent の標準的な unaccent.rules ファイルで、Unicode で知られているすべてのというファイルです。 合字を正しく展開する (トーマス Munro, Léonard Benedetti)。

太字強調は私です。これでわかった。

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インデックスの違い

左寄せのパターンだけであれば、もっと簡単な解決策があります。パターンマッチとパフォーマンスについての詳細。

PostgreSQL における LIKE, SIMILAR TO あるいは正規表現によるパターンマッチ。

pg_trgm はまた、便利な演算子も用意されています ( % ) と "distance" ( <-> ) .

トリグラムインデックスは ~ など、また 大文字小文字を区別しない とのパターンマッチ ILIKE :

PostgreSQLのアクセント＋大文字小文字を区別しない検索