PostgreSQLで並び順がおかしい時の修正方法

「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
initdb --encoding=UTF-8 --locale=C

バージョン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
スポンサーリンク

シェアする

  • このエントリーをはてなブックマークに追加

フォローする

コメント