PostgreSQL 10のICUコレーションを使うと日本語を普通にソートでき、更に文字順序までカスタマイズできる

PostgreSQL 10からICU（International Components for Unicode）のロケール/コレーションがサポートされました。

これまでサポートされてきた、libcのja_JPロケールの貧弱な日本語ソート機能とは比べ物にならないくらい高機能な文字比較をサポートしています。日本語や他の言語での照合順序を柔軟に変更できます。

• マトモな日本語ソート順でソートする（かなり重要）
• 数字を後にソートする
• 大文字を先にソートする
• 仮名を先にソートする
• 自然ソートする
• これらをまとめて特別なソート順にする

といったことがPostgreSQL 10から行えます。

基本的な使い方

ja-x-icuはICUサポートが組み込まれている場合、デフォルトで利用可能な日本語ロケールです。ICUロケール（ja-x-icuやund-x-icu）はcreatedbコマンドではまだサポートされていないようです。createdbからは使えません。

[yohgaki@dev pgsql]$ /usr/local/pgsql-10/bin/createdb -p 5410 -h 127.0.0.1 -T template0 -E utf8 --locale ja-x-icu test7
2017-11-29 15:03:11.613 JST [32459] ERROR:  invalid locale name: "ja-x-icu"
2017-11-29 15:03:11.613 JST [32459] STATEMENT:  CREATE DATABASE test7 ENCODING 'utf8' TEMPLATE template0 LC_COLLATE 'ja-x-icu' LC_CTYPE 'ja-x-icu';
createdb: database creation failed: ERROR:  invalid locale name: "ja-x-icu"

CREATE DATABASEでも同様です。

yohgaki@127 test7=# CREATE DATABASE test8 ENCODING 'UTF-8' LC_COLLATE 'ja-x-icu' LC_CTYPE 'ja-x-icu';
2017-11-29 15:06:29.845 JST [7149] ERROR:  invalid locale name: "ja-x-icu"
2017-11-29 15:06:29.845 JST [7149] STATEMENT:  CREATE DATABASE test8 ENCODING 'UTF-8' LC_COLLATE 'ja-x-icu' LC_CTYPE 'ja-x-icu';
ERROR:  42809: invalid locale name: "ja-x-icu"
LOCATION:  createdb, dbcommands.c:368
Time: 0.640 ms

ICUロケール/コレーションを使うにはCREATE TABLE実行時にCOLLATEを使います。

yohgaki@127 test7=# CREATE TABLE t (t text COLLATE "ja-x-icu");
CREATE TABLE
Time: 2.950 ms

テーブルをSELECTする際にCOLLATEで指定することも可能です。

SELECT * FROM mytable ORDER BY mycol COLLATE "ja-x-icu";

ICUロケール/コレーションのソート順を確かめる

全角、半角を混ぜた英数と仮名のテストデータを作成します。

yohgaki@127 test7=# INSERT INTO t (t) VALUES 
('う'), ('ｳ'), ('C'), ('かさ'), ('３'), ('c'), ('ｲ'), ('がく'), ('Ｃ'), ('ウ'),  
('1'), ('ｱ'), ('b'), ('2'), ('Ｂ'), ('B'), ('１'), ('Ａ'),('い'),('ア'), 
('か'), ('A'), ('a'), ('AbC'), ('aBc');
INSERT 0 22
Time: 6.260 ms

ソートなしでSELECTしてみます。

yohgaki@127 test7=# SELECT * FROM t;
  t   
------
 う
 ｳ
 C
 かさ
 ３
 c
 ｲ
 がく
 Ｃ
 ウ
 1
 ｱ
 b
 2
 Ｂ
 B
 １
 Ａ
 い
 ア
 か
 A
 a
 AbC
 aBc
(25 rows)

Time: 0.392 ms

ORDER BYを付けてSELECTします。ja-x-icu コレーションだと概ね一般に期待されるような順序でソートされています。

yohgaki@127 test7=# SELECT * FROM t ORDER BY t;
  t   
------
 1
 １
 2
 ３
 a
 A
 Ａ
 aBc
 AbC
 b
 B
 Ｂ
 c
 C
 Ｃ
 ア
 ｱ
 い
 ｲ
 う
 ウ
 ｳ
 か
 がく
 かさ
