PostgreSQLで日本語のソートがおかしい場合の対処法

PostgreSQLで日本語が格納されているカラムをORDER BYでソートさせたら明らかにおかしい順序で返ってきた。

test=# select name from test_table order by name asc;
     name     

























-












 試験
 てすと
 テスト
 てすとです
 試験データ
 テストデータ
 test
(7 rows)

こんな感じ。

まぁlocaleとかcollateの問題なんだろうなーと思いつつもPostgreSQLではどうなってるのか知らなかったので、ぐぐる先生にお伺いを立ててみたら、どうやらデータベースクラスタの初期化時（initdb時）に指定したものが一生使われるらしい。変更不可（えー

追記：8.4からはcreatedb時に指定できるみたい。

現在の設定の確認

pg_controldataコマンドで確認できるらしい。

$ pg_controldata /var/lib/pgsql/data

pg_control version number:            812
Catalog version number:               200510211
Database system identifier:           5197600922672676756
Database cluster state:               in production
pg_control last modified:             Tue 09 Sep 2008 11:15:42 AM JST
Current log file ID:                  0
Next log file segment:                2
Latest checkpoint location:           0/1D56BEC
Prior checkpoint location:            0/1D3E6FC
Latest checkpoint's REDO location:    0/1D56BEC
Latest checkpoint's UNDO location:    0/0
Latest checkpoint's TimeLineID:       1
Latest checkpoint's NextXID:          617956
Latest checkpoint's NextOID:          65776
Latest checkpoint's NextMultiXactId:  1
Latest checkpoint's NextMultiOffset:  0
Time of latest checkpoint:            Tue 09 Sep 2008 11:15:42 AM JST
Maximum data alignment:               4
Database block size:                  8192
Blocks per segment of large relation: 131072
Bytes per WAL segment:                16777216
Maximum length of identifiers:        64
Maximum columns in an index:          32
Date/time type storage:               floating-point numbers
Maximum length of locale name:        128
LC_COLLATE:                           en_US.UTF-8
LC_CTYPE:                             en_US.UTF-8

最後のLC_COLLATEとLC_CTYPEってやつがマッチング関係のロケールで、この場合はen_US.UTF-8になっている。
ちなみにこのサーバはCentOS 5.1でPostgreSQLはパッケージで入れたもの、データベースはEUC-JPという構成。

このロケールとデータベースのエンコーディングの不一致が問題のようだ。

ロケールを指定して初期化する

データベースのエンコーディングを変えるわけにはいかないのでロケールを変えたいわけだが、LC_COLLATEとLC_CTYPEの二つだけはinitdb時にしか設定できないらしい。途中で変える場合はダンプ取って初期化し直してリストアするしかない模様。
変更ができない理由はインデックス情報がうんたらとかマニュアルに書いてあった。

ロケールはinitdb時に指定する。

ja_JP.EUC-JPに設定
$ initdb --locale=ja_JP.EUC-JP

Cロケールに設定（ロケールに依存した処理をしない）
$ initdb --locale=C

--locale=Cと同じ
$ initdb --no-locale

デフォルトエンコーディングも指定するなら
$ initdb -E EUC-JP --no-locale

特別に何かをしたいわけでもなければ--no-localeが無難か。
つかロケール指定したらcreatedb時のエンコーディングも事実上縛られるような。

ぱせらんメモ

PostgreSQLで日本語のソートがおかしい場合の対処法

現在の設定の確認

ロケールを指定して初期化する

参考