PostgreSQL 即学即用

本文目录结构

编辑推荐

本书是实用的 PostgreSQL 快速上手指南，简明扼要地介绍了 PostgreSQL 核心概念与功能特性，可以帮助读者快速学习、理解并运用好这款开源数据库。

除了用诸多示例重点演示如何实现在别的数据库中难以实现或者根本不可能实现的任务，作者还介绍了 ANSI-SQL 语法、外部数据封装器增强、全文检索函数和运算符、XML 语法、逻辑复制等 PostgreSQL 9.5 之后版本的新增功能，充分展示了 PostgreSQL 作为全栈数据库的强大功能和企业级特性。

• 执行基本的数据库管理任务，比如角色管理、数据库创建、数据备份和恢复等；
• 使用 psql 命令行工具以及 pgAdmin 图形化管理工具；
• PostgreSQL 的表、约束和索引等数据库对象的特性和使用方法；
• PostgreSQL 所特有的若干功能强大的 SQL 语法；
• 使用多种不同的编程语言来编写 PostgreSQL 函数；
• 实施语句调优以充分挖掘服务器硬件的潜能；
• 通过外部数据封装器来查询多种多样的外部数据；
• 使用系统内置的复制筛选器复制数据。

内容简介

本书将帮助你理解和使用 PostgreSQL 这一开源数据库系统。主要介绍 PostgreSQL 9.5、PostgreSQL 9.6 以及 PostgreSQL 10 的核心概念和功能特性，但也会涉及之前版本中一些独特的高级特性。你会发现 PostgreSQL 不只是个数据库系统，也是一个出色的应用平台。本书通过示例展示了如何实现在其他数据库中难以或不能完成的任务。

作者简介

瑞金娜·奥贝（Regina Obe）是数据库咨询公司 Paragon 的负责人之一，在多种编程语言和数据库系统上有 20 多年的从业经验。她是 PostGIS 项目指导委员会成员，也是 PostGIS 核心开发团队的成员，还参与 pgRouting 和 GEOS 开发。

利奥·徐（Leo Hsu）也是 Paragon 公司的负责人之一，曾为大大小小的组织开发过数据库，有 20 多年的从业经验。

目录

• 前言 　　xiii

第 1 章　基础知识 　　1

• 1．1　为什么应该选择 PostgreSQL 　　1
• 1．2　不适用 PostgreSQL 的场景 　　3
• 1．3　如何获得 PostgreSQL 　　3
• 1．4　管理工具 　　4
• 1．4．1　psql 　　4
• 1．4．2　pgAdmin 　　4
• 1．4．3　phpPgAdmin 　　6
• 1．4．4　Adminer 　　6
• 1．5　PostgreSQL 数据库对象 　　7
• 1．6　最新版本的 PostgreSQL 中引入的新特性 　　12
• 1．6．1　为什么要升级 　　12
• 1．6．2　PostgreSQL 10 中引入的新特性 　　12
• 1．6．3　PostgreSQL 9．6 中引入的新特性 　　13
• 1．6．4　PostgreSQL 9．5 中引入的新特性 　　14
• 1．6．5　PostgreSQL 9．4 中引入的新特性 　　15
• 1．7　数据库驱动程序 　　17
• 1．8　如何获得帮助 　　18
• 1．9　PostgreSQL 的主要衍生版本 　　18

第 2 章　数据库管理 　　19

