★PostgreSQLカンファレンス2024 12月6日開催/チケット販売中★
他のバージョンの文書 16 | 15 | 14 | 13 | 12 | 11 | 10 | 9.6 | 9.5 | 9.4 | 9.3 | 9.2 | 9.1 | 9.0 | 8.4 | 8.3 | 8.2 | 8.1 | 8.0 | 7.4 | 7.3 | 7.2

24.2. 照合順序サポート

照合順序機能は、ソート順番と列ごともしくは操作ごとのデータの文字区別の振る舞いを指定することを可能にします。 これにより、作成後のデータベースのLC_COLLATELC_CTYPEの設定が変更できない制限が緩和されます。

24.2.1. 概念

概念的に照合可能なデータ型のそれぞれの式は、照合順序を保持しています (組み込みの照合可能なデータ型はtextvarcharcharです。 ユーザ定義の基本型は照合可能とマーキングできます。もちろん照合可能なデータ型上のドメインは照合可能となります)。 もし、式が列参照である場合は、式の照合順序は列の定義された照合順序となります。 もし、式が定数である場合は、照合順序は定数のデータ型のデフォルトの照合順序となります。 より複雑な式の照合順序は、下記に示すように、その入力の照合順序から引き出されます。

式の照合順序は、default照合順序となります。これはデータベースに対して定義されたロケール設定を意味しています。 式の照合順序は非決定となることもあります。そのような場合に、照合順序が必要となるような順序操作や他の操作は失敗するでしょう。

データベースシステムが並び変えや文字区別を行う場合、データベースは入力の照合順序を使用します。 これは、たとえばORDER BY句や<演算子や関数を使用する際に発生します。 ORDER BY句に適用する照合順序は、単純にソートキーの照合順序です。 関数や演算子の呼び出しに対して適用される照合順序は、以下に述べるように引数により決まります。 比較演算子に加えて、照合順序はlowerupperinitcapといった小文字と大文字を変換する関数やパターンマッチングの演算子、to_char関連の関数で考慮されています。

関数や演算子の呼び出しに対して、引数の照合順序検査により得られた照合順序は実行時に特定の操作を行うために使用されます。 もし関数や演算子の呼び出しの結果が照合順序可能なデータ型であった場合、照合順序は関数もしくは演算子式の定義済みの照合順序として 解析時にも試用されます。このとき照合順序の知識が必要となるような囲み式があります。

式の照合順序の導出は暗黙でも明示的にでも可能です。 この区別は、複数の異なる照合順序が式中に現れるときに照合順序がどのように組み合わされるか、に影響を与えます。 明示的な照合順序の導出は、COLLATE句が使用されたときに発生します。 他の全ての照合順序は暗黙となります。例えば関数呼び出しの中では、次の規則が用いられます。

  1. 入力式に明示的な照合順序の導出がある場合、入力式の中の明示的に導出された全ての照合順序は同一でなくてはなりません。 そうでない場合はエラーが発生します。もし明示的に導出された照合順序がある場合は、それは照合順序の組み合わせの結果となります。

  2. そうでない場合は、全ての入力式は同一の暗黙の照合順序の導出またはデフォルトの照合順序を持たなくてはなりません。 もしデフォルトではない照合順序がある場合は、それは照合順序の組み合わせの結果となります。 もしそうでない場合は、結果はデフォルトの照合順序となります。

  3. 入力式内でデフォルトではない暗黙の照合順序が衝突している場合、決定不能な照合順序であるとみなされます。 これは、もし呼び出された特定の関数が適用するべき照合順序を知っておく必要がないかぎりエラーの条件ではありません。 もし知っておく必要がある場合は、実行時にエラーとなります。

例えば、このテーブル定義を考えてみます。

CREATE TABLE test1 (
    a text COLLATE "de_DE",
    b text COLLATE "es_ES",
    ...
);

このとき

SELECT a < 'foo' FROM test1;

<の比較はde_DEの規則により実行されます。 というのも式は暗黙的に導出されたデフォルトの照合順序と組み合わせます。しかし、

