: Network Disk Download
Content Profile ...
This book will help you understand and use the PostgreSQL open source database system. Not only will you learn about enterprise features in versions 9.2, 9.3, and 9.4, but you'll also find that PostgreSQL is not just a database system, but also an excellent application platform. This book shows examples of how to implement tasks that are difficult or impossible to accomplish in other databases. This edition covers lateral queries, enhanced JSON support, materialized views, and other key topics.
about the author· · · · · ·
Regina Obe
is one of the principals of Paragon, a database consulting firm, with more than 15 years of professional experience in programming languages and database systems. She is a member of the PostGIS Steering Committee and a member of the PostGIS core development team. PostGIS in Action Book co-author.
Leo Hsu
Paragon is also the head of the company, has developed a database for large and small organizations, has more than 15 years of professional experience. PostGIS in Action Book co-author.
CataloguePreface XI
1th. Basic Knowledge 1
1.1 How to get PostgreSQL 1
1.2 Management Tools 1
1.2.1 Psql 2
1.2.2 Pgadmin 2
1.2.3 Phppgadmin 3
1.2.4 Adminer 3
1.3 PostgreSQL Database Object 4
1.4 new features introduced in the latest version of PostgreSQL 9
1.4.1 Why upgrade 10
New features introduced in 1.4.2 PostgreSQL version 9.4 10
1.4.3 PostgreSQL version 9.3 new features list 12
1.4.4 PostgreSQL version 9.2 new features list 13
1.4.5 PostgreSQL Version 9.1 new features list 14
1.5 Database Drivers 14
1.6 How to get help 15
Major derivative version of 1.7 PostgreSQL 15
The 2nd chapter of database Management 17
2.1 Configuration File 17
2.1.1 Postgresql.conf 18
2.1.2 Pg_hba.conf 21
2.1.3 Configuration file Reload 23
2.2 Connection Management 24
2.3 Characters 25
2.3.1 to create a login role 26
2.3.2 Creating a group role 26
2.4 Creating Database 28
2.4.1 Template Database 28
Use of the 2.4.2 schema 29
2.5 Rights Management 31
2.5.1 Types of Permissions 31
2.5.2 Introduction 31
2.5.3 GRANT 32
2.5.4 Default Permissions 33
Some distinctive features of the 2.5.5 PostgreSQL privilege System 34
2.6 expansion pack Mechanism 34
2.6.1 Installation of expansion packs 36
2.6.2 Universal Expansion Pack 38
2.7 Backup and Recovery 40
2.7.1 using Pg_dump for selective backups 41
2.7.2 using Pg_dumpall for full-Library backup 42
2.7.3 Data Recovery 43
2.8 Storage Management based on tablespace mechanism 45
2.8.1 Creating a table space 45
2.8.2 migrating objects between tablespaces 45
2.9 Prohibited Behavior 46
2.9.1 remember not to delete PostgreSQL system files 46
2.9.2 do not grant the OS administrator privileges to PostgreSQL's system account (Postgres) 47
2.9.3 do not set the shared_buffers buffer too large 47
2.9.4 do not set the PostgreSQL server's listening port to a port 47 already occupied by another program
3rd Chapter Psql Tools 49
3.1 Environment Variables 49
3.2 Psql Two modes of operation: interactive mode and non-interactive mode 50
3.3 Customizing the Psql Operating environment 51
3.3.1 Customizing the Psql interface prompt 52
3.3.2 Statement execution Time Statistics 53
3.3.3 Transaction Auto-Commit 53
3.3.4 Command Alias 54
3.3.5 remove the previously executed command line 54
3.4 Psql Use Tips 55
3.4.1 Execute shell Command 55
3.4.2 repeating statements with the Watch command 55
3.4.3 Displaying object information 55
3.5 Importing and exporting data using Psql 56
3.5.1 using Psql for data import 56
3.5.2 using Psql for data export 58
3.5.3 copying data from external programs and copying data to external programs 58
3.6 Making a simple report using Psql 58
Chapter 4th use of Pgadmin 61
4.1 Pgadmin Primer 61
4.1.1 Features Overview 61
4.1.2 How to connect to a PostgreSQL server 63
4.1.3 Pgadmin Interface Navigation 63
4.2 Pgadmin Functional Characteristics Introduction 64
4.2.1 calls Psql in Pgadmin 65
4.2.2 Editing postgresql.conf and pg_hba.conf files in Pgadmin 65
4.2.3 Creating a database Asset and setting permissions 66
4.2.4 data import and export 68
4.2.5 Backup and Recovery 69
4.3 pgscript scripting Mechanism 72
4.4 Graphical interpretation of execution Plan 75
4.5 using Pgagent to perform scheduled Tasks 75
4.5.1 Mounting Pgagent 76
4.5.2 Planning Scheduled Tasks 76
4.5.3 some useful pgagent-related query statements 78
Chapter 5th data Type 80
5.1 Value Type 80
5.1.1 Serial Type 81
5.1.2 function to generate array sequence 81
5.2 Characters and Strings 82
5.2.1 String Function 83
5.2.2 splitting a string into an array, table, or substring 83
5.2.3 Regular expressions and pattern matching 84
5.3 Time Type 85
5.3.1 time Zone detailed 87
5.3.2 operators and functions for date-time types 89
5.4 Array Type 91
5.4.1 Array Constructor 92
5.4.2 referencing an element in an array 93
Splitting and connecting a 5.4.3 array 93
5.4.4 to expand an array element to a record line 93
5.5 Interval Type 94
5.5.1 discrete interval and continuous interval 95
5.5.2 Native support interval Type 95
5.5.3 method of defining intervals 96
5.5.4 defining a table with interval type fields 97
5.5.5 operators for interval types 97
5.6 JSON data Type 98
5.6.1 inserting JSON Data 98
5.6.2 Querying JSON data 99
5.6.3 Output JSON Data 101
5.6.4 binary version of JSON type: JSONB 101
5.7 XML Data Type 103
5.7.1 inserting XML Data 103
5.7.2 Querying XML Data 104
5.8 Custom data types and composite data types 105
5.8.1 all tables have a corresponding custom data type 105
5.8.2 Building Custom data Types 107
5.8.3 building operators and functions for custom data Types 107
6th. Table, constraints and Index 109
6.1 Table 109
6.1.1 Basic table-building Operations 109
6.1.2 Inheritance Table 110
6.1.3 No log table 110
6.1.4 TYPE of 111
6.2 Constraint Mechanism 112
6.2.1 FOREIGN KEY constraint 112
6.2.2 Uniqueness Constraint 113
6.2.3 Check Constraint 113
6.2.4 Exclusivity constraint 113
6.3 Index 114
6.3.1 PostgreSQL Native supported index type 115
6.3.2 Operator Class 116
6.3.3 Function Index 118
6.3.4 index based on partial records 118
6.3.5 Multi-column index 119
Chapter 7th features of PostgreSQL SQL syntax 121
7.1 Views 121
7.1.1 Single-table View 122
7.1.2 using triggers to update views 123
7.1.3 materialized View 125
7.2 Flexible and easy to use PostgreSQL proprietary SQL Syntax 127
7.2.1 DISTINCT on 127
7.2.2 limit and offset keyword 128
7.2.3 Simplified type conversion Syntax 128
7.2.4 inserting multiple records at once 128
7.2.5 using ilike to implement case-insensitive queries 129
7.2.6 functions that can return a result set 129
7.2.7 limits the scope of the delete, UPDATE, and insert operations on an inherited table 130
7.2.8 DELETE using Syntax 130
7.2.9 returns the record rows affected by the modification to the user 130
7.2.10 using composite data types in queries 131
7.2.11 do 132
7.3 Filter clause for aggregation operations 133
7.4 Window Functions 135
7.4.1 PARTITION BY clause 136
7.4.2 ORDER BY clause 136
7.5 CTE Expression 138
7.5.1 Basic CTE Usage Introduction 139
7.5.2-Writable CTE usage Introduction 140
7.5.3 Recursive CTE Usage Introduction 140
7.6 Lateral Horizontal Association Syntax 141
The 8th Chapter function Writing 144
8.1 PostgreSQL Function Profiling 145
8.1.1 function Basic Knowledge Introduction 145
8.1.2 trigger and Trigger functions 146
8.1.3 Aggregation Operations 147
8.1.4 Trusted and non-trusted languages 149
8.2 Using the SQL language to write a function 149
8.2.1 writing basic SQL Functions 150
8.2.2 using SQL language to write aggregate functions 151
8.3 Writing functions using the Pl/pgsql language 153
8.3.1 writing the underlying Pl/pgsql function 153
8.3.2 writing trigger functions using Pl/pgsql 154
8.4 Writing functions using the Pl/python language 155
8.5 Writing functions using PL/V8, Pl/coffeescript, and Pl/livescript languages 157
8.5.1 Writing basic Functions 159
8.5.2 using PL/V8 to write aggregate functions 160
9th. Query Performance Tuning 162
9.1 View statement execution plan by explain command 162
9.1.1 Explain option 162
9.1.2 running the sample and interpreting the output content 163
9.1.3 graphical presentation of execution Plan 166
9.2 Execution statistics for collection statements 167
9.3 Manual Intervention Planner process for generating execution plans 168
9.3.1 Policy Settings 168
9.3.2 your index is used? 169
9.3.3 Statistics for Tables 170
9.3.4 random access cost for disk pages and disk drive performance 171
9.4 Data caching Mechanism 172
9.5 Writing a better SQL statement 173
9.5.1 Misuse of subqueries in SELECT statements 174
9.5.2 try to avoid using SELECT * Syntax 176
9.5.3 use case Syntax 177
9.5.4 using the filter syntax instead of case syntax 178
10th copy and external Data 180
10.1 Replication features Overview 180
Terminology involved in the 10.1.1 replication feature 181
Evolution of the 10.1.2 replication mechanism 182
10.1.3 third-party replication Solutions 182
10.2 Building a replication environment 183
10.2.1 configuration of the primary server 183
10.2.2 configuration of subordinate servers 184
10.2.3 Starting the replication process 185
10.3 External Data Wrapper 186
10.3.1 Querying flat Files 186
10.3.2 querying nonstandard flat files in the form of an irregular array 187
10.3.3 querying data on other PostgreSQL service instances 188
10.3.4 querying non-traditional data sources 190
Appendix A, installation of PostgreSQL 192
Appendix B PostgreSQL comes with command-line tools 196
About the author 204
Cover Introduction 204
: Network Disk Download
PostgreSQL is ready to learn (2nd edition) pdf
