Help us understand the problem. What is going on with this article?

PostgreSQLのICUコレーションを使った日本語辞書順ソート

PostgreSQLのICUコレーションを使った日本語辞書順ソート

元になった情報は以下のURLです。

PostgreSQL 10のICUコレーションを使うと日本語を普通にソートでき、更に文字順序までカスタマイズできる
https://blog.ohgaki.net/postgresql-10-icu-locale-collation-enables-natural-japanese-sorting

SQLでの文字列比較、ソート順には「照合順序(Collation)というのが絡みます。

代表的なのがMSのSQLServerで日本語版をインストールした場合
デフォルトの照合順序は Japanese_CI_AS です。
CaseIgnore(大文字小文字を区別しない)

MySQLにも照合順序があり最近のように文字コードがutf8,utf8mb4などの環境だと
照合順序は
utf8_general_ci
utf_unicode_ci

例えばテーブルTBL(txt varchar(10)で

txt
---
a
A
b
B
c
C
は
ハ

のようなデータに対して

SELECT txt FROM TBL ORER BY txt

とすると結果は

txt
---
a
A
b
B
c
C
は
ハ

いい感じに見えますが、ここで

INSERT INTO t VALUES ('A');
INSERT INTO t VALUES ('a');
INSERT INTO t VALUES ('ハ');
INSERT INTO t VALUES ('は');

として再度SELECT txt FROM t ORDER BY txt

とすると

txt
---
a
A
A
a
b
B
c
C
は
ハ
ハ
は

とかに。
アルファベットの大文字・小文字、ひらがな・カタカナを区別しない。
注意が必要なのはこれらはソート順だけでなく文字比較にも影響があること

SELECT txt FROM t WHERE txt = 'a' OR txt = 'は'

txt
---
a
A
A
a
は
ハ

結局大文字と小文字、ひらがな・カタカナが同じものとして扱われます。
2017で試していますが以前のバージョンでは日本語のひらがなで清音、濁音、半濁音が同一視されていました(それなのに照合順序名はASがついているのも疑問ですが)。

同様なことはMySQLの照合順序。character_setがutf8の時にutf8_general_ciでも起きます。ひらがな・カタカナは同一視しませんが。
なので

WHERE ’A' = 'a'

は「真」になります

で本題です。

PostgreSQLの場合は基本OSのlocale設定で決まります。

SHOW LC_COLLATE;

lc_collate => en_US.UTF-8

みたいになっていると日本語のソート順がおかしなことに。

ja_JP.UTF-8

でもいろいろ面倒なことがあるようです。

https://hacknote.jp/archives/24914/

それが「ja-x-icu」を指定すると結構ましな結果になります。

 txt
-----
 a
 a
 A
 A
 は
 は
 ハ
 ハ

一方

SELECT txt FROM t WHERE txt = 'a' OR txt = 'は'

の結果は

 txt
-----
 a
 a
 は
 は

ソート順はよしなに並べてくれるが同一視はしない。

これは文字列の比較条件として抽出、結合にも影響するので普通望んでいる結果を実現できそう。
ただし、現在はPostgreSQL 11でもDATABASE, TABLEのデフォルト指定はできず、項目(列)に対してのみ指定ができるようです。

通常のプログラミング言語では 'a' != 'A' です.
PostgreSQLでもlocaleをCにする
SQL Server,MySQLでもjapanese_binのようにバイナリ比較の照合順序を使うというのもありますが、そうするとODER BYの並び順も文字コード順になるので、使い分けが必要かなと思います。

内部処理的にはバイナリ比較にしたほうが処理負荷は少ないでしょうけど。

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした