postgresql 上的 COLLATION

由於一些大大們在我的 Facebook 留言討論了 postgresql 的 COLLATION 這件事情, 於是乎我比較仔細地做了實驗。postgresql 的 COLLATION 並無法解決筆畫排序這件事情 orz。

但其實, 我發現要在一個剛裝好的 postgresql 使用 zh_TW.UTF8 這個 COLLATION 還真是有點麻煩!

首先, 如果是一台 EC2 (Ubuntu 12.04 LTS), 裝好之後會發現根本無法使用 zh_TW.UTF8 這個 COLLATION, 原因是系統沒有裝, postgresql 也沒有 create。

因此要先經過以下步驟

1. 在 Ubuntu 把 locale 給 gen 出來

$ sudo locale-gen zh_TW.utf8
Generating locales...
zh_TW.UTF-8... done
Generation complete.

2. 要 Restart postgresql

$ sudo service postgresql restart

3. 要在 postgresql generate collation

$ psql maindb
maindb=> CREATE COLLATION "taiwan" (LOCALE="zh_TW.UTF8");
CREATE COLLATION

當然 taiwan 是我自己取的名字, 比較通用的話可以用 zh_TW.utf8 之類的。

其實這時候可以查看 postgresql 裡頭有的 collation

maindb=> select * from pg_collation;
  collname  | collnamespace | collowner | collencoding | collcollate | collctype  
------------+---------------+-----------+--------------+-------------+------------
 default    |            11 |        10 |           -1 |             | 
 C          |            11 |        10 |           -1 | C           | C
 POSIX      |            11 |        10 |           -1 | POSIX       | POSIX
 C.UTF-8    |            11 |        10 |            6 | C.UTF-8     | C.UTF-8
 en_US      |            11 |        10 |            6 | en_US.utf8  | en_US.utf8
 en_US.utf8 |            11 |        10 |            6 | en_US.utf8  | en_US.utf8
 ucs_basic  |            11 |        10 |            6 | C           | C
 taiwan     |          2200 |     16384 |            6 | zh_TW.utf8  | zh_TW.utf8
(8 rows)

4. 這時候才能使用 COLLATION 這個東西 orz

maindb=> CREATE TABLE test1 (a varchar(64) COLLATE "taiwan");
CREATE TABLE

接著就發現… 他的 sorting 還是無法使用筆畫排序

maindb=> INSERT INTO test1 (a) VALUES ('丁甲乙');
INSERT 0 1
maindb=> INSERT INTO test1 (a) VALUES ('乙丙丁');
INSERT 0 1
maindb=> SELECT * FROM test1 ORDER BY a;
a
--------
丁甲乙
乙丙丁
(2 rows)

maindb=> SELECT * FROM test1 ORDER BY convert_to(a, 'big5');
a
--------
乙丙丁
丁甲乙
(2 rows)

不死心直接在 ORDER BY 下 COLLATE 看看

maindb=> SELECT * FROM test1 ORDER BY a COLLATE "taiwan";
a
--------
丁甲乙
乙丙丁
(2 rows)

一樣的結果…

alas…

為了確認, 我又開了一個 database LC_COLLATION, LC_CTYPE 都換成 zh_TW.utf8, 然後再做一次一樣的實驗, 一樣無果。
所以基本上就是確認了, 即使設定了 COLLATE, 在 postgresql 一樣無法正確的使用排序。

(如果有人可以提供更好的解法, 那就太棒啦 :D)