PostgreSQL 即學即用, 3/e

本書將幫助你理解和使用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