SELECT a < ('foo' COLLATE "fr_FR") FROM test1;

このとき比較は、明示的な照合順序の導出は暗黙の照合順序をオーバライドするためfr_FR規則が用いられます。 さらに、次の例では

SELECT a < b FROM test1;

パーサはどの照合順序を適用するか決定できません。というのもab列は暗黙の衝突する照合順序を持つためです。 <演算子がどちらの照合順序を使用するか知る必要があるため、これはエラーとなります。

SELECT a < b COLLATE "de_DE" FROM test1;

もしくは同じく

SELECT a COLLATE "de_DE" < b FROM test1;

一方で、以下のように構造的に似たケースとして

SELECT a || b FROM test1;

これはエラーとなりません。というのも||演算子は、照合順序には関係がないためです。 この結果は照合順序とは関係なく同じになります。

もし関数や演算子が照合順序可能なデータ型の結果を出力する場合は、関数に割り当てられた照合順序、もしくは演算子の組み合わされた入力式は、関数もしくは演算子の結果に対しても 適用されると考えられます。よって、以下の例では

SELECT * FROM test1 ORDER BY a || 'foo';

順序はde_DE規則に基づき実行されますが、以下のクエリでは

SELECT * FROM test1 ORDER BY a || b;

エラーとなります。というのも||演算子が照合順序を知る必要がなかったとしても ORDER BY句が照合順序を知る必要があるためです。 以前と同様、この衝突は明示的に照合順序を指定することにより解決できます。

SELECT * FROM test1 ORDER BY a || b COLLATE "fr_FR";

24.2.2. 照合順序の管理

照合順序は、SQL名称を、オペレーティングシステム中にインストールされたライブラリによって提供されるロケールにマッピングするSQLスキーマオブジェクトです。 照合順序の定義には、ロケールデータを提供するライブラリを指定するプロバイダ(provider)が含まれます。 標準プロバイダの一つはlibcで、オペレーティングシステムのCライブラリが提供するロケールを使用します。 オペレーティングシステムが提供するほとんどのツールが、このロケールを使用します。 他のプロバイダとしてはicuがあり、外部のICUライブラリを使います。 ICUロケールは、PostgreSQLがビルドされた際にICUサポートが設定されていた場合にのみ利用可能です。

libcが提供する照合順序は、setlocale()システムライブラリの呼び出しが許可するLC_COLLATELC_CTYPEの組み合わせ設定にマッピングします。 (名称から推測されるように、照合順序の主な目的はソート順序を制御するLC_COLLATEを設定することです。 しかし実際にはLC_CTYPEの設定をLC_COLLATEと異なるようにする必要はほとんどありません。 そのため、式ごとにLC_CTYPEを設定するような別の機構を作成するより、これらの設定を収集する方が、より便利です。) また、libcの照合順序は文字エンコーディングと結びついています(24.3を参照下さい)。 同一の照合順序名称が異なるエンコーディングに対して存在しています。

icuが提供する照合順序オブジェクトは、ICUライブラリが提供する照合順序機能(collator)にマップします。 ICUはcollatectypeを別々に設定する機能を提供しないので、それら常に同じものになります。 また、ICUの照合順序はエンコーディングからは独立しています。 ですから、データベース中のある名前のICU照合順序は、常にただひとつだけです。

24.2.2.1. 標準の照合順序

すべてのプラットフォーム上でdefaultCそしてPOSIXという名称の照合順序が 利用できます。 オペレーティングシステムによっては追加の照合順序が利用可能な場合もあります。 default照合順序は、データベース作成時にLC_COLLATE値とLC_CTYPE値を 選択します。CPOSIX照合順序は共にtraditional Cの動作を指定します。 これはASCII文字のAからZを文字として扱い、ソート順は厳密な文字コードのバイト値によります。

加えて、エンコーディングUTF8では、SQL標準の照合順序名ucs_basicが利用できます。 ucs_basicCと同等のもので、ソート順はUnicodeのコードポイントです。

24.2.2.2. 定義済みの照合順序