(25 rows)

Time: 0.820 ms

libcのja_JP（古いPostgreSQLで利用可能なロケール。このブログを書くために使っているOSはFedora 26です。libcロケールのソート順はOSによって異る）の場合は次のように、人間が期待する順番通りにはソートされません。

yohgaki@127 test7=# SELECT * FROM t ORDER BY t COLLATE "ja_JP";
  t   
------
 1
 2
 A
 AbC
 B
 C
 a
 aBc
 b
 c
 ｱ
 ｲ
 ｳ
 １
 ３
 Ａ
 Ｂ
 Ｃ
 い
 う
 か
 かさ
 がく
 ア
 ウ
(25 rows)

Time: 0.308 ms

デフォルトで利用できる”ja-x-icu“（und-x-icu : 言語未定義のロケールもja-x-icuと同様にソートする）だけでも日本語ユーザーにとって画期的なソート結果ですが、ICUロケールの場合は更に色々な設定が可能です。

ICUのロケール/コレーション拡張を利用する

ICUはBCP 47というルールに従ってロケールを定義できます。拡張ルールは”u“を利用して定義します。例えば、PostgreSQLのマニュアルページでも紹介されている

de-u-co-phonebk ロケール/コレーションはICUで定義されている、ドイツ語の電話帳順ソートです。de-u-co-phonebkを使うにはPostgreSQLでロケール／コレーションとして使えるように定義する必要があります。

PostgreSQL 10からICUプロバイダー（provider）が使えます。libcを使う場合はicuの代わりにlibcを指定できます。

CREATE COLLATION de-u-co-phonebk-x-icu (provider = icu, locale = ‘de-u-co-phonebk‘);

実際にこのコレーションを使ってみます。ソート順序が異ることが判ります。

yohgaki@127 test7=# CREATE TABLE names (name text);
CREATE TABLE
Time: 2.496 ms

yohgaki@127 test7=# INSERT INTO names
test7-# VALUES ('Göbel'), ('Goethe'), ('Goldmann'), ('Göthe'), ('Götz');
INSERT 0 5
Time: 1.254 ms

yohgaki@127 test7=# SELECT name FROM names ORDER BY name;
name 
----------
Goethe
Goldmann
Göbel
Göthe
Götz
(5 rows)

Time: 0.533 ms
yohgaki@127 test7=# SELECT name FROM names ORDER BY name 
COLLATE "de-u-co-phonebk-x-icu";
name 
----------
Göbel
Goethe
Göthe
Götz
Goldmann
(5 rows)

Time: 0.578 ms

数字が英字の前なんて許せない！

数字が優先されることが許せない、と感じていた方には朗報です。簡単に順番を変えれます。拡張ロケール／コレーションはCRATE COLLATIONでコレーションと作ってからでないと使えないことを思い出してください。作れば使えるようになります。

CREATE COLLATION digitslast (provider = icu, locale = ‘ja-u-kr-latn-digit‘);

​yohgaki@127 test7=# select * from t order by t collate "digitslast";
  t   
------
 a
 A
 Ａ
 aBc
 AbC
 b
 B
 Ｂ
 c
 C
 Ｃ
 1
 １
 2
 ３
 ア
 ｱ
 い
 ｲ
 う
 ウ
 ｳ
 か
 がく
 かさ
(25 rows)

Time: 0.490 ms

数字が後にソートされています。

小文字の後に大文字なんて許せない！

個人的には小文字の後に大文字アルファベットがソートされていると違和感が大きいです。これも直せます。

CREATE COLLATION upperfirst (provider = icu, locale = ‘ja-u-kf-upper‘);

yohgaki@127 test7=# select * from t order by t collate "upperfirst";
  t   
------
 1
 １
 2
 ３
 A
 Ａ
 a
 AbC
 aBc
 B
 Ｂ
 b
 C
 Ｃ
 c
 ア
 ｱ
 い
 ｲ
 う
 ウ
 ｳ
 か
 がく
 かさ
(25 rows)

Time: 0.526 ms

仮名が英数の後なんて許せない！

これも順番を変更できます。

