PostgreSQL 创建库时如何指定 Collate, Ctype

背景

初始化集群，建库，建表，建索引，sort | compare QUERY时都可以指定COLLATE。

用法参考

https://www.postgresql.org/docs/9.5/static/sql-createtable.html

https://www.postgresql.org/docs/9.5/static/collation.html

CREATE DATABASE name  
    [ [ WITH ] [ OWNER [=] user_name ]  
           [ TEMPLATE [=] template ]  
           [ ENCODING [=] encoding ]  
           [ LC_COLLATE [=] lc_collate ]  
           [ LC_CTYPE [=] lc_ctype ]  
           [ TABLESPACE [=] tablespace_name ]  
           [ ALLOW_CONNECTIONS [=] allowconn ]  
           [ CONNECTION LIMIT [=] connlimit ] ]  
           [ IS_TEMPLATE [=] istemplate ]  
  
CREATE TABLE test1 (  
    a text COLLATE "de_DE",  
    b text COLLATE "es_ES",  
    ...  
);  
  
CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON table_name [ USING method ]  
    ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )  
    [ WITH ( storage_parameter = value [, ... ] ) ]  
    [ TABLESPACE tablespace_name ]  
    [ WHERE predicate ]  
  
Character-string data is sorted according to the collation that applies to the column being sorted.  
That can be overridden at need by including a COLLATE clause in the expression, for example  
ORDER BY mycolumn COLLATE "en_US".  

如果你要在一个集群中创建不同的或者与模板库不兼容的LC怎么做呢？

通过template0创建即可，建议你这样做：

新建所有需求的lc库，以它们为模板再创建其他的库。

创建模板库，指定collate

PostgreSQL 9.5以下的版本不支持is_template选项，不加即可。

postgres=# create database tmpdb_zh_cn with template template0 lc_collate 'zh_CN.UTF8' lc_ctype 'zh_CN.UTF8' is_template=true;  

在模板库中创建一些你需要预定义的东西，包括数据等等。

tmpdb_zh_cn=# create extension hstore;  
CREATE EXTENSION  

基于模板库创建数据库。

postgres=# create database db1_zh_cn with template tmpdb_zh_cn;  
CREATE DATABASE  
  
 tmp         | postgres | UTF8     | zh_CN.utf8 | zh_CN.utf8 |                       | 7360 kB | pg_default |   
 tmpdb_zh_cn | postgres | UTF8     | zh_CN.UTF8 | zh_CN.UTF8 |                       | 7360 kB | pg_default |   
(8 rows)  

如果你要删除模板库，需要先把模板库改成普通库再删除。

postgres=# alter database tmpdb_zh_cn is_template false;  
ALTER DATABASE  
postgres=# drop database tmpdb_zh_cn ;  
DROP DATABASE  

查询PG支持的collate

postgres=# 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  
 aa_DJ                 |            11 |        10 |            6 | aa_DJ.utf8            | aa_DJ.utf8  
 aa_DJ                 |            11 |        10 |            8 | aa_DJ                 | aa_DJ  
 aa_DJ.iso88591        |            11 |        10 |            8 | aa_DJ.iso88591        | aa_DJ.iso88591  
......  

查询列对应的collate

pg_catalog.pg_attribute.attcollation  

对应 pg_collation.oid

digoal’s 大量PostgreSQL文章入口