17 2 月

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 出來

[code]]czo5NjpcIiQgc3VkbyBsb2NhbGUtZ2VuIHpoX1RXLnV0ZjgNCkdlbmVyYXRpbmcgbG9jYWxlcy4uLg0KemhfVFcuVVRGLTguLi4gZG97WyYqJl19bmUNCkdlbmVyYXRpb24gY29tcGxldGUuDQpcIjt7WyYqJl19[[/code]

2. 要 Restart postgresql

[code]]czozNTpcIiQgc3VkbyBzZXJ2aWNlIHBvc3RncmVzcWwgcmVzdGFydA0KXCI7e1smKiZdfQ==[[/code]

3. 要在 postgresql generate collation

[code]]czo5NTpcIiQgcHNxbCBtYWluZGINCm1haW5kYj0mZ3Q7IENSRUFURSBDT0xMQVRJT04gXCJ0YWl3YW5cIiAoTE9DQUxFPVwiemhfVFcuVVR7WyYqJl19RjhcIik7DQpDUkVBVEUgQ09MTEFUSU9ODQpcIjt7WyYqJl19[[/code]

當然 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
[code]]czo4MjpcIg0KbWFpbmRiPSZndDsgQ1JFQVRFIFRBQkxFIHRlc3QxIChhIHZhcmNoYXIoNjQpIENPTExBVEUgXCJ0YWl3YW5cIik7DQpDUntbJiomXX1FQVRFIFRBQkxFDQpcIjt7WyYqJl19[[/code]

接著就發現… 他的 sorting 還是無法使用筆畫排序
[code]]czoyMzA6XCINCm1haW5kYj0mZ3Q7IElOU0VSVCBJTlRPIHRlc3QxIChhKSBWQUxVRVMgKFwn5LiB55Sy5LmZXCcpOw0KSU5TRVJUIDAgMXtbJiomXX0NCm1haW5kYj0mZ3Q7IElOU0VSVCBJTlRPIHRlc3QxIChhKSBWQUxVRVMgKFwn5LmZ5LiZ5LiBXCcpOw0KSU5TRVJUIDAgMQ0KbWFpbmR7WyYqJl19Yj0mZ3Q7IFNFTEVDVCAqIEZST00gdGVzdDEgT1JERVIgQlkgYTsNCmENCi0tLS0tLS0tDQrkuIHnlLLkuZkNCuS5meS4meS4gQ0KKHtbJiomXX0yIHJvd3MpDQpcIjt7WyYqJl19[[/code]

[code]]czoxMTE6XCIgbWFpbmRiPSZndDsgU0VMRUNUICogRlJPTSB0ZXN0MSBPUkRFUiBCWSBjb252ZXJ0X3RvKGEsIFwnYmlnNVwnKTsNCmENCntbJiomXX0tLS0tLS0tLQ0K5LmZ5LiZ5LiBDQrkuIHnlLLkuZkNCigyIHJvd3MpDQpcIjt7WyYqJl19[[/code]

不死心直接在 ORDER BY 下 COLLATE 看看
[code]]czoxMDk6XCINCm1haW5kYj0mZ3Q7IFNFTEVDVCAqIEZST00gdGVzdDEgT1JERVIgQlkgYSBDT0xMQVRFIFwidGFpd2FuXCI7DQphDQotLXtbJiomXX0tLS0tLS0NCuS4geeUsuS5mQ0K5LmZ5LiZ5LiBDQooMiByb3dzKQ0KXCI7e1smKiZdfQ==[[/code]
一樣的結果…

alas…

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

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