照合順序機能は、ソート順番と列ごともしくは操作ごとのデータの文字区別の振る舞いを指定することを可能にします。
これにより、作成後のデータベースのLC_COLLATE
とLC_CTYPE
の設定を変更できない制限が緩和されます。
概念的に照合可能なデータ型のそれぞれの式は、照合順序を保持しています
(組み込みの照合可能なデータ型はtext
、varchar
、 char
です。
ユーザ定義の基本型は照合可能とマーキングできます。もちろん照合可能なデータ型上のドメインは照合可能となります)。
もし、式が列参照である場合は、式の照合順序は列の定義された照合順序となります。
もし、式が定数である場合は、照合順序は定数のデータ型のデフォルトの照合順序となります。
より複雑な式の照合順序は、下記に示すように、その入力の照合順序から引き出されます。
式の照合順序は、「default」照合順序となります。これはデータベースに対して定義されたロケール設定を意味しています。 式の照合順序は非決定となることもあります。そのような場合に、照合順序が必要となるような順序操作や他の操作は失敗するでしょう。
データベースシステムが並び変えや文字区別を行う場合、データベースは入力の照合順序を使用します。
これは、たとえばORDER BY
句や<
演算子や関数を使用する際に発生します。
ORDER BY
句に適用する照合順序は、単純にソートキーの照合順序です。
関数や演算子の呼び出しに対して適用される照合順序は、以下に述べるように引数により決まります。
比較演算子に加えて、照合順序はlower
、upper
、initcap
といった小文字と大文字を変換する関数やパターンマッチングの演算子、to_char
関連の関数で考慮されています。
関数や演算子の呼び出しに対して、引数の照合順序検査により得られた照合順序は実行時に特定の操作を行うために使用されます。 もし関数や演算子の呼び出しの結果が照合順序可能なデータ型であった場合、照合順序は関数もしくは演算子式の定義済みの照合順序として 解析時にも試用されます。このとき照合順序の知識が必要となるような囲み式があります。
式の照合順序の導出は暗黙でも明示的にでも可能です。
この区別は、複数の異なる照合順序が式中に現れるときに照合順序がどのように組み合わされるか、に影響を与えます。
明示的な照合順序の導出は、COLLATE
句が使用されたときに発生します。
他の全ての照合順序は暗黙となります。例えば関数呼び出しの中では、次の規則が用いられます。
入力式に明示的な照合順序の導出がある場合、入力式の中の明示的に導出された全ての照合順序が同一でなくてはなりません。 そうでない場合はエラーが発生します。もし明示的に導出された照合順序がある場合は、それは照合順序の組み合わせの結果となります。
そうでない場合は、全ての入力式は同一の暗黙の照合順序の導出またはデフォルトの照合順序を持たなくてはなりません。 もしデフォルトではない照合順序がある場合は、それは照合順序の組み合わせの結果となります。 もしそうでない場合は、結果はデフォルトの照合順序となります。
入力式内でデフォルトではない暗黙の照合順序が衝突している場合、決定不能な照合順序であるとみなされます。 これは、もし呼び出された特定の関数が適用するべき照合順序を知っておく必要がないかぎりエラーの条件ではありません。 もし知っておく必要がある場合は、実行時にエラーとなります。
例えば、このテーブル定義を考えてみます。
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;
パーサはどの照合順序を適用するか決定できません。というのもa
とb
列は暗黙の衝突する照合順序を持つためです。
<
演算子がどちらの照合順序を使用するか知る必要があるため、エラーとなります。
このエラーは、どちらかの入力式に明示的な照合順序の指定を付加することで解決できます。
つまり、以下のようになります。
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";
照合順序は、SQL名称を、オペレーティングシステム中にインストールされたライブラリによって提供されるロケールにマッピングするSQLスキーマオブジェクトです。
照合順序の定義には、ロケールデータを提供するライブラリを指定するプロバイダ(provider)が含まれます。
標準プロバイダの一つはlibc
で、オペレーティングシステムのCライブラリが提供するロケールを使用します。
オペレーティングシステムが提供するほとんどのツールが、このロケールを使用します。
他のプロバイダとしてはicu
があり、外部のICUライブラリを使います。
ICUロケールは、PostgreSQLがビルドされた際にICUサポートが設定されていた場合にのみ利用可能です。
libc
が提供する照合順序は、setlocale()
システムライブラリの呼び出しが許可するLC_COLLATE
とLC_CTYPE
の組み合わせ設定にマッピングします。
(名称から推測されるように、照合順序の主な目的はソート順序を制御するLC_COLLATE
を設定することです。
しかし実際にはLC_CTYPE
の設定をLC_COLLATE
と異なるようにする必要はほとんどありません。
そのため、式ごとにLC_CTYPE
を設定するような別の機構を作成するより、これらの設定を収集する方が、より便利です。)
また、libc
の照合順序は文字エンコーディングと結びついています(24.3を参照してください)。
同一の照合順序名称が異なるエンコーディングに対して存在しています。
icu
が提供する照合順序オブジェクトは、ICUライブラリが提供する照合順序機能(collator)にマップします。
ICUは「collate」と「ctype」を別々に設定する機能を提供しないので、それら常に同じものになります。
また、ICUの照合順序はエンコーディングからは独立しています。
ですから、データベース中のある名前のICU照合順序は、常にただひとつだけです。
すべてのプラットフォーム上でdefault
、C
そしてPOSIX
という名称の照合順序が
利用できます。
オペレーティングシステムによっては追加の照合順序が利用可能な場合もあります。
default
照合順序は、データベース作成時にLC_COLLATE
値とLC_CTYPE
値を
選択します。C
とPOSIX
照合順序は共に「traditional C」の動作を指定します。
これはASCII文字の「A
」から「Z
」を文字として扱い、ソート順は厳密な文字コードのバイト値によります。
《機械翻訳》C
ロケールとPOSIX
ロケールは、データベースエンコーディングによって動作が異なる場合があります。
《機械翻訳》さらに、次の2つのSQL標準照合順序名を使用できます。
unicode
この照合順序は、Unicode照合順序アルゴリズムとデフォルトUnicode照合順序要素テーブルを使用してソートします。
すべてのエンコーディングで使用できます。
この照合順序を使用するには、ICU空港が必要です。
(この照合順序は、ICU空港ロケールと同じ動作をします。
und-x-icu
(「undefined」用)を参照してください。)
ucs_basic
《機械翻訳》この照合順序はUnicodeコードポイントでソートされます。
エンコーディングUTF8
でのみ使用できます。
(この照合順序は、UTF8
エンコーディングのlibcロケール指定C
と同じ動作をします。)
オペレーティングシステムが単一のプログラム内(newlocale
や関連する関数)で複数のロケールを使用することをサポートしているか、ICUサポートが組み込み済みの場合、データベースクラスタが初期化されるとinitdb
は、オペレーティングシステム上で見つけた全てのロケールに基づく照合順序をシステムカタログのpg_collation
に書き込みます。
現在利用可能なロケールを調べるには、SELECT * FROM pg_collation
という問合せを使うか、psql内で\dOS+
コマンドを使用します。
例えば、オペレーティングシステムがde_DE.utf8
という名称のロケールを提供するとします。
initdb
は、de_DE.utf8
に設定されたLC_COLLATE
とLC_CTYPE
の両方を持つUTF8
エンコーディングのde_DE.utf8
という名称の照合順序を作成します。
同時に照合順序の名称から.utf8
タグを削除した照合順序も作成します。
そのため、de_DE
という名前の照合を使用することもできます。
これは手間を省き、名称がエンコーディングに依存しにくいようになります。
それにもかかわらず、照合順序名称の初期値はプラットフォーム依存となることに気をつけてください。
libc
が提供するデフォルトの照合順序の集合は、直接オペレーティングシステム内にインストールされたロケールにマップされ、コマンドlocale -a
で参照できます。
LC_COLLATE
とLC_CTYPE
で違う値を持つlibc
照合順序が必要な場合、あるいはデータベースシステムが初期化された後に新しいロケールがインストールされた場合は、新しい照合順序をCREATE COLLATIONコマンドで作成できます。
また、新しいオペレーティングシステムロケールは、pg_import_system_collations()
関数でインポートできます。
どのようなデータベース内でも、データベースのエンコーディングを使用している照合順序のみが興味の対象となります。
pg_collation
内の他のエントリは無視されます。
よってde_DE
といったようなエンコーディング名が省かれた照合順序は、一般的には一意でなくてもデータベース内では一意であるとみなされます。
エンコーディング名が省かれた照合順序を使用することを推奨します。
というのも、データベースのエンコーディングを変更するときに、変えなければならないものを1つ減らせるからです。
しかし、default
、C
そしてPOSIX
照合順序は、データベースのエンコーディングに関係なく使用可能であることに注意してください。
PostgreSQLは、異なる照合順序オブジェクトは、それらが同じプロパティを持っていても互換性がないものとみなします。 例えば、
SELECT a COLLATE "C" < b COLLATE "POSIX" FROM test1;
は、C
とPOSIX
照合順序が同じ動作であってもエラーとなります。
よってエンコーディング名が省かれた照合順序を混ぜて使用することは推奨されません。
ICUにおいては、すべての可能なロケール名を列挙するのは賢明ではありません。
ICUはロケールの固有の名前付けシステムを使っています。
しかし、実際の個別のロケール名以上の名前を付ける多くの方法があります。
initdb
はICUのAPIを使い、照合順序の初期集合を入力するための
個別のロケールの集合を取り出します。
ICUが提供する照合順序は、libcロケールと区別するために、SQL環境において、「私的利用」拡張-x-icu
を追加したBCP 47言語タグ形式の名前で作成されます。
以下は作成されるかもしれない照合順序の例です。
ある種の(利用頻度が低い)エンコーディングをICUはサポートしません。
データベースエンコーディングがこのようなものであった場合、pg_collation
中のICU照合順序は無視されます。
このようなものを使おうとすると、「collation "de-x-icu" for encoding "WIN874" does not exist」というメッセージを伴ったエラーが発生します。
標準の定義済み照合順序が十分でない場合は、ユーザはSQLコマンドCREATE COLLATIONで照合順序オブジェクトを作成できます。
すべての定義済みオブジェクト同様、標準の定義済み照合順序はpg_catalog
スキーマにあります。
これはまた、pg_dump
の保存対象になることを確実にします。
以下のようにして新しいlibc照合順序を作成できます。
CREATE COLLATION german (provider = libc, locale = 'de_DE');
コマンド中のlocale
句に使用できる正確な値は、オペレーティングシステムに依存します。
Unix系のシステムでは、locale -a
コマンドでこのリストを表示できるでしょう。
定義済みのlibc照合順序は、データベースインスタンスが初期化された際に、オペレーティングシステムで定義されたすべての照合順序をすでに含んでいるので、新しいものを手動で作る必要はあまりないでしょう。
そうしたことをする理由があるとすれば、異なる命名規則が必要である(この場合は、24.2.2.3.3も参照してください)、あるいはオペレーティングシステムが更新されて、新しい照合順序の定義が提供されるようになった場合です。(この場合はpg_import_system_collations()
も参照してください。)
《機械翻訳》照合順序照合は次のように作成できます。
CREATE COLLATION german (provider = icu, locale = 'de-DE');
ロケールロケールはBCP 47言語タグとして指定されますが、ほとんどのlibc-スタイルロケール名も受け入れることができます。 可能であれば、libc-スタイルロケール名は言語タグに変換されます。
《機械翻訳》新しいカスタマイズ照合では、照合順序タグに照合順序属性を含めることで、言語の動作を広範囲にICU化できます。 詳細と例については、24.2.3を参照してください。
コマンドCREATE COLLATIONは、既存の照合順序から新しい照合順序を作る際にも利用できます。 これは、オペレーティングシステムから独立した照合順序名をアプリケーションで使用可能にしたり、互換性のある名称を作成したり、ICUが提供する照合順序を、よりわかりやすい名称で利用するのに役立ちます。 例を示します。
CREATE COLLATION german FROM "de_DE"; CREATE COLLATION french FROM "fr-x-icu";
照合順序は決定論的もしくは非決定論的のどちらかです。 決定論的な照合順序は決定論的な比較を使用します。 つまり、同じバイト列で構成される場合に限り等しい文字列とみなします。 非決定論的な比較は、異なるバイト値で構成される文字列の場合でさえ文字列が等しいと判定するかもしれません。 一般的な状況では、大文字小文字を区別しない比較、アクセントを区別しない比較および異なるUnicode正規化形式による文字列の比較が含まれます。 このような区別しない比較を実際に実装するかは照合順序のプロパイダ次第です。 deterministicフラグはバイト単位の比較を用いて分解されるかどうかのみを決定します。 用語の詳細については、Unicode Technical Standard 10を参照してください。
非決定論的な照合順序を作るためにはCREATE COLLATION
にdeterministic = 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
関数もしくは式を使用して文字列の前処理もしくはチェックをするオプションもあります。
それぞれのアプローチで異なるトレードオフがあります。
《機械翻訳》ICUでは、コントロール設定を使用して新しい照合を照合順序タグの照合順序として定義することで、パートを介した広範なニーズ動作が可能になります。 これらの設定では、様々なに適合するように照合順序オーダーを変更できます。 インスタンスの場合:
-- ignore differences in accents and case CREATE COLLATION ignore_accent_case (provider = icu, deterministic = false, locale = 'und-u-ks-level1'); SELECT 'Å' = 'A' COLLATE ignore_accent_case; -- true SELECT 'z' = 'Z' COLLATE ignore_accent_case; -- true -- upper case letters sort before lower case. CREATE COLLATION upper_first (provider = icu, locale = 'und-u-kf-upper'); SELECT 'B' < 'b' COLLATE upper_first; -- true -- treat digits numerically and ignore punctuation CREATE COLLATION num_ignore_punct (provider = icu, deterministic = false, locale = 'und-u-ka-shifted-kn'); SELECT 'id-45' < 'id-123' COLLATE num_ignore_punct; -- true SELECT 'w;x*y-z' = 'wxyz' COLLATE num_ignore_punct; -- true
使用可能なの多くは24.2.3.2で説明されています。 詳細は24.2.3.5を参照してください。
《機械翻訳》ICUでの2つの文字列の比較(照合)は、テキストの特徴が「レベル」にグループ化される多段階プロセスによって決定されます。 各レベルの処理は、照合設定によって制御されます。 高いレベルは、より細かいテキストの特徴に対応します。
《機械翻訳》表 24.1は、特定の機能での等価性を決定する際に、どのテキストレベルの差異が重要であると考えられるかを示します。
Unicode文字U+2063
は非表示のセパレータであり、表に示すように、比較より小さいのすべてのレベルで無視されますidentic
。
表24.1 ICU Collation Levels
Level | Description | 'f' = 'f' | 'ab' = U&'a\2063b' | 'x-y' = 'x_y' | 'g' = 'G' | 'n' = 'ñ' | 'y' = 'z' |
---|---|---|---|---|---|---|---|
level1 | Base Character | true | true | true | true | true | false |
level2 | Accents | true | true | true | true | false | false |
level3 | Case/Variants | true | true | true | false | false | false |
level4 | Punctuation | true | true | false | false | false | false |
identic | All | true | false | false | false | false | false |
《機械翻訳》すべてのレベルで、正規化オフが満席であっても基本正規化が行われます。
例の場合、'á'
はコードポイントU&'\0061\0301'
または単一コードポイントU&'\00E1'
で構成され、これらのシーケンスはidentic
レベルでも等しいとみなされます。
コードポイント表現形式内の差を個別として扱うには、deterministic
を真
に設定して作成された照合順序を使用します。
CREATE COLLATION level3 (provider = icu, deterministic = false, locale = 'und-u-ka-shifted-ks-level3'); CREATE COLLATION level4 (provider = icu, deterministic = false, locale = 'und-u-ka-shifted-ks-level4'); CREATE COLLATION identic (provider = icu, deterministic = false, locale = 'und-u-ka-shifted-ks-identic'); -- invisible separator ignored at all levels except identic SELECT 'ab' = U&'a\2063b' COLLATE level4; -- true SELECT 'ab' = U&'a\2063b' COLLATE identic; -- false -- punctuation ignored at level3 but not at level 4 SELECT 'x-y' = 'x_y' COLLATE level3; -- true SELECT 'x-y' = 'x_y' COLLATE level4; -- false
《機械翻訳》表 24.2には、利用可能な照合順序設定が表示されます。 これは、言語タグのパートとして照合順序をカスタマイズするために使用できます。
表24.2 ICU Collation Settings
Key | Values | Default | Description |
---|---|---|---|
co | emoji , phonebk , standard , ... | standard | Collation type. See 24.2.3.5 for additional options and details. |
ka | noignore , shifted | noignore |
If set to shifted , causes some characters
(e.g. punctuation or space) to be ignored in comparison. Key
ks must be set to level3 or
lower to take effect. Set key kv to control which
character classes are ignored.
|
kb | true , false | false |
Backwards comparison for the level 2 differences. For example,
locale und-u-kb sorts 'àe'
before 'aé' .
|
kc | true , false | false |
《機械翻訳》ケースを、レベルと他のアクセント3フィーチャの間にある「レベル2.5」に分割します。
《機械翻訳》 |
kf |
upper , lower ,
false
| false |
If set to upper , upper case sorts before lower
case. If set to lower , lower case sorts before
upper case. If set to false , the sort depends on
the rules of the locale.
|
kn | true , false | false |
If set to true , numbers within a string are
treated as a single numeric value rather than a sequence of
digits. For example, 'id-45' sorts before
'id-123' .
|
kk | true , false | false |
《機械翻訳》完全正規化を有効にします。
パフォーマンスに影響する場合があります。
基本正規化は、
《機械翻訳》正規化マルチプルが1つの文字に適用される場合など、場合によっては完全なアクセントが重要になります。
例の場合、コードポイントシーケンス |
kr |
space , punct ,
symbol , currency ,
digit , script-id
|
《機械翻訳》1つ以上の有効な値、または任意のBCP 47
《機械翻訳》文字クラスの順序付けを再定義します。
リストソート前の前のクラスに属する文字は、リストの後のクラスに属する文字です。
インスタンスの場合、値 | |
ks | level1 , level2 , level3 , level4 , identic | level3 |
《機械翻訳》等価性を判定するときの感度(または「強さ」)。level1 が最も感度が低く、identic が最も感度が高い。
詳細は表 24.1を参照してください。
|
kv |
space , punct ,
symbol , currency
| punct |
《機械翻訳》レベル 3 での比較で無視される文字のクラス。
後の値に設定すると、前の値も含まれます。たとえば、symbol は、無視する文字にpunct とspace も含みます。
キー ka は、shifted に設定し、キー ks は、level3 以下に設定して、有効にする必要があります。
|
《機械翻訳》デフォルトはロケールによって異なります。 上記のテーブルは完全なものではありません。 追加のオプションと詳細については24.2.3.5を参照してください。
CREATE COLLATION "de-u-co-phonebk-x-icu" (provider = icu, locale = 'de-u-co-phonebk');
#電話帳照合順形式を伴うドイツ語の照合順序
CREATE COLLATION "und-u-co-emoji-x-icu" (provider = icu, locale = 'und-u-co-emoji');
#絵文字照合順序型を伴うRoot照合順序。Unicode標準51番による。
CREATE COLLATION latinlast (provider = icu, locale = 'en-u-kr-grek-latn');
#ラテン文字の前にギリシャ文字が来るように並べます。(デフォルトではギリシャ文字の前にラテン文字が来ます。)
CREATE COLLATION upperfirst (provider = icu, locale = 'en-u-kf-upper');
#《マッチ度[81.720430]》小文字の前に大文字が来るように並べます。(デフォルトでは最初に小文字が来ます。) 《機械翻訳》小文字の前に大文字が来るように並べます。 (デフォルトでは最初に小文字が来ます。)
CREATE COLLATION special (provider = icu, locale = 'en-u-kf-upper-kr-grek-latn');
#上記のオプションを組み合わせます。
《機械翻訳》上記のオプション設定によって提供される照合順序が十分でない場合は、照合順序要素のオーダーを適合化規則を使用して変更できます。 適合化規則の構文はで詳しく説明されています。
《機械翻訳》この小さな例は、ルートロケールをベースにした照合順序を作成し、ルールを調整します。
CREATE COLLATION custom (provider = icu, locale = 'und', rules = '&V << w <<< W');
このルールでは、文字「W」は「V」の後にソートされますが、アクセントと同様に二次的な差として扱われます。 このようなルールは、一部の言語のロケール定義に含まれています。 (もちろん、ロケール定義がすでに目的のルールを包含している場合は、明示的に再指定する必要はありません。)
《機械翻訳》ここに、より複雑な例があります。
次のステートメントは、EBCDIC記名的のオーダーにUS-ASCII文字をソートするためのルールを使用して、照合順序エンコーディングebcdic
を設定します。
CREATE COLLATION ebcdic (provider = icu, locale = 'und', rules = $$ & ' ' < '.' < '<' < '(' < '+' < \| < '&' < '!' < '$' < '*' < ')' < ';' < '-' < '/' < ',' < '%' < '_' < '>' < '?' < '`' < ':' < '#' < '@' < \' < '=' < '"' <*a-r < '~' <*s-z < '^' < '[' < ']' < '{' <*A-I < '}' <*J-R < '\' <*S-Z <*0-9 $$); SELECT c FROM (VALUES ('a'), ('b'), ('A'), ('B'), ('1'), ('2'), ('!'), ('^')) AS x(c) ORDER BY c COLLATE ebcdic; c --- ! a b ^ A B 1 2