オペレーティングシステムが単一のプログラム内(newlocaleや関連する関数)で複数のロケールを使用することをサポートしているか、ICUサポートが組み込み済みの場合、データベースクラスタが初期化されるとinitdbは、オペレーティングシステム上で見つけた全てのロケールに基づく照合順序をシステムカタログのpg_collationに書き込みます。

現在利用可能なロケールを調べるには、SELECT * FROM pg_collationという問合せを使うか、psql内で\dOS+コマンドを使用します。

24.2.2.2.1. libc照合順序

例えば、オペレーティングシステムがde_DE.utf8という名称のロケールを提供するとします。 initdbは、de_DE.utf8に設定されたLC_COLLATELC_CTYPEの両方を持つUTF8エンコーディングのde_DE.utf8という名称の照合順序を作成します。 同時に照合順序の名称から.utf8タグを削除した照合順序も作成します。 そのため、de_DEという名前の照合を使用することもできます。 これは手間を省き、名称がエンコーディングに依存しにくいようになります。 それにもかかわらず、照合順序名称の初期値はプラットフォーム依存となることに気をつけてください。

libcが提供するデフォルトの照合順序の集合は、直接オペレーティングシステム内にインストールされたロケールにマップされ、コマンドlocale -aで参照できます。 LC_COLLATELC_CTYPEで違う値を持つlibc照合順序が必要な場合、あるいはデータベースシステムが初期化された後に新しいロケールがインストールされた場合は、新しい照合順序をCREATE COLLATIONコマンドで作成できます。 また、新しいオペレーティングシステムロケールは、pg_import_system_collations()関数でインポートできます。

どのようなデータベース内でも、データベースのエンコーディングを使用している照合順序のみが興味の対象となります。 pg_collation内の他のエントリは無視されます。 よってde_DEといったようなエンコーディング名が省かれた照合順序は、一般的には一意でなくてもデータベース内では一意であるとみなされます。 エンコーディング名が省かれた照合順序を使用することを推奨します。 というのも、データベースのエンコーディングを変更するときに、変えなければならないものを1つ減らせるからです。 しかし、defaultCそしてPOSIX照合順序は、データベースのエンコーディングに関係なく使用可能であることに注意してください。

PostgreSQLは、異なる照合順序オブジェクトは、それらが同じプロパティを持っていても互換性がないものとみなします。 例えば、

SELECT a COLLATE "C" < b COLLATE "POSIX" FROM test1;

は、CPOSIX照合順序が同じ動作であってもエラーとなります。 よってエンコーディング名が省かれた照合順序を混ぜて使用することは推奨されません。

24.2.2.2.2. ICU照合順序

ICUにおいては、すべての可能なロケール名を列挙するのは賢明ではありません。 ICUはロケールの固有の名前付けシステムを使っています。 しかし、実際の個別のロケール名以上の名前を付ける多くの方法があります。 initdbはICUのAPIを使い、照合順序の初期集合を入力するための 個別のロケールの集合を取り出します。 ICUが提供する照合順序は、libcロケールと区別するために、SQL環境において、私的利用拡張-x-icuを追加したBCP 47言語タグ形式の名前で作成されます。

以下は作成されるかもしれない照合順序の例です。

de-x-icu

ドイツ語照合順序、デフォルトの異型

de-AT-x-icu

オーストリアのドイツ語照合順序、デフォルトの異型

(他に、de-DE-x-icuあるいはde-CH-x-icuというのがあります。 しかし、本稿執筆時点では、それらはde-x-icuと同じです。)

und-x-icu (undefined用)

ICUroot照合順序。 言語に依存しない適当なソート順を得るために使用してください。

ある種の(利用頻度が低い)エンコーディングをICUはサポートしません。 データベースエンコーディングがこのようなものであった場合、pg_collation中のICU照合順序は無視されます。 このようなものを使おうとすると、collation "de-x-icu" for encoding "WIN874" does not existというメッセージを伴ったエラーが発生します。

24.2.2.3. 新しい照合順序オブジェクの作成

標準の定義済み照合順序が十分でない場合は、ユーザはSQLコマンドCREATE COLLATIONで照合順序オブジェクトを作成できます。

