「order by」を指定しているのも関わらず、並び順がおかしい、特に日本語を含む場合におかしくなる現象が起きました。
なんでだろうなあ、と調べた結果をまとめます。
並び順の「照合順序」というものがある
文字列をどのような並び順で行うか、という定義が「照合順序」です。
意図した並び順になっていない時がありました。
その場合は、エンコードにあった「照合順序」を定義してやる必要があります。
確認方法
select name, setting, context from pg_settings where name like 'lc%';
| name | setting | context |
|---|---|---|
| lc_collate | C | internal |
| lc_ctype | C | internal |
SHOW LC_COLLATE;
| lc_collate |
|---|
| C |
いずれかのSQLで可能です。
この様なものでいくつか出ますが、並び順の指定は、「lc_collate」のところです。
lc_collateが「C」となっている場合
エンコーディングのバイナリ値で並び順をします。
は（清音）
ば（濁音）
ぱ（半濁音）
ハ（清音）
バ（濁音）
パ（半濁音）
lc_collateが「ja_JP.utf8」や「ja_JP.eucJP」となっている場合
辞書にあるような並び順になります。
ハ（清音）
は（清音）
バ（濁音）
ば（濁音）
パ（半濁音）
ぱ（半濁音）
lc_collateが「en_US.UTF-8」や「en_US.euc」となっている場合
日本語を含むカラムで並び替えを行うと、並び順がおかしくなります。
下記方法で、正しい定義を行います。
定義方法
PostgreSQLのバージョンによって、出来る方法が異なります。
バージョン8.3以前
PostgreSQLをインストールした際に、「initdb」を行うタイミングでのみ、変更可能です。
すでに運用されている場合は、システムを停止し、データベースをダンプ後、行い、データベースをリストアしてください。
initdb --encoding=UTF-8 --locale=ja_JP.UTF-8
バージョン8.4以降
上記の「initdb」時も可能です。
その他に、下記の方法でも可能です。
データベース作成時
createdb testdb --locale=japanese --template=template0
スキーマを変更し特定カラムの照合順序を指定
alter table TABLENAME alter column COLUMNNAME TYPE VARCHAR COLLATE "ja_JP.utf8";
SQL実行時、特定カラムの照合順序を指定
select * from TABLENAME order by COLUMNNAME collate "ja_JP.utf8" asc
コメント