PostgreSQLを使っているとき、日本語がうまくソートできない。これはいわゆるCOLLATION (照合順序)の設定によります。 色々とこの順番を設定できるため、今回その設定を試してみました。
基礎知識
Postgresqlでは、それぞれのデータタイプはcollationを持っており、その値によってソート順等が制御されます。例えばaよりもbの方が大きいと扱う、というような定義はcollationによるものです。
Postgresqlにおけるこのcollationの定義は、providerによって与えられます。このproviderは複数存在し、よく使われるのはlibcやicuです。
A collation definition has a provider that specifies which library supplies the locale data.
providerとしてlibcを使用する場合、環境変数LC_COLLATEおよびLC_CTYPEにて実際のcollationが定義されます。一方で、icuを使用する場合は、当該ライブラリによって定義されるcollatorの名称によってcollationが定義されます。
Postgresqlで利用できるcollationの種類は、メタコマンド\dOS+によって参照できます。
List of collations Schema | Name | Collate | Ctype | Provider | Deterministic? | Description ------------+------------------------+------------+------------+----------+----------------+---------------------------------------------- pg_catalog | C | C | C | libc | yes | standard C collation pg_catalog | C.UTF-8 | C.UTF-8 | C.UTF-8 | libc | yes | pg_catalog | POSIX | POSIX | POSIX | libc | yes | standard POSIX collation pg_catalog | af-NA-x-icu | | | icu | yes | Afrikaans (Namibia) (略)
何も考えないでデータベースを作るとどうなるか
DockerHubにあるpostgres:15.3イメージを何も考えずに使うとCOLLATIONはどうなるのでしょうか。
このkiririmodeというデータベースを作成した結果は以下で、providerはlibc、Collationとしてはen_US.utf8、en_US.utf8が選択されています。
kiririmode=> \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
------------+----------+----------+------------+------------+-----------------------
kiririmode | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
これは、template[01]から引き継いだ設定です。
デフォルトでは、databaseを作成するとtemplate1から設定を持ってくるようになっています。
By default, the new database will be created by cloning the standard system database template1
Collationの設定とソート順の確認
本エントリでは、以下のCollationをdatabaseに設定してみて、その結果を見ることにします。
| provider | LC_COLLATE |
|---|---|
| libc | en_US.utf8 |
| libc | C |
| icu | ja-JP-x-icu |
| icu | ja-x-icu |
具体的には、以下のようなSQLによって、異なるCollationを持つdatabaseをそれぞれ構築します。
CREATE DATABASE en WITH ENCODING=utf8 LC_COLLATE='en_US.utf8' TEMPLATE=template0; CREATE DATABASE c WITH ENCODING=utf8 LC_COLLATE='C' TEMPLATE=template0; CREATE DATABASE jajpxicu WITH ENCODING=utf8 LOCALE_PROVIDER=icu ICU_LOCALE='ja-JP-x-icu' TEMPLATE=template0; CREATE DATABASE jaxicu WITH ENCODING=utf8 LOCALE_PROVIDER=icu ICU_LOCALE='ja-x-icu' TEMPLATE=template0;
各databaseには、それぞれ以下のデータを入れました。
create table strs ( s varchar(10) ); insert into strs(s) values ('あいうえお'), ('かきくけこ'), ('アイウエオ'), ('カキクケコ'), ('ABCDE'), ('ＡＢＣＤＥ'), ('abcde'), ('ａｂｃｄｅ'), ('12345'), ('１２３４５');
結果は以下に示します。
Cのみが結果が大きく異なり、他の３つは今回のデータセットにおいては同じソート順を示しました。これら３つはいわゆる辞書順、と言えば良いでしょうか。
慣れているせいかCが一番自然に感じます。
en_US.utf8
en=# select * from strs order by 1; s ------------ 12345 １２３４５ abcde ａｂｃｄｅ ABCDE ＡＢＣＤＥ あいうえお アイウエオ かきくけこ カキクケコ (10 rows)
C
c=# select * from strs order by 1; s ------------ 12345 ABCDE abcde あいうえお かきくけこ アイウエオ カキクケコ １２３４５ ＡＢＣＤＥ ａｂｃｄｅ (10 rows)
ja-JP-x-icu
jajpxicu=# select * from strs order by 1; s ------------ 12345 １２３４５ abcde ａｂｃｄｅ ABCDE ＡＢＣＤＥ あいうえお アイウエオ かきくけこ カキクケコ (10 rows)
ja-x-icu
jaxicu=# select * from strs order by 1; s ------------ 12345 １２３４５ abcde ａｂｃｄｅ ABCDE ＡＢＣＤＥ あいうえお アイウエオ かきくけこ カキクケコ