すべての定義済みオブジェクト同様、標準の定義済み照合順序はpg_catalogスキーマにあります。 これはまた、pg_dumpの保存対象になることを確実にします。

24.2.2.3.1. libc照合順序

以下のようにして新しいlibc照合順序を作成できます。

CREATE COLLATION german (provider = libc, locale = 'de_DE');

コマンド中のlocale句に使用できる正確な値は、オペレーティングシステムに依存します。 Unix系のシステムでは、locale -aコマンドでこのリストを表示できるでしょう。

定義済みのlibc照合順序は、データベースインスタンスが初期化された際に、オペレーティングシステムで定義されたすべての照合順序をすでに含んでいるので、新しいものを手動で作る必要はあまりないでしょう。 そうしたことをする理由があるとすれば、異なる命名規則が必要である(この場合は、24.2.2.3.3も参照してください)、あるいはオペレーティングシステムが更新されて、新しい照合順序の定義が提供されるようになった場合です。(この場合はpg_import_system_collations()も参照してください。)

24.2.2.3.2. ICU照合順序

ICUでは、initdbであらかじめロードされた基本的な言語+国の集合を超えて照合順序をカスタマイズできます。 ユーザが、これらの機能を利用して、ソート処理の挙動が自分の要件に適合する照合順序オブジェクトを定義することを推奨します。 ICUロケールの命名規則に関する情報に関しては、https://unicode-org.github.io/icu/userguide/locale/https://unicode-org.github.io/icu/userguide/collation/api.htmlを参照してください。 利用可能な名前と属性の集合は、ICUのバージョンに依存します。

例を示します。

CREATE COLLATION "de-u-co-phonebk-x-icu" (provider = icu, locale = 'de-u-co-phonebk');
CREATE COLLATION "de-u-co-phonebk-x-icu" (provider = icu, locale = 'de@collation=phonebook');

電話帳照合順形式を伴うドイツ語の照合順序

最初の例では、BCP 47によるlanguage tagを使ったICU照合順序を選択しています。 次の例は伝統的なICU固有のロケール構文を使用しています。 最初の形式を以後使用しますが、これは古いICUのバージョンではサポートされません。

SQL環境における照合順序オブジェクトにはどのような名前でも付けられることに注意してください。 この例では、定義済みの照合順序が使っている名前付け形式に従っているので、結果としてBCP 47に従っています。 しかし、これはユーザ定義照合順序では必須ではありません。

CREATE COLLATION "und-u-co-emoji-x-icu" (provider = icu, locale = 'und-u-co-emoji');
CREATE COLLATION "und-u-co-emoji-x-icu" (provider = icu, locale = '@collation=emoji');

絵文字照合順序型を伴うRoot照合順序。Unicode標準51番による。

伝統的なICUロケール命名規則では、rootロケールは空文字列によって選択されることに注目してください。

CREATE COLLATION latinlast (provider = icu, locale = 'en-u-kr-grek-latn');
CREATE COLLATION latinlast (provider = icu, locale = 'en@colReorder=grek-latn');

ラテン文字の前にギリシャ文字が来るように並べます。(デフォルトではギリシャ文字の前にラテン文字が来ます。)

CREATE COLLATION upperfirst (provider = icu, locale = 'en-u-kf-upper');
CREATE COLLATION upperfirst (provider = icu, locale = 'en@colCaseFirst=upper');

小文字の前に大文字が来るように並べます。(デフォルトでは最初に小文字が来ます。)

CREATE COLLATION special (provider = icu, locale = 'en-u-kf-upper-kr-grek-latn');
CREATE COLLATION special (provider = icu, locale = 'en@colCaseFirst=upper;colReorder=grek-latn');

上記のオプションを組み合わせます。

CREATE COLLATION numeric (provider = icu, locale = 'en-u-kn-true');
CREATE COLLATION numeric (provider = icu, locale = 'en@colNumeric=yes');

数値の値により、数字の列を並べる数値の順序付けです。 例: A-21 < A-123 (自然順ソート(natural sort)としても知られています。)

