PostgreSQL 实用实例参考
陈景峰(netkiller)
前言
经过三个月的努力《PostgreSQL 实用实例参考》正式版终于推出了。因为最近换了工作，
新公司的工作也很忙所以文档进展很慢，从最初几十页写到现在 200 页的文档，每天写文档的
时间越来越少，有时一周也就只写 2 页，甚至一周一字未对。
正式版推出了，然后就是不断的修正。可能这段时间《PostgreSQL 实用实例参考》更新会
更慢些。因为我还有其它文档要写：《OpenLDAP 文档》、《PHP + Corba + Python 开发分布式组
件》、《JBuilder + Weblogic + PostgreSQL 开发 EJB》。。。。。
文档中所有例子，都是在工作总结出来的，如有错误请指正。本人爱写错别字（哈哈）如
果你发现了有错字，请发邮件给我 netkiller@9812.net
修正文档，在这里我假设你对数据有一定
认识。
目录
1 简介......................................................................................................................................................... 7
1.1 关于性能................................................................................................................................. 7
1.2 PostgreSQL 对 SQL99 的支持...............................................................................................7
2 PostgreSQL 数据库 ...............................................................................................................................8
2.1 PostgreSQL 分区 ....................................................................................................................8
2.2 RPM 包安装 ........................................................................................................................... 9
2.3 数据库备份方案................................................................................................................... 14
2.3.1 备份数据库脚本...........................................................................................................14
2.3.2 下载备份脚本............................................................................................................... 15
2.4 备份计划............................................................................................................................... 15
2.4.1 服务器端计划............................................................................................................... 31
2.4.2 客户端计划................................................................................................................... 32
2.5 数据恢复............................................................................................................................... 32
2.6 性能提升............................................................................................................................... 32
2.6.1 共享内存....................................................................................................................... 32
2.6.2 最大连接....................................................................................................................... 34
2.6.3 vacuumdb ......................................................................................................................39
2.6.4 数据库操作与性能 ....................................................................................................... 40
2.6.5 硬件方面....................................................................................................................... 41
2.7 使用 SSL 进行安全的 TCP/IP 联接 .................................................................................... 42
2.7.1 设置用户信息：...........................................................................................................42
2.7.2 生产秘钥文件：...........................................................................................................44
2.7.3 产生证书文件：...........................................................................................................46
2.7.4 权限方面：................................................................................................................... 48
2.7.5 配置 postgresql.conf 文件：......................................................................................... 48
2.7.6 测试 SSL ....................................................................................................................... 54
2.7.7 配置 pg_hba.conf 强制使用 SSL 联接： ....................................................................54
2.7.8 连接测试：
...................................................................................................................55
2.7.9 注意事项：................................................................................................................... 56
2.8 使用 SSH 进行安全 TCP/IP 联接........................................................................................ 57
2.8.1 实例 1............................................................................................................................58
3 数据定义（DDL）...............................................................................................................................59
3.1 日期时间常量....................................................................................................................... 59
3.1.1 当前日期....................................................................................................................... 59
3.1.2 当前时间....................................................................................................................... 59
3.1.3 当前日期时间............................................................................................................... 59
3.1.4 除去时区....................................................................................................................... 60
3.1.5 计算时间差................................................................................................................... 60
3.1.6 计算时间和................................................................................................................... 61
3.1.7 date_part ........................................................................................................................ 61
3.2 汉字做字段名....................................................................................................................... 61
3.3 “：：”数据转换................................................................................................................... 64
3.3.1 text to varchar................................................................................................................64
3.4 序列.......................................................................................................................................66
3.4.1 等差列........................................................................................................................... 66
3.4.2 “1，2，3，4，5，6，7，8，9…”.............................................................................. 67
3.4.3 “1，3，5，7，9…” ..................................................................................................... 68
3.4.4 “2，4，6，8，10…” ................................................................................................... 69
3.4.5 n
1
+n
2
..............................................................................................................................70
3.5 约束.......................................................................................................................................70
3.6 检查约束............................................................................................................................... 70
3.7 非空约束............................................................................................................................... 71
3.8 唯一约束............................................................................................................................... 71
3.8.1 单字段约束................................................................................................................... 71
3.8.2 多个字段组合约束 ....................................................................................................... 72
3.8.3 唯一约束的注意事项 ................................................................................................... 73
3.9 主键/外键.............................................................................................................................. 76
3.9.1 主键............................................................................................................................... 76
3.9.2 外键约束....................................................................................................................... 76
3.9.3 PostgreSQL 7.3.x 新增功能......................................................................................... 77
3.9.4 例子-分类目录.............................................................................................................. 77
3.9.5 总结............................................................................................................................... 85
3.10 模式.......................................................................................................................................85
3.10.1 创建模式.......................................................................................................................85
3.10.2 删除模式.......................................................................................................................86
3.10.3 模式搜索路径...............................................................................................................86
4 实体关系（Entity-Relation） ..............................................................................................................88
4.1 E-R 图（Entity-Relation）................................................................................................... 88
4.2 一对多关系........................................................................................................................... 89
4.3 多对多关系........................................................................................................................... 91
4.4 一对一关系........................................................................................................................... 93
4.5 引用完整性........................................................................................................................... 94
5 视图....................................................................................................................................................... 95
5.1 VIEW 基本使用实例............................................................................................................95
5.2 使用 HTML 格式化 VIEW 的实例 .....................................................................................96
5.3 view 中使用汉字做字段名 ..................................................................................................99
5.4 取出字符如果超过 20 个在后尾加“…”............................................................................ 100
6 查询 SQL（DML） ...........................................................................................................................102
6.1 子查询................................................................................................................................. 102
6.2 substring()函数截取部分汉字............................................................................................ 104
7 过程与函数......................................................................................................................................... 106
7.1 基本使用实例..................................................................................................................... 106
7.2 过程中使用 Select Into....................................................................................................... 107
7.3 返回 integer.........................................................................................................................109
7.4 返回 void............................................................................................................................. 109
8 规则..................................................................................................................................................... 110
8.1 规则实例............................................................................................................................. 110
9 触发器................................................................................................................................................. 113
9.1 一般用法............................................................................................................................. 113
9.2 多个触发器使用同一个过程 ............................................................................................. 113
9.3 时间调度触发器................................................................................................................. 116
9.3.1 定时触发器................................................................................................................. 117
9.3.2 周期触发器................................................................................................................. 117
10 游标............................................................................................................................................. 118
10.1 游标结果集......................................................................................................................... 119
10.2 例子２................................................................................................................................. 120
11 事务处理..................................................................................................................................... 121
11.1 批量插入、更新、删除 ..................................................................................................... 121
11.1.1 批量插入操作-例 1..................................................................................................... 121
11.2 保持数据完整-例 2............................................................................................................. 122
12 用户权限..................................................................................................................................... 122
12.1.1 组................................................................................................................................. 123
12.1.1.1 创建组............................................................................................................... 123
12.1.1.2 删除组............................................................................................................... 123
12.1.2 用户............................................................................................................................. 124
12.1.2.1 创建用户........................................................................................................... 124
12.1.2.2 删除用户........................................................................................................... 125
12.1.2.3 修改密码........................................................................................................... 125
12.1.3 创建数据..................................................................................................................... 125
12.1.4 用户认证..................................................................................................................... 126
12.1.4.1 本地连接........................................................................................................... 126
12.1.4.2 允许任何 IP 连接主机...................................................................................... 127
12.1.5 脚本例子..................................................................................................................... 127
12.1.6 权限............................................................................................................................. 127
13 FAQ .........................................................................................................................
.................... 128
13.1 Postgresql 与 mysql............................................................................................................. 128
13.2 Putty 中输入汉字的问题.................................................................................................... 131
13.3 控制台下输入汉字............................................................................................................. 135
13.4 PostgreSQL RPM 包安装后，为何没有 5432 端口 ........................................................135
13.5 PHP 连接 PostgreSQL ....................................................................................................... 138
13.6 汉字编码问题..................................................................................................................... 138
13.6.1 Jsp/Java........................................................................................................................138
13.6.2 toChinese() 方法 ........................................................................................................138
13.6.3 Unicode (UTF-8) 完全解决方案 ............................................................................... 138
13.6.3.1 setCharacterEncoding() 方案 ............................................................................ 139
13.6.3.2 Web.xml Filter 过滤方案：............................................................................... 141
13.6.3.3 Jdbc url charSet 方案 ......................................................................................... 147
13.6.4 PHP..............................................................................................................................147
13.6.4.1 set CLIENT_ENCODING TO 'GB18030';方案 ................................................147
13.6.4.2 convert()方案 ..................................................................................................... 156
