创建数据库

要创建一个数据库，你必须是一个超级用户或者具有特殊的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