PostgreSQL 9.1 Documentation

2836 Pages·2016·6.14 MB·English

PostgreSQL 9.1.24 Documentation
The PostgreSQL Global Development Group
PostgreSQL 9.1.24 Documentation
by The PostgreSQL Global Development Group
Copyright © 1996-2016 The PostgreSQL Global Development Group
Legal Notice
PostgreSQL is Copyright © 1996-2016 by the PostgreSQL Global Development Group and is distributed under the terms of the license of the
University of California below.
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, INCI-
DENTAL, 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 IM-
PLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HERE-
UNDER IS ON AN “AS-IS” BASIS, AND THE UNIVERSITY OF CALIFORNIA HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE,
SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.
Table of Contents
Preface .................................................................................................................................................... lxii
1. What is PostgreSQL? ................................................................................................................. lxii
2. A Brief History of PostgreSQL................................................................................................. lxiii
2.1. The Berkeley POSTGRES Project ............................................................................... lxiii
2.2. Postgres95......................................................................................................................lxiv
2.3. PostgreSQL....................................................................................................................lxiv
3. Conventions.................................................................................................................................lxv
4. Further Information.....................................................................................................................lxv
5. Bug Reporting Guidelines......................................................................................................... lxvi
5.1. Identifying Bugs ........................................................................................................... lxvi
5.2. What to Report ............................................................................................................ lxvii
5.3. Where to Report Bugs .................................................................................................. lxix
I. Tutorial ....................................................................................................................................................1
1. Getting Started ...............................................................................................................................1
1.1. Installation .........................................................................................................................1
1.2. Architectural Fundamentals...............................................................................................1
1.3. Creating a Database...........................................................................................................2
1.4. Accessing a Database ........................................................................................................3
2. The SQL Language ........................................................................................................................6
2.1. Introduction .......................................................................................................................6
2.2. Concepts ............................................................................................................................6
2.3. Creating a New Table ........................................................................................................6
2.4. Populating a Table With Rows ..........................................................................................7
2.5. Querying a Table ...............................................................................................................8
2.6. Joins Between Tables.......................................................................................................10
2.7. Aggregate Functions........................................................................................................12
2.8. Updates ............................................................................................................................14
2.9. Deletions..........................................................................................................................14
3. Advanced Features .......................................................................................................................16
3.1. Introduction .....................................................................................................................16
3.2. Views ...............................................................................................................................16
3.3. Foreign Keys....................................................................................................................16
3.4. Transactions.....................................................................................................................17
3.5. Window Functions...........................................................................................................19
3.6. Inheritance .......................................................................................................................22
3.7. Conclusion.......................................................................................................................24
II. The SQL Language.............................................................................................................................25
4. SQL Syntax ..................................................................................................................................27
4.1. Lexical Structure..............................................................................................................27
4.1.1. Identifiers and Key Words...................................................................................27
4.1.2. Constants.............................................................................................................29
4.1.2.1. String Constants .....................................................................................29
4.1.2.2. String Constants with C-style Escapes...................................................29
4.1.2.3. String Constants with Unicode Escapes.................................................31
4.1.2.4. Dollar-quoted String Constants ..............................................................32
iii
4.1.2.5. Bit-string Constants................................................................................33
4.1.2.6. Numeric Constants .................................................................................33
4.1.2.7. Constants of Other Types .......................................................................33
4.1.3. Operators.............................................................................................................34
4.1.4. Special Characters...............................................................................................35
4.1.5. Comments ...........................................................................................................35
4.1.6. Operator Precedence ...........................................................................................36
4.2. Value Expressions............................................................................................................37
4.2.1. Column References.............................................................................................38
4.2.2. Positional Parameters..........................................................................................38
4.2.3. Subscripts............................................................................................................38
4.2.4. Field Selection ....................................................................................................39
4.2.5. Operator Invocations...........................................................................................40
4.2.6. Function Calls .....................................................................................................40
4.2.7. Aggregate Expressions........................................................................................40
4.2.8. Window Function Calls.......................................................................................42
4.2.9. Type Casts ...........................................................................................................43
4.2.10. Collation Expressions .......................................................................................44
4.2.11. Scalar Subqueries..............................................................................................45
4.2.12. Array Constructors............................................................................................45
4.2.13. Row Constructors..............................................................................................47
4.2.14. Expression Evaluation Rules ............................................................................48
4.3. Calling Functions.............................................................................................................50
4.3.1. Using Positional Notation ...................................................................................50
4.3.2. Using Named Notation .......................................................................................51
4.3.3. Using Mixed Notation.........................................................................................51
5. Data Definition .............................................................................................................................53
5.1. Table Basics.....................................................................................................................53
5.2. Default Values .................................................................................................................54
5.3. Constraints .......................................................................................................................55
5.3.1. Check Constraints ...............................................................................................55
5.3.2. Not-Null Constraints...........................................................................................57
5.3.3. Unique Constraints..............................................................................................58
5.3.4. Primary Keys.......................................................................................................59
5.3.5. Foreign Keys .......................................................................................................60
5.3.6. Exclusion Constraints .........................................................................................62
5.4. System Columns..............................................................................................................63
5.5. Modifying Tables.............................................................................................................64
5.5.1. Adding a Column................................................................................................65
5.5.2. Removing a Column ...........................................................................................65
5.5.3. Adding a Constraint ............................................................................................65
5.5.4. Removing a Constraint .......................................................................................66
5.5.5. Changing a Column’s Default Value...................................................................66
5.5.6. Changing a Column’s Data Type ........................................................................67
5.5.7. Renaming a Column ...........................................................................................67
5.5.8. Renaming a Table ...............................................................................................67
5.6. Privileges .........................................................................................................................67
5.7. Schemas...........................................................................................................................68
iv
5.7.1. Creating a Schema ..............................................................................................69
5.7.2. The Public Schema .............................................................................................70
5.7.3. The Schema Search Path.....................................................................................70
5.7.4. Schemas and Privileges.......................................................................................71
5.7.5. The System Catalog Schema ..............................................................................71
5.7.6. Usage Patterns.....................................................................................................72
5.7.7. Portability............................................................................................................72
5.8. Inheritance .......................................................................................................................73
5.8.1. Caveats ................................................................................................................76
5.9. Partitioning ......................................................................................................................76
5.9.1. Overview.............................................................................................................76
5.9.2. Implementing Partitioning ..................................................................................77
5.9.3. Managing Partitions ............................................................................................80
5.9.4. Partitioning and Constraint Exclusion ................................................................81
5.9.5. Alternative Partitioning Methods........................................................................82
5.9.6. Caveats ................................................................................................................83
5.10. Foreign Data ..................................................................................................................84
5.11. Other Database Objects .................................................................................................84
5.12. Dependency Tracking....................................................................................................85
6. Data Manipulation........................................................................................................................87
6.1. Inserting Data ..................................................................................................................87
6.2. Updating Data..................................................................................................................88
6.3. Deleting Data...................................................................................................................89
7. Queries .........................................................................................................................................90
7.1. Overview .........................................................................................................................90
7.2. Table Expressions ............................................................................................................90
7.2.1. The FROM Clause.................................................................................................91
7.2.1.1. Joined Tables ..........................................................................................91
7.2.1.2. Table and Column Aliases......................................................................95
7.2.1.3. Subqueries ..............................................................................................96
7.2.1.4. Table Functions ......................................................................................97
7.2.2. The WHERE Clause...............................................................................................97
7.2.3. The GROUP BY and HAVING Clauses..................................................................99
7.2.4. Window Function Processing ...........................................................................101
7.3. Select Lists.....................................................................................................................101
7.3.1. Select-List Items ...............................................................................................101
7.3.2. Column Labels ..................................................................................................102
7.3.3. DISTINCT .........................................................................................................103
7.4. Combining Queries........................................................................................................103
7.5. Sorting Rows .................................................................................................................104
7.6. LIMIT and OFFSET........................................................................................................105
7.7. VALUES Lists .................................................................................................................105
7.8. WITH Queries (Common Table Expressions) ................................................................106
7.8.1. SELECT in WITH................................................................................................106
7.8.2. Data-Modifying Statements in WITH ................................................................110
8. Data Types..................................................................................................................................113
8.1. Numeric Types...............................................................................................................114
8.1.1. Integer Types.....................................................................................................115
v
8.1.2. Arbitrary Precision Numbers ............................................................................115
8.1.3. Floating-Point Types .........................................................................................117
8.1.4. Serial Types.......................................................................................................118
8.2. Monetary Types .............................................................................................................119
8.3. Character Types .............................................................................................................120
8.4. Binary Data Types .........................................................................................................122
8.4.1. bytea Hex Format............................................................................................122
8.4.2. bytea Escape Format.......................................................................................123
8.5. Date/Time Types............................................................................................................124
8.5.1. Date/Time Input ................................................................................................126
8.5.1.1. Dates.....................................................................................................126
8.5.1.2. Times ....................................................................................................127
8.5.1.3. Time Stamps.........................................................................................128
8.5.1.4. Special Values ......................................................................................129
8.5.2. Date/Time Output .............................................................................................129
8.5.3. Time Zones .......................................................................................................130
8.5.4. Interval Input.....................................................................................................132
8.5.5. Interval Output ..................................................................................................134
8.5.6. Internals.............................................................................................................135
8.6. Boolean Type.................................................................................................................135
8.7. Enumerated Types .........................................................................................................136
8.7.1. Declaration of Enumerated Types.....................................................................136
8.7.2. Ordering ............................................................................................................136
8.7.3. Type Safety .......................................................................................................137
8.7.4. Implementation Details.....................................................................................138
8.8. Geometric Types............................................................................................................138
8.8.1. Points ................................................................................................................139
8.8.2. Line Segments...................................................................................................139
8.8.3. Boxes.................................................................................................................139
8.8.4. Paths..................................................................................................................139
8.8.5. Polygons............................................................................................................140
8.8.6. Circles ...............................................................................................................140
8.9. Network Address Types.................................................................................................140
8.9.1. inet..................................................................................................................141
8.9.2. cidr..................................................................................................................141
8.9.3. inet vs. cidr...................................................................................................142
8.9.4. macaddr ...........................................................................................................142
8.10. Bit String Types ...........................................................................................................143
8.11. Text Search Types........................................................................................................143
8.11.1. tsvector .......................................................................................................143
8.11.2. tsquery .........................................................................................................145
8.12. UUID Type ..................................................................................................................146
8.13. XML Type ...................................................................................................................147
8.13.1. Creating XML Values .....................................................................................147
8.13.2. Encoding Handling .........................................................................................148
8.13.3. Accessing XML Values...................................................................................149
8.14. Arrays ..........................................................................................................................149
8.14.1. Declaration of Array Types.............................................................................149
vi
8.14.2. Array Value Input............................................................................................150
8.14.3. Accessing Arrays ............................................................................................151
8.14.4. Modifying Arrays............................................................................................153
8.14.5. Searching in Arrays.........................................................................................156
8.14.6. Array Input and Output Syntax.......................................................................157
8.15. Composite Types .........................................................................................................158
8.15.1. Declaration of Composite Types.....................................................................158
8.15.2. Composite Value Input....................................................................................159
8.15.3. Accessing Composite Types ...........................................................................160
8.15.4. Modifying Composite Types...........................................................................161
8.15.5. Composite Type Input and Output Syntax......................................................161
8.16. Object Identifier Types ................................................................................................162
8.17. Pseudo-Types...............................................................................................................164
9. Functions and Operators ............................................................................................................166
9.1. Logical Operators ..........................................................................................................166
9.2. Comparison Operators...................................................................................................166
9.3. Mathematical Functions and Operators.........................................................................168
9.4. String Functions and Operators .....................................................................................172
9.5. Binary String Functions and Operators .........................................................................187
9.6. Bit String Functions and Operators ...............................................................................189
9.7. Pattern Matching ...........................................................................................................190
9.7.1. LIKE..................................................................................................................190
9.7.2. SIMILAR TO Regular Expressions ...................................................................191
9.7.3. POSIX Regular Expressions .............................................................................192
9.7.3.1. Regular Expression Details ..................................................................196
9.7.3.2. Bracket Expressions .............................................................................198
9.7.3.3. Regular Expression Escapes.................................................................199
9.7.3.4. Regular Expression Metasyntax...........................................................202
9.7.3.5. Regular Expression Matching Rules ....................................................203
9.7.3.6. Limits and Compatibility .....................................................................205
9.7.3.7. Basic Regular Expressions ...................................................................206
9.8. Data Type Formatting Functions ...................................................................................206
9.9. Date/Time Functions and Operators..............................................................................213
9.9.1. EXTRACT, date_part......................................................................................217
9.9.2. date_trunc.....................................................................................................221
9.9.3. AT TIME ZONE.................................................................................................222
9.9.4. Current Date/Time ............................................................................................223
9.9.5. Delaying Execution...........................................................................................224
9.10. Enum Support Functions .............................................................................................225
9.11. Geometric Functions and Operators ............................................................................226
9.12. Network Address Functions and Operators.................................................................230
9.13. Text Search Functions and Operators ..........................................................................232
9.14. XML Functions ...........................................................................................................236
9.14.1. Producing XML Content.................................................................................236
9.14.1.1. xmlcomment ......................................................................................236
9.14.1.2. xmlconcat ........................................................................................237
9.14.1.3. xmlelement ......................................................................................237
9.14.1.4. xmlforest ........................................................................................239
vii
9.14.1.5. xmlpi .................................................................................................239
9.14.1.6. xmlroot.............................................................................................240
9.14.1.7. xmlagg...............................................................................................240
9.14.2. XML Predicates ..............................................................................................241
9.14.2.1. IS DOCUMENT ....................................................................................241
9.14.2.2. XMLEXISTS ........................................................................................241
9.14.2.3. xml_is_well_formed .....................................................................242
9.14.3. Processing XML .............................................................................................243
9.14.4. Mapping Tables to XML.................................................................................244
9.15. Sequence Manipulation Functions ..............................................................................247
9.16. Conditional Expressions..............................................................................................250
9.16.1. CASE................................................................................................................250
9.16.2. COALESCE .......................................................................................................252
9.16.3. NULLIF............................................................................................................252
9.16.4. GREATEST and LEAST.....................................................................................252
9.17. Array Functions and Operators ...................................................................................253
9.18. Aggregate Functions....................................................................................................255
9.19. Window Functions.......................................................................................................259
9.20. Subquery Expressions .................................................................................................261
9.20.1. EXISTS............................................................................................................261
9.20.2. IN ....................................................................................................................261
9.20.3. NOT IN............................................................................................................262
9.20.4. ANY/SOME ........................................................................................................263
9.20.5. ALL ..................................................................................................................263
9.20.6. Row-wise Comparison....................................................................................264
9.21. Row and Array Comparisons ......................................................................................264
9.21.1. IN ....................................................................................................................264
9.21.2. NOT IN............................................................................................................265
9.21.3. ANY/SOME (array) ............................................................................................265
9.21.4. ALL (array) ......................................................................................................265
9.21.5. Row-wise Comparison....................................................................................266
9.22. Set Returning Functions ..............................................................................................267
9.23. System Information Functions ....................................................................................269
9.24. System Administration Functions ...............................................................................280
9.25. Trigger Functions ........................................................................................................289
10. Type Conversion.......................................................................................................................291
10.1. Overview .....................................................................................................................291
10.2. Operators .....................................................................................................................292
10.3. Functions .....................................................................................................................296
10.4. Value Storage...............................................................................................................299
10.5. UNION, CASE, and Related Constructs.........................................................................300
11. Indexes .....................................................................................................................................302
11.1. Introduction .................................................................................................................302
11.2. Index Types..................................................................................................................303
11.3. Multicolumn Indexes...................................................................................................305
11.4. Indexes and ORDER BY................................................................................................306
11.5. Combining Multiple Indexes .......................................................................................306
11.6. Unique Indexes ............................................................................................................307
viii
11.7. Indexes on Expressions ...............................................................................................308
11.8. Partial Indexes .............................................................................................................308
11.9. Operator Classes and Operator Families .....................................................................311
11.10. Indexes and Collations...............................................................................................312
11.11. Examining Index Usage.............................................................................................313
12. Full Text Search .......................................................................................................................315
12.1. Introduction .................................................................................................................315
12.1.1. What Is a Document?......................................................................................316
12.1.2. Basic Text Matching .......................................................................................317
12.1.3. Configurations.................................................................................................318
12.2. Tables and Indexes.......................................................................................................318
12.2.1. Searching a Table ............................................................................................318
12.2.2. Creating Indexes .............................................................................................319
12.3. Controlling Text Search...............................................................................................320
12.3.1. Parsing Documents .........................................................................................321
12.3.2. Parsing Queries ...............................................................................................322
12.3.3. Ranking Search Results ..................................................................................323
12.3.4. Highlighting Results .......................................................................................325
12.4. Additional Features .....................................................................................................327
12.4.1. Manipulating Documents................................................................................327
12.4.2. Manipulating Queries......................................................................................328
12.4.2.1. Query Rewriting .................................................................................328
12.4.3. Triggers for Automatic Updates .....................................................................330
12.4.4. Gathering Document Statistics .......................................................................331
12.5. Parsers..........................................................................................................................332
12.6. Dictionaries..................................................................................................................334
12.6.1. Stop Words......................................................................................................335
12.6.2. Simple Dictionary ...........................................................................................336
12.6.3. Synonym Dictionary .......................................................................................337
12.6.4. Thesaurus Dictionary ......................................................................................339
12.6.4.1. Thesaurus Configuration ....................................................................340
12.6.4.2. Thesaurus Example ............................................................................340
12.6.5. Ispell Dictionary..............................................................................................341
12.6.6. Snowball Dictionary .......................................................................................342
12.7. Configuration Example................................................................................................343
12.8. Testing and Debugging Text Search ............................................................................344
12.8.1. Configuration Testing......................................................................................345
12.8.2. Parser Testing..................................................................................................347
12.8.3. Dictionary Testing...........................................................................................348
12.9. GiST and GIN Index Types .........................................................................................349
12.10. psql Support...............................................................................................................350
12.11. Limitations.................................................................................................................353
12.12. Migration from Pre-8.3 Text Search..........................................................................353
13. Concurrency Control ................................................................................................................355
13.1. Introduction .................................................................................................................355
13.2. Transaction Isolation ...................................................................................................355
13.2.1. Read Committed Isolation Level ....................................................................356
13.2.2. Repeatable Read Isolation Level.....................................................................357
ix
13.2.3. Serializable Isolation Level.............................................................................358
13.3. Explicit Locking ..........................................................................................................360
13.3.1. Table-level Locks ............................................................................................361
13.3.2. Row-level Locks .............................................................................................363
13.3.3. Deadlocks........................................................................................................364
13.3.4. Advisory Locks...............................................................................................365
13.4. Data Consistency Checks at the Application Level.....................................................366
13.4.1. Enforcing Consistency With Serializable Transactions ..................................366
13.4.2. Enforcing Consistency With Explicit Blocking Locks ...................................367
13.5. Caveats.........................................................................................................................367
13.6. Locking and Indexes....................................................................................................368
14. Performance Tips .....................................................................................................................369
14.1. Using EXPLAIN ...........................................................................................................369
14.2. Statistics Used by the Planner .....................................................................................374
14.3. Controlling the Planner with Explicit JOIN Clauses...................................................375
14.4. Populating a Database .................................................................................................377
14.4.1. Disable Autocommit .......................................................................................377
14.4.2. Use COPY.........................................................................................................378
14.4.3. Remove Indexes ..............................................................................................378
14.4.4. Remove Foreign Key Constraints ...................................................................378
14.4.5. Increase maintenance_work_mem ...............................................................379
14.4.6. Increase checkpoint_segments .................................................................379
14.4.7. Disable WAL Archival and Streaming Replication ........................................379
14.4.8. Run ANALYZE Afterwards...............................................................................379
14.4.9. Some Notes About pg_dump..........................................................................380
14.5. Non-Durable Settings ..................................................................................................380
III. Server Administration ....................................................................................................................382
15. Installation from Source Code .................................................................................................384
15.1. Short Version ...............................................................................................................384
15.2. Requirements...............................................................................................................384
15.3. Getting The Source......................................................................................................386
15.4. Installation Procedure..................................................................................................386
15.5. Post-Installation Setup.................................................................................................396
15.5.1. Shared Libraries ..............................................................................................396
15.5.2. Environment Variables ....................................................................................397
15.6. Supported Platforms ....................................................................................................398
15.7. Platform-specific Notes ...............................................................................................398
15.7.1. AIX .................................................................................................................399
15.7.1.1. GCC Issues.........................................................................................399
15.7.1.2. Unix-Domain Sockets Broken............................................................399
15.7.1.3. Internet Address Issues.......................................................................400
15.7.1.4. Memory Management ........................................................................400
References and Resources .......................................................................401
15.7.2. Cygwin............................................................................................................402
15.7.3. HP-UX ............................................................................................................403
15.7.4. IRIX ................................................................................................................404
15.7.5. MinGW/Native Windows ...............................................................................404
x