• 2．1　配置文件 　　19
• 2．1．1　让配置文件生效 　　20
• 2．1．2　postgresql．conf 　　21
• 2．1．3　pg_hba．conf　　25
• 2．2　连接管理 　　27
• 2．3　角色 　　29
• 2．3．1　创建可登录角色 　　30
• 2．3．2　创建组角色 　　30
• 2．4　创建 database 　　33
• 2．4．1　模板数据库 　　33
• 2．4．2　schema 的使用 　　34
• 2．5　权限管理 　　36
• 2．5．1　权限的类型 　　36
• 2．5．2　入门介绍 　　36
• 2．5．3　GRANT 　　37
• 2．5．4　默认权限 　　38
• 2．5．5　PostgreSQL 权限体系中一些与众不同的特点 　　39
• 2．6　扩展包机制 　　39
• 2．6．1　扩展包的安装 　　40
• 2．6．2　通用扩展包 　　42
• 2．7　备份与恢复 　　44
• 2．7．1　使用 pg_dump 进行有选择性的备份 　　45
• 2．7．2　使用 pg_dumpall 进行全局备份 　　46
• 2．7．3　数据恢复 　　47
• 2．8　基于表空间机制进行存储管理 　　48
• 2．8．1　表空间的创建 　　49
• 2．8．2　在表空间之间迁移对象 　　49
• 2．9　禁止的行为 　　49
• 2．9．1　切记不要删除 PostgreSQL 系统文件 　　50
• 2．9．2　不要把操作系统管理员权限授予 PostgreSQL 的系统账号 　　50
• 2．9．3　不要把 shared_buffers 缓存区设置得过大 　　51
• 2．9．4　不要将 PostgreSQL 服务器的侦听端口设为一个已被其他程序占用的端口 　　51

第 3 章　psql 工具 　　52

• 3．1　环境变量 　　52
• 3．2　psql 的两种操作模式：交互模式与非交互模式 　　 53
• 3．3　定制 psql 操作环境 　　54
• 3．3．1　自定义 psql 界面提示符 　　55
• 3．3．2　语句执行时间统计 　　56
• 3．3．3　事务自动提交 　　56
• 3．3．4　命令别名 　　56
• 3．3．5　取出前面执行过的命令行 　　57
• 3．4　psql 使用技巧 　　57
• 3．4．1　执行 shell 命令 　　57
• 3．4．2　用 watch 命令重复执行语句 　　57
• 3．4．3　显示对象信息 　　58
• 3．4．4　行转列视图 　　59
• 3．4．5　执行动态 SQL 　　59
• 3．5　使用 psql 实现数据的导入和导出 　　60
• 3．5．1　使用 psql 进行数据导入 　　60
• 3．5．2　使用 psql 进行数据导出 　　61
• 3．5．3　从外部程序复制数据以及将数据复制到外部程序 　　62
• 3．6　使用 psql 制作简单的报表 　　62

第 4 章　pgAdmin 的使用　　65

• 4．1　pgAdmin 入门 　　65
• 4．1．1　功能概览 　　66
• 4．1．2　如何连接到 PostgreSQL 服务器 　　67
• 4．1．3　pgAdmin 界面导航 　　68
• 4．2　pgAdmin 功能特性介绍 　　68
• 4．2．1　根据表定义自动生成 SQL 语句 　　69
• 4．2．2　在 pgAdmin3 中调用 psql 　　69
• 4．2．3　在 pgAdmin3 中编辑 postgresql．conf 和 pg_hba．conf 文件 　　70
• 4．2．4　创建数据库对象并设置权限 　　70
• 4．2．5　数据导入和导出 　　73
• 4．2．6　备份与恢复 　　74
• 4．3　pgScript 脚本机制 　　78
• 4．4　以图形化方式解释执行计划 　　80
• 4．5　使用 pgAgent 执行定时任务 　　81
• 4．5．1　安装 pgAgent 　　81
• 4．5．2　规划定时任务 　　82
• 4．5．3　一些有用的 pgAgent 相关查询语句 　　84

第 5 章　数据类型 　　85