CREATE COLLATION kanafirst (provider = icu, locale = ‘ja-u-kr-hrkt-latn-digit‘);

yohgaki@127 test7=# select * from t order by t collate "kanafirst";
  t   
------
 ア
 ｱ
 い
 ｲ
 う
 ウ
 ｳ
 か
 がく
 かさ
 a
 A
 Ａ
 aBc
 AbC
 b
 B
 Ｂ
 c
 C
 Ｃ
 1
 １
 2
 ３
(25 rows)

Time: 0.373 ms

前の3つを組み合わせる！

仮名を優先ソート、大文字を優先ソート、数字を英字の後にする、をまとめる事も可能です。

CREATE COLLATION special (provider = icu, locale = ‘ja-u-kr-hrkt-latn-digit-kf-upper‘);

yohgaki@127 test7=# select * from t order by t collate "special";
  t   
------
 ア
 ｱ
 い
 ｲ
 う
 ウ
 ｳ
 か
 がく
 かさ
 A
 Ａ
 a
 AbC
 aBc
 B
 Ｂ
 b
 C
 Ｃ
 c
 1
 １
 2
 ３
(25 rows)

Time: 0.298 ms

いわゆる自然ソートでソートしたい！

CREATE COLLATION numeric (provider = icu, locale = ‘ja-u-kn-true‘);

サンプルデータが自然ソート（Natural Sort）向きではないのでソート実行例は省略しますが、自然ソートできます。

ソート例：最近のファイルビューワーのように人が期待する数値順序になる

    img1.png
    img2.png
    img10.png
    img12.png

絵文字のソート順がバラバラなのが許せない！

“u-co-emoji”を使えば、絵文字がグループ毎にソートされます。（Uniocde絵文字をサポートしない環境では絵文字は表示されません）

CREATE COLLATION emoji (provider = icu, locale = ‘ja-u-co-emoji‘);

yohgaki@127 test7=# SELECT chr(x) FROM generate_series(x'1F634'::int, x'1F644'::int) 
AS _(x) ORDER BY chr(x) COLLATE "emoji";
chr 
-----
🙂
😶
🙄
😴
🙃
🙁
😵
😷
😺
😸
😹
😻
😼
😽
🙀
😿
😾
(17 rows)

Time: 0.610 ms

バイナリでソートすると以下のようにバラバラです。

yohgaki@127 test7=# SELECT chr(x) FROM generate_series(x'1F634'::int, x'1F644'::int) 
AS _(x) ORDER BY chr(x) COLLATE "C";
 chr 
-----
 😴
 😵
 😶
 😷
 😸
 😹
 😺
 😻
 😼
 😽
 😾
 😿
 🙀
 🙁
 🙂
 🙃
 🙄
(17 rows)

Time: 1.337 ms
yohgaki@127 test7=# 

まとめ

このソート順の為だけにPostgreSQL 10にアップグレードしたくなった！という方も少なくないのでは？！

Unicode Technical Standard #35 と BCP 47 を参照すると機能の詳細が解ります。

CLDR repository と ICU Locale Explorer を参照すると記号のようなロケール文字の意味や定義が解ります。

ソート順序はICUのバージョンによって変わる場合があります。このため、ICUバージョンが異るとエラーが発生することがあります。

WARNING:  collation "xx-x-icu" has version mismatch
DETAIL:  The collation in the database was created using version 1.2.3.4, but the operating system provides version 2.3.4.5.
HINT:  Rebuild all objects affected by this collation and run ALTER COLLATION pg_catalog."xx-x-icu" REFRESH VERSION, or build PostgreSQL with the right library version.

この場合、適切なバージョンのICUとリンクしたPostgreSQLを使うか、エラーメッセージに記載された通りRERESH VERSIONすると直ります。

ICUロケール文字列にタイポなどがあってもほとんどの場合エラーになりません。思った通りのソートでない場合は先ずタイポを疑いましょう。タイポには注意しましょう。

コレーション（照合順序）を利用するとソート順を定義したテーブルを参照しながらソートします。バイナリソートと比べると遅くなります。どの程度遅くなるかはデータとソート方法（コレーション）によります。既存のデータベースで利用する場合、ベンチマークすることをお勧めします。