はじめに
PostgreSQLで文字列カラム (character varying a.k.a. varchar) にてORDER BYを使いソートすると、並び順がむちゃくちゃ。けれど、ローカルでは問題無かったはず……。そんな時の解決方法です。
私はHeroku Postgresを使用していて遭遇しました。ここではこちらを例に挙げてご説明します。
原因
文字の並び順の設定（文字の照合順序）が正しく指定されていないことが原因と考えられます。
（Herokuの場合は以下のコマンドで直接DBに接続出来ます）
$ heroku pg:psql
psqlでデータベースに接続し、照合順序を見てみると……なるほどね。
=> SHOW LC_COLLATE;
lc_collate
-------------
en_US.UTF-8
(1 行)
前準備
インストールされている照合順序の中から、お好みのものを選びましょう。
=> SELECT * FROM pg_collation;
解決方法
ここでは items テーブルの title カラム (character varying) で降順ソートしたい場合を例とし、解決方法を説明します。
1. ソート時に照合順序を指定
ALTERをかける必要も無く楽です。クエリのORDER BYにて照合順序を指定します。
ORDER BY title DESC
となっているのを、
ORDER BY title COLLATE "ja_JP.utf8" DESC
と変更
2. スキーマを変更し特定カラムの照合順序を変更
逆にこちらはクエリに手を入れる必要が無く楽です。
=> ALTER TABLE items ALTER COLUMN title TYPE VARCHAR COLLATE "ja_JP.utf8";
これで大丈夫。確認です。
=> \d items
出来ましたね。
title | character varying | 照合順序 ja_JP.utf8
余談
Heroku Postgresで \l すると共有スペース時代を思い出せます。