PostgreSQL COLLATE 作用

PostgreSQL COLLATE用來設定欄位的排序規則及字元分類，又稱為「定序」。

資料庫執行ORDER BY敘述時會依照欄位值將資料做排序，而資料庫的collation則決定欄位的排序規則，例如英文是否區分大小寫、中文是用筆畫還是注音排序等。

執行SELECT * FROM pg_collation或在psql輸入/dOS+可查詢PostgreSQL預先定義的collation表。

postgres=> \dOS+
                                                                  List of collations
   Schema   |          Name          |     Collate      |      Ctype       | Provider | Deterministic? |                 Description
------------+------------------------+------------------+------------------+----------+----------------+----------------------------------------------
 pg_catalog | C                      | C                | C                | libc     | yes            | standard C collation
 pg_catalog | POSIX                  | POSIX            | POSIX            | libc     | yes            | standard POSIX collation
...
 pg_catalog | zh-Hans-CN-x-icu       | zh-Hans-CN       | zh-Hans-CN       | icu      | yes            | Chinese (Simplified, China)
 pg_catalog | zh-Hans-HK-x-icu       | zh-Hans-HK       | zh-Hans-HK       | icu      | yes            | Chinese (Simplified, Hong Kong SAR China)
 pg_catalog | zh-Hans-MO-x-icu       | zh-Hans-MO       | zh-Hans-MO       | icu      | yes            | Chinese (Simplified, Macao SAR China)
 pg_catalog | zh-Hans-SG-x-icu       | zh-Hans-SG       | zh-Hans-SG       | icu      | yes            | Chinese (Simplified, Singapore)
 pg_catalog | zh-Hans-x-icu          | zh-Hans          | zh-Hans          | icu      | yes            | Chinese (Simplified)
 pg_catalog | zh-Hant-HK-x-icu       | zh-Hant-HK       | zh-Hant-HK       | icu      | yes            | Chinese (Traditional, Hong Kong SAR China)
 pg_catalog | zh-Hant-MO-x-icu       | zh-Hant-MO       | zh-Hant-MO       | icu      | yes            | Chinese (Traditional, Macao SAR China)
 pg_catalog | zh-Hant-TW-x-icu       | zh-Hant-TW       | zh-Hant-TW       | icu      | yes            | Chinese (Traditional, Taiwan)
 pg_catalog | zh-Hant-x-icu          | zh-Hant          | zh-Hant          | icu      | yes            | Chinese (Traditional)
 pg_catalog | zh-x-icu               | zh               | zh               | icu      | yes            | Chinese
 pg_catalog | zh_CN                  | zh_CN            | zh_CN            | libc     | yes            |
 pg_catalog | zh_CN.UTF-8            | zh_CN.UTF-8      | zh_CN.UTF-8      | libc     | yes            |
 pg_catalog | zh_HK                  | zh_HK            | zh_HK            | libc     | yes            |
 pg_catalog | zh_HK.UTF-8            | zh_HK.UTF-8      | zh_HK.UTF-8      | libc     | yes            |
 pg_catalog | zh_TW                  | zh_TW            | zh_TW            | libc     | yes            |
 pg_catalog | zh_TW.UTF-8            | zh_TW.UTF-8      | zh_TW.UTF-8      | libc     | yes            |

• PostgreSQL Doc - Collation Support