创建数据库

要创建一个数据库，你必须是一个超级用户或者具有特殊的CREATEDB特权，默认情况下，新数据库将通过克隆标准系统数据库template1被创建。可以通过写TEMPLATE name指定一个不同的模板。特别地，通过写TEMPLATE template0你可以创建一个干净的数据库，它将只包含你的TDSQL PG所预定义的标准对象。

• 默认参数创建数据库。

   postgres=# create database tbase_db;
   CREATE DATABASE

• 指定克隆库。

   postgres=# create database tbase_db_template TEMPLATE template0;
   CREATE DATABASE

• 指定所有者。

   postgres=# create role pgxz with login;
   CREATE ROLE
   postgres=# create database tbase_db_owner owner pgxz;
   CREATE DATABASE
   postgres=# \l+ tbase_db_owner
                                                   List of databases
        Name      | Owner | Encoding|  Collate   |  Ctype    | Access privileges |Size  | Tablespace | Description 
   ----------------+-------+----------+------------+------------+-------------------+-------+------------+-------------
    tbase_db_owner | pgxz  | UTF8    | en_US.utf8 | en_US.utf8 |                   | 18 MB | pg_default | 
   (1 row)

• 指定编码。

   postgres=# create database tbase_db_encoding ENCODING UTF8;    
   CREATE DATABASE
   postgres=# \l+  tbase_db_encoding   
                                                   List of databases
        Name      | Owner | Encoding|  Collate   |  Ctype    | Access privileges |Size  | Tablespace | Description 
   -------------------+-------+----------+------------+------------+-------------------+-------+------------+-------------
    tbase_db_encoding | tbase | UTF8     | en_US.utf8 | en_US.utf8 |                   | 18 MB | pg_default | 
   (1 row)

• 创建gbk编码。

   postgres=# CREATE DATABASE db_gbk template template0 encoding = gbk LC_COLLATE = 'zh_CN.gbk' LC_CTYPE = 'zh_CN.gbk';
   CREATE DATABASE
   postgres=# \l+
                                                   List of databases
        Name      | Owner | Encoding|  Collate   |  Ctype    | Access privileges |Size  | Tablespace | Description 
   -----------+-------+----------+------------+------------+-------------------+-------+------------+--------------------------------------------
    db_gbk    | tbase | GBK      | zh_CN.gbk  | zh_CN.gbk  |                   | 19 MB | pg_default | 
    postgres  | tbase | UTF8     | zh_CN.utf8 | zh_CN.utf8 |                   | 26 MB | pg_default | default administrative connection database
    template0 | tbase | UTF8     | zh_CN.utf8 | zh_CN.utf8 | =c/tbase         +| 19 MB | pg_default | unmodifiable empty database
              |       |          |            |            | tbase=CTc/tbase   |       |            | 
    template1 | tbase | UTF8     | zh_CN.utf8 | zh_CN.utf8 | =c/tbase         +| 24 MB | pg_default | default template for new databases
              |       |          |            |            | tbase=CTc/tbase   |       |            | 
   (4 rows)

• 创建gb18030编码。

   postgres=# create database db_gb18030 template template0 encoding=gb18030 LC_COLLATE = 'zh_CN.gb18030' LC_CTYPE = 'zh_CN.gb18030';  
   CREATE DATABASE
   postgres=# \l
                                                   List of databases
       Name    | Owner | Encoding |    Collate    |     Ctype     | Access privileges 
   ------------+-------+----------+---------------+---------------+-------------------
    db_gb18030 | tbase | GB18030  | zh_CN.gb18030 | zh_CN.gb18030 | 
    db_gbk     | tbase | GBK      | zh_CN.gbk     | zh_CN.gbk     | 
    postgres   | tbase | UTF8     | zh_CN.utf8    | zh_CN.utf8    | 
    template0  | tbase | UTF8     | zh_CN.utf8    | zh_CN.utf8    | =c/tbase         +
               |       |          |               |               | tbase=CTc/tbase
    template1  | tbase | UTF8     | zh_CN.utf8    | zh_CN.utf8    | =c/tbase         +
               |       |          |               |               | tbase=CTc/tbase
    test       | tbase | UTF8     | zh_CN.utf8    | zh_CN.utf8    | 
   (6 rows)
  
   postgres=#

• 指定排序规则。

   postgres=# create database tbase_db_lc_collate lc_collate 'C';
   CREATE DATABASE
   postgres=# \l+ tbase_db_lc_collate
                                                   List of databases
        Name      | Owner | Encoding|  Collate   |  Ctype    | Access privileges |Size  | Tablespace | Description 
   ---------------------+-------+----------+---------+------------+-------------------+-------+------------+-------------
    tbase_db_lc_collate | tbase | UTF8     | C       | en_US.utf8 |                   | 18 MB | pg_default | 
   (1 row)
  

• 指定分组规则。

   postgres=# create database tbase_db_lc_ctype LC_CTYPE 'C' ;
   CREATE DATABASE
   postgres=# \l+  tbase_db_lc_ctype 
                                                   List of databases
        Name      | Owner | Encoding|  Collate   |  Ctype    | Access privileges |Size  | Tablespace | Description 
   -------------------+-------+----------+------------+-------+-------------------+-------+------------+-------------
    tbase_db_lc_ctype | tbase | UTF8     | en_US.utf8 | C     |                   | 18 MB | pg_default | 
   (1 row)

• 配置数据可连接。

   postgres=# create database tbase_db_allow_connections ALLOW_CONNECTIONS true;
   CREATE DATABASE
   postgres=# select datallowconn from pg_database where datname='tbase_db_allow_connections'; 
    datallowconn 
   --------------
    t

• 配置连接数。

   postgres=# create database tbase_db_connlimit CONNECTION LIMIT 100;
   CREATE DATABASE
   postgres=# select datconnlimit  from pg_database where datname='tbase_db_connlimit';                       
    datconnlimit 
   --------------
             100
   (1 row)

• 配置数据库可以被复制。

   postgres=# create database tbase_db_istemplate is_template true;
   CREATE DATABASE
   postgres=# select datistemplate from pg_database where datname='tbase_db_istemplate';            
    datistemplate 
   ---------------
    t
   (1 row)

• 多个参数一起配置。

   postgres=# create database tbase_db_mul owner pgxz CONNECTION LIMIT 50 template template0 encoding 'utf8'  lc_collate 'C';
   CREATE DATABASE