PostgreSQL 10.1
手册
PostgreSQL
全球开发组
翻译：彭煜玮
1
，
瀚高软件
2
及其他贡献者
版权
© 1996-2016 The PostgreSQL Global Development Group
摘要
《PostgreSQL
10.1
手册》基于上一版本的《PostgreSQL9.6.0中文手册》
3
翻译。翻译工作主要由瀚高软
件的韩悦悦完成，
详细请参考
PostgreSQL 10
中文手册的翻译
4
。
如果发现中文手册中的问题请向
Github
源码仓库
5
或
PostgreSQL
中文手册翻译小组
QQ
群
(309292849)
反馈,也可直接向
Github
源码仓库提交PR。
中文手册版本:1.0
最后更新时间:2018-06-30
Legal Notice
PostgreSQL is Copyright © 1996-2016 by the PostgreSQL Global Development Group.
Postgres95 is Copyright © 1994-5 by the Regents of the University of California.
Permission to use, copy, modify, and distribute this software and its documentation for any purpose, without fee, and without a written
agreement is hereby granted, provided that the above copyright notice and this paragraph and the following two paragraphs appear in all copies.
IN NO EVENT SHALL THE UNIVERSITY OF CALIFORNIA BE LIABLE TO ANY PARTY FOR DIRECT, INDIRECT, SPECIAL,
INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE
AND ITS DOCUMENTATION, EVEN IF THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED OF THE POSSIBILITY OF
SUCH DAMAGE.
THE UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED
HEREUNDER IS ON AN “AS-IS” BASIS, AND THE UNIVERSITY OF CALIFORNIA HAS NO OBLIGATIONS TO PROVIDE
MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.
1
http://www.pengyuwei.net
2
http://www.highgo.com/
3
http://www.postgres.cn/docs/9.6
4
https://github.com/postgres-cn/pgdoc-cn/wiki/pg10
5
https://github.com/postgres-cn/pgdoc-cn
iii
目录
前言
....................................................................................................................... xxxv
1.
何为PostgreSQL？
....................................................................................... xxxv
2. PostgreSQL
简史
........................................................................................... xxxv
2.1.
伯克利的
POSTGRES
项目
.................................................................. xxxvi
2.2. Postgres95 ........................................................................................ xxxvi
2.3. PostgreSQL ..................................................................................... xxxvii
3.
约定
......................................................................................................... xxxvii
4.
进一步的信息
............................................................................................ xxxvii
5.
缺陷报告指南
........................................................................................... xxxviii
5.1.
标识缺陷
....................................................................................... xxxviii
5.2.
报告什么
....................................................................................... xxxviii
5.3.
向哪里报告缺陷
.................................................................................... xl
I.
教程
........................................................................................................................ 1
1.
从头开始
......................................................................................................... 3
1.1.
安装
..................................................................................................... 3
1.2.
架构基础
............................................................................................... 3
1.3.
创建一个数据库
..................................................................................... 3
1.4.
访问数据库
........................................................................................... 5
2. SQL
语言
.......................................................................................................... 7
2.1.
引言
..................................................................................................... 7
2.2.
概念
..................................................................................................... 7
2.3.
创建一个新表
........................................................................................ 7
2.4.
在表中增加行
........................................................................................ 8
2.5.
查询一个表
........................................................................................... 9
2.6.
在表之间连接
...................................................................................... 10
2.7.
聚集函数
............................................................................................. 12
2.8.
更新
.................................................................................................... 13
2.9.
删除
.................................................................................................... 13
3.
高级特性
........................................................................................................ 15
3.1.
简介
.................................................................................................... 15
3.2.
视图
.................................................................................................... 15
3.3.
外键
.................................................................................................... 15
3.4.
事务
.................................................................................................... 16
3.5.
窗口函数
............................................................................................. 17
3.6.
继承
.................................................................................................... 20
3.7.
小结
.................................................................................................... 21
II. SQL
语言
.............................................................................................................. 22
4. SQL
语法
........................................................................................................ 29
4.1.
词法结构
............................................................................................. 29
4.2.
值表达式
............................................................................................. 37
4.3.
调用函数
............................................................................................. 47
5.
数据定义
........................................................................................................ 50
5.1.
表基础
................................................................................................ 50
5.2.
默认值
................................................................................................ 51
5.3.
约束
.................................................................................................... 51
5.4.
系统列
................................................................................................ 58
5.5.
修改表
................................................................................................ 59
5.6.
权限
.................................................................................................... 61
5.7.
行安全性策略
...................................................................................... 62
5.8.
模式
.................................................................................................... 67
5.9.
继承
.................................................................................................... 70
5.10.
表分区
............................................................................................... 73
5.11.
外部数据
........................................................................................... 82
5.12.
其他数据库对象
................................................................................. 83
5.13.
依赖跟踪
........................................................................................... 83
PostgreSQL 10.1
手册
iv
6.
数据操纵
........................................................................................................ 85
6.1.
插入数据
............................................................................................. 85
6.2.
更新数据
............................................................................................. 86
6.3.
删除数据
............................................................................................. 86
6.4.
从修改的行中返回数据
......................................................................... 87
7.
查询
.............................................................................................................. 88
7.1.
概述
.................................................................................................... 88
7.2.
表表达式
............................................................................................. 88
7.3.
选择列表
........................................................................................... 101
7.4.
组合查询
........................................................................................... 102
7.5.
行排序
............................................................................................... 103
7.6. LIMIT
和
OFFSET ................................................................................ 104
7.7. VALUES
列表
..................................................................................... 104
7.8. WITH
查询（公共表表达式）
............................................................... 105
8.
数据类型
...................................................................................................... 110
8.1.
数字类型
........................................................................................... 111
8.2.
货币类型
........................................................................................... 115
8.3.
字符类型
........................................................................................... 116
8.4.
二进制数据类型
................................................................................. 118
8.5.
日期/时间类型
................................................................................... 119
8.6.
布尔类型
........................................................................................... 127
8.7.
枚举类型
........................................................................................... 128
8.8.
几何类型
........................................................................................... 130
8.9.
网络地址类型
..................................................................................... 132
8.10.
位串类型
.......................................................................................... 134
8.11.
文本搜索类型
................................................................................... 135
8.12. UUID
类型
........................................................................................ 137
8.13. XML
类型
......................................................................................... 138
8.14. JSON
类型
....................................................................................... 140
8.15.
数组
................................................................................................ 145
8.16.
复合类型
.......................................................................................... 153
8.17.
范围类型
.......................................................................................... 158
8.18.
对象标识符类型
................................................................................ 163
8.19. pg_lsn Type ...................................................................................... 164
8.20.
伪类型
............................................................................................. 165
9.
函数和操作符
............................................................................................... 167
9.1.
逻辑操作符
........................................................................................ 167
9.2.
比较操作符
........................................................................................ 167
9.3.
数学函数和操作符
.............................................................................. 169
9.4.
字符串函数和操作符
........................................................................... 172
9.5.
二进制串函数和操作符
....................................................................... 184
9.6.
位串函数和操作符
.............................................................................. 186
9.7.
模式匹配
........................................................................................... 186
9.8.
数据类型格式化函数
........................................................................... 200
9.9.
时间/日期函数和操作符
...................................................................... 206
9.10.
枚举支持函数
................................................................................... 217
9.11.
几何函数和操作符
............................................................................ 218
9.12.
网络地址函数和操作符
...................................................................... 221
9.13.
文本搜索函数和操作符
...................................................................... 223
9.14. XML
函数
........................................................................................ 227
9.15. JSON
函数和操作符
.......................................................................... 238
9.16.
序列操作函数
................................................................................... 245
9.17.
条件表达式
...................................................................................... 247
9.18.
数组函数和操作符
............................................................................ 249
9.19.
范围函数和操作符
............................................................................ 252
9.20.
聚集函数
.......................................................................................... 253
9.21.
窗口函数
.......................................................................................... 259
9.22.
子查询表达式
................................................................................... 260
PostgreSQL 10.1
手册
v
9.23.
行和数组比较
................................................................................... 263
9.24.
集合返回函数
................................................................................... 265
9.25.
系统信息函数
................................................................................... 268
9.26.
系统管理函数
................................................................................... 282
9.27.
触发器函数
...................................................................................... 296
9.28.
事件触发器函数
................................................................................ 296
10.
类型转换
.................................................................................................... 300
10.1.
概述
................................................................................................ 300
10.2.
操作符
............................................................................................. 301
10.3.
函数
................................................................................................ 304
10.4.
值存储
............................................................................................. 306
10.5. UNION
、
CASE
和相关结构
................................................................ 307
10.6. SELECT
输出列
................................................................................ 308
11.
索引
........................................................................................................... 310
11.1.
简介
................................................................................................ 310
11.2.
索引类型
.......................................................................................... 310
11.3.
多列索引
.......................................................................................... 312
11.4.
索引和
ORDER BY ............................................................................ 313
11.5.
组合多个索引
................................................................................... 314
11.6.
唯一索引
.......................................................................................... 314
11.7.
表达式索引
...................................................................................... 315
11.8.
部分索引
.......................................................................................... 315
11.9.
操作符类和操作符族
......................................................................... 317
11.10.
索引和排序规则
.............................................................................. 318
11.11.
只用索引的扫描
.............................................................................. 319
11.12.
检查索引使用
................................................................................. 320
12.
全文搜索
.................................................................................................... 322
12.1.
介绍
................................................................................................ 322
12.2.
表和索引
.......................................................................................... 325
12.3.
空值文本搜索
................................................................................... 327
12.4.
额外特性
.......................................................................................... 332
12.5.
解析器
............................................................................................. 336
12.6.
词典
................................................................................................ 338
12.7.
配置例子
.......................................................................................... 346
12.8.
测试和调试文本搜索
......................................................................... 347
12.9. GIN
和
GiST
索引类型
...................................................................... 351
12.10. psql
支持
......................................................................................... 352
12.11.
限制
............................................................................................... 354
13.
并发控制
.................................................................................................... 355
13.1.
介绍
................................................................................................ 355
13.2.
事务隔离
.......................................................................................... 355
13.3.
显式锁定
.......................................................................................... 360
13.4.
应用级别的数据完整性检查
............................................................... 364
13.5.
提醒
................................................................................................ 366
13.6.
锁定和索引
...................................................................................... 366
14.
性能提示
.................................................................................................... 367
14.1.
使用
EXPLAIN .................................................................................. 367
14.2.
规划器使用的统计信息
...................................................................... 376
14.3.
用显式
JOIN
子句控制规划器
............................................................... 379
14.4.
填充一个数据库
................................................................................ 381
14.5.
非持久设置
...................................................................................... 383
15.
并行查询
.................................................................................................... 385
15.1.
并行查询如何工作
............................................................................ 385
15.2.
何时会用到并行查询？
...................................................................... 385
15.3.
并行计划
.......................................................................................... 386
15.4.
并行安全性
...................................................................................... 388
III.
服务器管理
......................................................................................................... 389
16.
从源代码安装
.............................................................................................. 395
