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ロケール文字列にタイポなどがあってもほとんどの場合エラーになりません。思った通りのソートでない場合は先ずタイポを疑いましょう。タイポには注意しましょう。
コレーション（照合順序）を利用するとソート順を定義したテーブルを参照しながらソートします。バイナリソートと比べると遅くなります。どの程度遅くなるかはデータとソート方法（コレーション）によります。既存のデータベースで利用する場合、ベンチマークすることをお勧めします。
