1.数据库逻辑结构介绍
在一个PostgreSQL数据库系统中，
- 数据库： 一个PostgreSQL数据库服务下可以管理多个数据库，当应用连接到一个数据库时，一般只能访问这个数据库中的数据，而不能访问其他数据库中的内容（除非使用dblink等其他手段）
- 表、索引：一个数据库中有很多表、索引。一般，在PostgreSQL中表的术语为”Relation”,而在其他数据库中则叫”Table”
- 数据行：每张表中有很多行数据。在PostgreSQL中行的术语一般为”Tuple”,而在其他数据库中则叫”Row”
注意: 在PostgreSQL中，一个数据库服务（实例）下可以有多个数据库，而一个数据库不能属于多个实例，这与Oracle数据库不同，在Oracle 数据库中，一个实例只能有一个数据库，但一个数据库可以在多个实例中（如RAC）。
2.创建数据库
创建数据库的语法：
CREATE DATABASE name
[ [ WITH ] [ OWNER [=] user_name ]
[ TEMPLATE [=] template ]
[ ENCODING [=] encoding ]
[ LOCALE [=] locale ]
[ LC_COLLATE [=] lc_collate ]
[ LC_CTYPE [=] lc_ctype ]
[ TABLESPACE [=] tablespace_name ]
[ ALLOW_CONNECTIONS [=] allowconn ]
[ CONNECTION LIMIT [=] connlimit ]
[ IS_TEMPLATE [=] istemplate ] ]一般情况下，不需要上面那么多参数，最简单的建库命令：
CREATE DATABASE testdb;参数说明
- [ OWNER [=] user_name ] ：指定新建的数据库属于哪个用户，如果不指定就默认属于当前执行命令的用户
- [ TEMPLATE [=] template ]：模板名（从哪个模板创建新数据库）。如果不指定，将使用默认模板template1
- [ ENCODING [=] encoding ]：创建数据库使用的字符编码。
- [ LOCALE [=] locale ]：这是同时设置LC_COLLATE和LC_CTYPE的快捷方式。如果指定此参数，则不能指定其中任何一个参数。
- [ LC_COLLATE [=] lc_collate ]：在新数据库中使用的排序顺序(LC_COLLATE)。这将影响应用于字符串的排序顺序，例如，在使用order BY的查询中，以及在文本列上的索引中使用的顺序。默认情况下使用模板数据库的排序顺序。参见下面的附加限制。
- [ LC_CTYPE [=] lc_ctype ]：字符分类(LC_CTYPE)在新数据库中使用。这将影响字符的分类，例如，小写、大写和数字。默认情况下使用模板数据库的字符分类。参见下面的附加限制。
- [ TABLESPACE [=] tablespace_name ]：指定和新数据库关联的表空间名字
- [ CONNECTION LIMIT [=] connlimit ]：数据库可以接受多少并发的连接。默认为-1，表示没有限制。
在日常使用中，很少会用到指定数据库的字符集。因为PostgreSQL数据库服务端并不支持通常汉字字符集”GBK”、”GB18030”,所以一般都用”UTF8”字符集来支持中文的。
建库例子：
1.创建一个LATIN1编码的数据库
postgres=# CREATE DATABASE testdb01 ENCODING 'LATIN1' TEMPLATE template0;
ERROR: encoding "LATIN1" does not match locale "en_US.UTF-8"
DETAIL: The chosen LC_CTYPE setting requires encoding "UTF8".
注意，上面的命令中需要指定模板数据库为template0，而不是template1.这是因为编码和区域设置必须与模板数据库的相匹配，如果模板数据库中包含了与新建的数据库之编码不匹配的数据，或者包含了排序受LC_COLLATE和LC_CTYPE影响的索引，那么复制这些数据将会导致数据库被新设置破坏。template0公认不包含任何字符集编码或排序影响的数据或索引，故可以作为创建任意字符集数据库的模板。
postgres=# CREATE DATABASE testdb01 ENCODING 'LATIN1' LC_COLLATE='aa_DJ.iso88591' LC_CTYPE='aa_DJ.iso88591' TEMPLATE template0;
查询字符集支持的 LC_COLLATE 和 LC_CTYPE 信息
postgres=# select pg_encoding_to_char(collencoding) as encoding,collname,collcollate,collctype from pg_collation ;
encoding | collname | collcollate | collctype
------------+------------------------+-----------------------+-----------------------
| default | |
| C | C | C
| POSIX | POSIX | POSIX
UTF8 | ucs_basic | C | C
LATIN1 | aa_DJ | aa_DJ | aa_DJ
LATIN1 | aa_DJ.iso88591 | aa_DJ.iso88591 | aa_DJ.iso88591
UTF8 | aa_DJ.utf8 | aa_DJ.utf8 | aa_DJ.utf8
UTF8 | aa_ER | aa_ER | aa_ER
UTF8 | aa_ER@saaho | aa_ER@saaho | aa_ER@saaho
UTF8 | aa_ER.utf8 | aa_ER.utf8 | aa_ER.utf8
UTF8 | aa_ER.utf8@saaho | aa_ER.utf8@saaho | aa_ER.utf8@saaho
UTF8 | aa_ET | aa_ET | aa_ET
UTF8 | aa_ET.utf8 | aa_ET.utf8 | aa_ET.utf8
2.创建数据库sals,指定用户salesapp,表空间salesspace
postgres=# CREATE DATABASE sales OWNER salesapp TABLESPACE salesspace;
3.创建不同区域和字符编码的数据库
postgres=# CREATE DATABASE music2 LOCALE 'sv_SE.iso885915' ENCODING LATIN9 TEMPLATE template0;
LOCALE和ENCODING必须匹配，否则会报错。
LOCALE名称特定于操作系统，不同的操作系统命令可能不一样。
4.创建数据库korean,编码为EUC_KR ，排序顺序和字符类型必须匹配。
postgres=# CREATE DATABASE korean WITH ENCODING 'EUC_KR' LC_COLLATE='ko_KR.euckr' LC_CTYPE='ko_KR.euckr' TEMPLATE=template0;
注意，ENCODING，LC_COLLATE，LC_CTYPE三个参数需要你保持一致，因为不一致可能会由于编码字符集不匹配而导致乱码等问题；
5.创建支持中文的数据库
postgres=# CREATE DATABASE chinese WITH ENCODING 'UTF8';
查看创建的数据库
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-----------------+-----------------+-----------------------
chinese | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
korean | postgres | EUC_KR | ko_KR.euckr | ko_KR.euckr |
music2 | postgres | LATIN9 | sv_SE.iso885915 | sv_SE.iso885915 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
testdb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
testdb01 | postgres | LATIN1 | aa_DJ.iso88591 | aa_DJ.iso88591 |
(8 rows)3.修改数据库
修改数据库语法：
ALTER DATABASE name [ [ WITH ] option [ ... ] ]
where option can be:
ALLOW_CONNECTIONS allowconn
CONNECTION LIMIT connlimit
IS_TEMPLATE istemplate
ALTER DATABASE name RENAME TO new_name
ALTER DATABASE name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER DATABASE name SET TABLESPACE new_tablespace
ALTER DATABASE name SET configuration_parameter { TO | = } { value | DEFAULT }
ALTER DATABASE name SET configuration_parameter FROM CURRENT
ALTER DATABASE name RESET configuration_parameter
ALTER DATABASE name RESET ALL例子:
1.改变数据库testdb01的最大连接数为10
postgres=# alter database testdb01 CONNECTION LIMIT 10;
2.改变数据库testdb01的名称为prodb01
postgres=# alter database testdb01 rename to prodb01;
ALTER DATABASE
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-----------------+-----------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
prodb01 | postgres | LATIN1 | aa_DJ.iso88591 | aa_DJ.iso88591 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
3.关闭testdb01上的默认索引扫描
postgres=# alter database testdb01 set enable_indexscan TO off;
4.清除所有的数据库设置
postgres=# alter database testdb01 reset all;4.删除数据库
1.直接删除数据库
postgres=# drop database prodb01;
DROP DATABASE
2.如果数据库存在，则将其删除，如果不存在，使用删除命令也不报错
postgres=# drop database if exists prodb01;
NOTICE: database "prodb01" does not exist, skipping
DROP DATABASE
注意：如果还有人连接在这个数据库上，将不能删除该数据库
postgres=# drop database music2;
ERROR: database "music2" is being accessed by other users
DETAIL: there is 1 other session using the database.5.常见问题
1. 不能在事务块中创建或删除数据库
postgres=# begin;
BEGIN
postgres=*# create database testdb01;
ERROR: CREATE DATABASE cannot run inside a transaction block
postgres=!# drop database chinese;
ERROR: current transaction is aborted, commands ignored until end of transaction block
2.可以在事务块中修改数据库
postgres=*# alter database music2 rename to music3;
ALTER DATABASE
postgres=*# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-----------------+-----------------+-----------------------
chinese | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
korean | postgres | EUC_KR | ko_KR.euckr | ko_KR.euckr |
music3 | postgres | LATIN9 | sv_SE.iso885915 | sv_SE.iso885915 |
postgres=*# rollback;
ROLLBACK
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-----------------+-----------------+-----------------------
chinese | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
korean | postgres | EUC_KR | ko_KR.euckr | ko_KR.euckr |
music2 | postgres | LATIN9 | sv_SE.iso885915 | sv_SE.iso885915 |