理系学生日記

おまえはいつまで学生気分なのか

PostgreSQLにおけるCOLLATIONとソート

PostgreSQLを使っているとき、日本語がうまくソートできない。これはいわゆるCOLLATION (照合順序)の設定によります。 色々とこの順番を設定できるため、今回その設定を試してみました。

基礎知識

Postgresqlでは、それぞれのデータタイプはcollationを持っており、その値によってソート順等が制御されます。例えばaよりもbの方が大きいと扱う、というような定義はcollationによるものです。 Postgresqlにおけるこのcollationの定義は、providerによって与えられます。このproviderは複数存在し、よく使われるのはlibcicuです。

A collation definition has a provider that specifies which library supplies the locale data.

PostgreSQL: Documentation: 15: 24.2. Collation Support

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.utf8en_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

PostgreSQL: Documentation: 15: CREATE DATABASE

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'),
('abcde'),
('abcde'),
('12345'),
('12345');

結果は以下に示します。 Cのみが結果が大きく異なり、他の3つは今回のデータセットにおいては同じソート順を示しました。これら3つはいわゆる辞書順、と言えば良いでしょうか。 慣れているせいかCが一番自然に感じます。

en_US.utf8

en=# select * from strs order by 1;
     s
------------
 12345
 12345
 abcde
 abcde
 ABCDE
 ABCDE
 あいうえお
 アイウエオ
 かきくけこ
 カキクケコ
(10 rows)

C

c=# select * from strs order by 1;
     s
------------
 12345
 ABCDE
 abcde
 あいうえお
 かきくけこ
 アイウエオ
 カキクケコ
 12345
 ABCDE
 abcde
(10 rows)

ja-JP-x-icu

jajpxicu=# select * from strs order by 1;
     s
------------
 12345
 12345
 abcde
 abcde
 ABCDE
 ABCDE
 あいうえお
 アイウエオ
 かきくけこ
 カキクケコ
(10 rows)

ja-x-icu

jaxicu=# select * from strs order by 1;
     s
------------
 12345
 12345
 abcde
 abcde
 ABCDE
 ABCDE
 あいうえお
 アイウエオ
 かきくけこ
 カキクケコ