• 5．1　数值类型 　　85
• 5．1．1　serial 类型 　　85
• 5．1．2　生成数组序列的函数 　　86
• 5．2　文本类型 　　87
• 5．2．1　字符串函数 　　87
• 5．2．2　将字符串拆分为数组、表或者子字符串 　　88
• 5．2．3　正则表达式和模式匹配 　　89
• 5．3　时间类型 　　90
• 5．3．1　时区详解 　　92
• 5．3．2　日期时间类型的运算符和函数 　　93
• 5．4　数组类型 　　95
• 5．4．1　数组构造函数 　　96
• 5．4．2　将数组元素展开为记录行 　　97
• 5．4．3　数组的拆分与连接 　　98
• 5．4．4　引用数组中的元素 　　99
• 5．4．5　数组包含性检查 　　99
• 5．5　区间类型 　　100
• 5．5．1　离散区间和连续区间 　　100
• 5．5．2　原生支持的区间类型 　　100
• 5．5．3　定义区间的方法 　　101
• 5．5．4　定义含区间类型字段的表 　　102
• 5．5．5　适用于区间类型的运算符 　　102
• 5．6　JSON 数据类型 　　103
• 5．6．1　插入 JSON 数据 　　103
• 5．6．2　查询 JSON 数据 　　104
• 5．6．3　输出 JSON 数据 　　105
• 5．6．4　JSON 类型的二进制版本：jsonb 　　106
• 5．6．5　编辑 JSONB 类型的数据 　　108
• 5．7　XML 数据类型 　　109
• 5．7．1　插入 XML 数据　　110
• 5．7．2　查询 XML 数据　　110
• 5．8　全文检索 　　112
• 5．8．1　FTS 配置库　　113
• 5．8．2　TSVector 原始文本向量 　　115
• 5．8．3　TSQueries 检索条件向量 　　117
• 5．8．4　使用全文检索 　　119
• 5．8．5　对检索结果进行排序 　　120
• 5．8．6　全文检索向量信息的裁减 　　121
• 5．8．7　全文检索机制对 JSON 和 JSONB 数据类型的支持 　　122
• 5．9　自定义数据类型和复合数据类型 　　122
• 5．9．1　所有表都有一个对应的自定义数据类型 　　123
• 5．9．2　构建自定义数据类型 　　124
• 5．9．3　复合类型中的空值处理 　　124
• 5．9．4　为自定义数据类型构建运算符和函数 　　125

第 6 章　表、约束和索引 　　126

• 6．1　表 　　126
• 6．1．1　基本的建表操作 　　126
• 6．1．2　继承表 　　128
• 6．1．3　原生分区表支持 　　128
• 6．1．4　无日志表 　　130
• 6．1．5　TYPE OF 　　131
• 6．2　约束机制 　　132
• 6．2．1　外键约束 　　132
• 6．2．2　唯一性约束 　　133
• 6．2．3　check 约束 　　133
• 6．2．4　排他性约束 　　133
• 6．3　索引 　　134
• 6．3．1　PostgreSQL 原生支持的索引类型 　　135
• 6．3．2　运算符类 　　137
• 6．3．3　函数索引 　　138
• 6．3．4　基于部分记录的索引 　　138
• 6．3．5　多列索引 　　140

第 7 章　PostgreSQL 的特色 SQL 语法 　　141

• 7．1　视图 　　141
• 7．1．1　单表视图 　　142
• 7．1．2　使用触发器来更新视图 　　143
• 7．1．3　物化视图 　　145
• 7．2　灵活易用的 PostgreSQL 专有 SQL 语法 　　146
• 7．2．1　DISTINCT ON 　　147
• 7．2．2　LIMIT 和 OFFSET 关键字 　　147
• 7．2．3　简化的类型转换语法 　　148
• 7．2．4　一次性插入多条记录 　　148
• 7．2．5　使用 ILIKE 实现不区分大小写的查询 　　148
• 7．2．6　使用 ANY 运算符进行数组搜索 　　149
• 7．2．7　可以返回结果集的函数 　　149
• 7．2．8　限制对继承表的 DELETE、UPDATE、INSERT 操作的影响范围 　　150
• 7．2．9　DELETE USING 语法 　　150
• 7．2．10　将修改影响到的记录行返回给用户 　　150
• 7．2．11　UPSERT：INSERT 时如果主键冲突则进行 UPDATE 　　151
• 7．2．12　在查询中使用复合数据类型 　　152
• 7．2．13　使用 $ 文本引用符 　　153
• 7．2．14　DO 　　154
• 7．2．15　适用于聚合操作的 FILTER 子句 　　155
• 7．2．16　查询百分位数与最高出现频率数 　　157
• 7．3　窗口函数 　　158
• 7．3．1　PARTITION BY 子句 　　159
• 7．3．2　ORDER BY 子句 　　160
• 7．4　CTE 表达式 　　162
• 7．4．1　基本 CTE 用法介绍 　　162
• 7．4．2　可写 CTE 用法介绍 　　163
• 7．4．3　递归 CTE 用法介绍 　　163
• 7．5　LATERAL 横向关联语法 　　165
• 7．6　WITH ORDINALITY 子句 　　166
• 7．7　GROUPING SETS、CUBE 和 ROLLUP 语法 　　168