詳細は、Unicode Technical Standard #35と、BCP 47をご覧ください。 可能な照合順序型(co下位タグ)のリストは、CLDR repositoryで参照できます。

このシステムでは、大文字小文字の違いを無視、あるいはアクセントを無視、あるいはその類似(ksキーを使用)の照合順序を作成できますが、照合順序が真の大文字小文字あるいはアクセントを無視するように動作するためには、CREATE COLLATION内で決定論的でないとして宣言されている必要があることに注意してください。 24.2.2.4を参照してください。 宣言されていない場合、照合順序としては同一に扱われるものの、バイトごとの比較では異なる文字列は、バイト値によってソートされます。

注記

設計上、ICUはロケール名としてどのような文字列も受け入れ、ドキュメントで説明されているフォールバック手続きを使って、ICUが提供するもっとも近いロケールにマッチさせます。 したがって、インストールされたICUが実際にはサポートしていない機能を使って照合順序の指定が構成されていたとしても、直接にはフィードバックはありません。 そういうわけで、照合順序の定義が要件を満たしているかどうかを確認するためのアプリケーションレベルのテストケースを作成することをお勧めします。

24.2.2.3.3. 照合順序の複製

コマンドCREATE COLLATIONは、既存の照合順序から新しい照合順序を作る際にも利用できます。 これは、オペレーティングシステムから独立した照合順序名をアプリケーションで使用可能にしたり、互換性のある名称を作成したり、ICUが提供する照合順序を、よりわかりやすい名称で利用するのに役立ちます。 例を示します。

CREATE COLLATION german FROM "de_DE";
CREATE COLLATION french FROM "fr-x-icu";

24.2.2.4. 非決定論的な照合順序

照合順序は決定論的もしくは非決定論的のどちらかです。 決定論的な照合順序は決定論的な比較を使用します。 つまり、同じバイト列で構成される場合に限り等しい文字列とみなします。 非決定論的な比較は、異なるバイト値で構成される文字列の場合でさえ文字列が等しいと判定するかもしれません。 一般的な状況では、大文字小文字を区別しない比較、アクセントを区別しない比較および異なるUnicode正規化形式による文字列の比較が含まれます。 このような区別しない比較を実際に実装するかは照合順序のプロパイダ次第です。 deterministicフラグはバイト単位の比較を用いて分解されるかどうかのみを決定します。 用語の詳細については、Unicode Technical Standard 10を参照してください。

非決定論的な照合順序を作るためにはCREATE COLLATIONdeterministic = falseプロパティを指定します。 以下に例を示します。

CREATE COLLATION ndcoll (provider = icu, locale = 'und', deterministic = false);

この例では非決定論的な方法で標準のUnicode照合順序を使えます。 具体的には、これは異なる正規形の文字列を正しく比較できるでしょう。 より興味深い例は上述したICUカスタマイズ機能を用いた場合です。 以下に例を示します。

CREATE COLLATION case_insensitive (provider = icu, locale = 'und-u-ks-level2', deterministic = false);
CREATE COLLATION ignore_accents (provider = icu, locale = 'und-u-ks-level1-kc-true', deterministic = false);

すべての標準および事前に定義された照合順序は決定論的であり、すべてのユーザ定義の照合順序はデフォルトで決定論的です。 特にUnicodeの全機能およびその特別な場合を考えた際、非決定論的な照合順序はより多くの正しい振る舞いを与えると同時に、いくつかの欠点もあります。 第一にそれらを使用するとパフォーマンスが低下します。 B-treeは非決定的照合順序を使用したインデックスでは重複排除には使用できないことに特に注意してください。 また、パターンマッチング操作などで非決定論的な照合順序による操作ができないことも避けられません。 したがって、これらは明確に必要とされる場合のみに使用されるべきです。

ヒント

異なるUnicode正規化形式のテキストを処理する場合、非決定論的な照合を使用する代わりにnormalizeおよびis normalized関数もしくは式を使用して文字列の前処理もしくはチェックをするオプションもあります。 それぞれのアプローチで異なるトレードオフがあります。