第 8 章　函数编写 　　171

• 8．1　PostgreSQL 函数功能剖析 　　171
• 8．1．1　函数功能基础知识介绍 　　171
• 8．1．2　触发器和触发器函数 　　174
• 8．1．3　聚合操作 　　175
• 8．1．4　受信与非受信语言 　　176
• 8．2　使用 SQL 语言来编写函数 　　177
• 8．2．1　编写基本的 SQL 函数 　　177
• 8．2．2　使用 SQL 语言编写聚合函数 　　179
• 8．3　使用 PL/pgSQL 语言编写函数　　181
• 8．3．1　编写基础的 PL/pgSQL 函数 　　181
• 8．3．2　使用 PL/pgSQL 编写触发器函数 　　181
• 8．4　使用 PL/Python 语言编写函数 　　182
• 8．5　使用 PL/V8、PL/CoffeeScript 以及 PL/LiveScript 语言来编写函数 　　184
• 8．5．1　编写基本的函数 　　185
• 8．5．2　使用 PL/V8 来编写聚合函数 　　186
• 8．5．3　使用 PL/V8 编写窗口函数 　　187

第 9 章　查询性能调优 　　190

• 9．1　通过 EXPLAIN 命令查看语句执行计划 　　190
• 9．1．1　EXPLAIN 选项 　　190
• 9．1．2　运行示例以及输出内容解释 　　191
• 9．1．3　图形化展示执行计划 　　193
• 9．2　搜集语句的执行统计信息 　　195
• 9．3　编写更好的 SQL 语句 　　196
• 9．3．1　在 SELECT 语句中滥用子查询 　　196
• 9．3．2　尽量避免使用 SELECT * 语法 　　198
• 9．3．3　善用 CASE 语法 　　199
• 9．3．4　使用 Filter 语法替代 CASE 语法 　　200
• 9．4　并行化语句执行 　　201
• 9．4．1　并行化的执行计划是什么样子 　　201
• 9．4．2　并行化扫描 　　204
• 9．4．3　并行化关联操作 　　205
• 9．5　人工干预规划器生成执行计划的过程 　　205
• 9．5．1　策略设置 　　205
• 9．5．2　你的索引被用到了吗 　　206
• 9．5．3　表的统计信息 　　207
• 9．5．4　磁盘页的随机访问成本以及磁盘驱动器的性能 　　209
• 9．6　数据缓存机制 　　209

第 10 章　复制与外部数据 　　211

• 10．1　复制功能概览 　　211
• 10．1．1　复制功能涉及的术语 　　212
• 10．1．2　复制机制的演进 　　214
• 10．1．3　第三方复制解决方案 　　214
• 10．2　复制环境的搭建 　　215
• 10．2．1　主服务器的配置 　　215
• 10．2．2　为从属服务器配置全量复制环境 　　216
• 10．2．3　启动流复制进程 　　217
• 10．2．4　使用逻辑复制实现部分表或者部分 database 的复制 　　218
• 10．3　外部数据封装器 　　219
• 10．3．1　查询平面文件 　　220
• 10．3．2　以不规则数组的形式查询不规范的平面文件 　　220
• 10．3．3　查询其他 PostgreSQL 服务实例上的数据 　　222
• 10．3．4　使用 ogr_fdw 查询其他二维表形式的数据源 　　224
• 10．3．5　查询非传统数据源 　　226
• 附录 A　PostgreSQL 的安装 　　228
• 附录 B　PostgreSQL 自带的命令行工具 　　233
• 作者简介 　　242
• 封面介绍 　　242