PostgreSQL 8.3 Documentation

2015 Pages·2013·4.54 MB·English

PostgreSQL 8.3.23 Documentation
The PostgreSQL Global Development Group
PostgreSQL 8.3.23 Documentation
by The PostgreSQL Global Development Group
Copyright © 1996-2013 The PostgreSQL Global Development Group
Legal Notice
PostgreSQL is Copyright © 1996-2013 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,
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 PRO-
VIDED HEREUNDER 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 .............................................................................................................................................xliv
1. What is PostgreSQL? ..........................................................................................................xliv
2. A Brief History of PostgreSQL............................................................................................xlv
2.1. The Berkeley POSTGRES Project ..........................................................................xlv
2.2. Postgres95................................................................................................................xlv
2.3. PostgreSQL.............................................................................................................xlvi
3. Conventions.........................................................................................................................xlvi
4. Further Information............................................................................................................xlvii
5. Bug Reporting Guidelines..................................................................................................xlvii
5.1. Identifying Bugs ...................................................................................................xlviii
5.2. What to report.......................................................................................................xlviii
5.3. Where to report bugs ................................................................................................... l
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 ..................................................................................................................... 13
2.9. Deletions................................................................................................................... 14
3. Advanced Features ................................................................................................................ 15
3.1. Introduction .............................................................................................................. 15
3.2. Views ........................................................................................................................ 15
3.3. Foreign Keys............................................................................................................. 15
3.4. Transactions.............................................................................................................. 16
3.5. Inheritance ................................................................................................................ 18
3.6. Conclusion................................................................................................................ 19
II. The SQL Language...................................................................................................................... 21
4. SQL Syntax ........................................................................................................................... 23
4.1. Lexical Structure....................................................................................................... 23
4.1.1. Identifiers and Key Words............................................................................ 23
4.1.2. Constants...................................................................................................... 24
4.1.2.1. String Constants .............................................................................. 24
4.1.2.2. Dollar-Quoted String Constants ...................................................... 25
4.1.2.3. Bit-String Constants ........................................................................ 26
4.1.2.4. Numeric Constants .......................................................................... 26
4.1.2.5. Constants of Other Types ................................................................ 27
4.1.3. Operators...................................................................................................... 28
4.1.4. Special Characters........................................................................................ 28
4.1.5. Comments .................................................................................................... 29
iii
4.1.6. Lexical Precedence ...................................................................................... 29
4.2. Value Expressions..................................................................................................... 30
4.2.1. Column References...................................................................................... 31
4.2.2. Positional Parameters................................................................................... 31
4.2.3. Subscripts..................................................................................................... 32
4.2.4. Field Selection ............................................................................................. 32
4.2.5. Operator Invocations.................................................................................... 33
4.2.6. Function Calls .............................................................................................. 33
4.2.7. Aggregate Expressions................................................................................. 33
4.2.8. Type Casts .................................................................................................... 34
4.2.9. Scalar Subqueries......................................................................................... 35
4.2.10. Array Constructors..................................................................................... 35
4.2.11. Row Constructors....................................................................................... 36
4.2.12. Expression Evaluation Rules ..................................................................... 38
5. Data Definition ...................................................................................................................... 39
5.1. Table Basics.............................................................................................................. 39
5.2. Default Values .......................................................................................................... 40
5.3. Constraints ................................................................................................................ 41
5.3.1. Check Constraints ........................................................................................ 41
5.3.2. Not-Null Constraints.................................................................................... 43
5.3.3. Unique Constraints....................................................................................... 44
5.3.4. Primary Keys................................................................................................ 44
5.3.5. Foreign Keys ................................................................................................ 45
5.4. System Columns....................................................................................................... 48
5.5. Modifying Tables...................................................................................................... 49
5.5.1. Adding a Column......................................................................................... 49
5.5.2. Removing a Column .................................................................................... 50
5.5.3. Adding a Constraint ..................................................................................... 50
5.5.4. Removing a Constraint ................................................................................ 51
5.5.5. Changing a Column’s Default Value............................................................ 51
5.5.6. Changing a Column’s Data Type ................................................................. 51
5.5.7. Renaming a Column .................................................................................... 52
5.5.8. Renaming a Table ........................................................................................ 52
5.6. Privileges .................................................................................................................. 52
5.7. Schemas.................................................................................................................... 53
5.7.1. Creating a Schema ....................................................................................... 53
5.7.2. The Public Schema ...................................................................................... 54
5.7.3. The Schema Search Path.............................................................................. 54
5.7.4. Schemas and Privileges................................................................................ 56
5.7.5. The System Catalog Schema ....................................................................... 56
5.7.6. Usage Patterns.............................................................................................. 56
5.7.7. Portability..................................................................................................... 57
5.8. Inheritance ................................................................................................................ 57
5.8.1. Caveats ......................................................................................................... 60
5.9. Partitioning ............................................................................................................... 60
5.9.1. Overview...................................................................................................... 60
5.9.2. Implementing Partitioning ........................................................................... 61
5.9.3. Managing Partitions ..................................................................................... 64
5.9.4. Partitioning and Constraint Exclusion ......................................................... 65
5.9.5. Alternative Partitioning Methods................................................................. 66
5.9.6. Caveats ......................................................................................................... 67
5.10. Other Database Objects .......................................................................................... 67
iv
5.11. Dependency Tracking............................................................................................. 68
6. Data Manipulation................................................................................................................. 69
6.1. Inserting Data ........................................................................................................... 69
6.2. Updating Data........................................................................................................... 70
6.3. Deleting Data............................................................................................................ 71
7. Queries .................................................................................................................................. 72
7.1. Overview .................................................................................................................. 72
7.2. Table Expressions ..................................................................................................... 72
7.2.1. The FROM Clause.......................................................................................... 73
7.2.1.1. Joined Tables ................................................................................... 73
7.2.1.2. Table and Column Aliases............................................................... 76
7.2.1.3. Subqueries ....................................................................................... 77
7.2.1.4. Table Functions ............................................................................... 78
7.2.2. The WHERE Clause........................................................................................ 78
7.2.3. The GROUP BY and HAVING Clauses........................................................... 79
7.3. Select Lists................................................................................................................ 82
7.3.1. Select-List Items .......................................................................................... 82
7.3.2. Column Labels ............................................................................................. 82
7.3.3. DISTINCT .................................................................................................... 83
7.4. Combining Queries................................................................................................... 83
7.5. Sorting Rows ............................................................................................................ 84
7.6. LIMIT and OFFSET................................................................................................... 85
7.7. VALUES Lists ............................................................................................................ 85
8. Data Types............................................................................................................................. 87
8.1. Numeric Types.......................................................................................................... 88
8.1.1. Integer Types................................................................................................ 89
8.1.2. Arbitrary Precision Numbers ....................................................................... 89
8.1.3. Floating-Point Types .................................................................................... 90
8.1.4. Serial Types.................................................................................................. 91
8.2. Monetary Types ........................................................................................................ 92
8.3. Character Types ........................................................................................................ 93
8.4. Binary Data Types .................................................................................................... 95
8.5. Date/Time Types....................................................................................................... 96
8.5.1. Date/Time Input ........................................................................................... 97
8.5.1.1. Dates................................................................................................ 98
8.5.1.2. Times ............................................................................................... 98
8.5.1.3. Time Stamps.................................................................................... 99
8.5.1.4. Intervals ......................................................................................... 100
8.5.1.5. Special Values ............................................................................... 101
8.5.2. Date/Time Output ...................................................................................... 101
8.5.3. Time Zones ................................................................................................ 102
8.5.4. Internals...................................................................................................... 104
8.6. Boolean Type.......................................................................................................... 104
8.7. Enumerated Types .................................................................................................. 105
8.7.1. Declaration of Enumerated Types.............................................................. 105
8.7.2. Ordering ..................................................................................................... 105
8.7.3. Type Safety ................................................................................................ 106
8.7.4. Implementation Details.............................................................................. 107
8.8. Geometric Types..................................................................................................... 107
8.8.1. Points ......................................................................................................... 107
8.8.2. Line Segments............................................................................................ 108
8.8.3. Boxes.......................................................................................................... 108
v
8.8.4. Paths........................................................................................................... 108
8.8.5. Polygons..................................................................................................... 108
8.8.6. Circles ........................................................................................................ 109
8.9. Network Address Types.......................................................................................... 109
8.9.1. inet........................................................................................................... 109
8.9.2. cidr........................................................................................................... 110
8.9.3. inet vs. cidr............................................................................................ 110
8.9.4. macaddr .................................................................................................... 111
8.10. Bit String Types .................................................................................................... 111
8.11. Text Search Types................................................................................................. 112
8.11.1. tsvector ................................................................................................ 112
8.11.2. tsquery .................................................................................................. 113
8.12. UUID Type ........................................................................................................... 114
8.13. XML Type ............................................................................................................ 114
8.13.1. Creating XML Values .............................................................................. 115
8.13.2. Encoding Handling .................................................................................. 115
8.13.3. Accessing XML Values............................................................................ 116
8.14. Arrays ................................................................................................................... 116
8.14.1. Declaration of Array Types...................................................................... 116
8.14.2. Array Value Input..................................................................................... 117
8.14.3. Accessing Arrays ..................................................................................... 118
8.14.4. Modifying Arrays..................................................................................... 120
8.14.5. Searching in Arrays.................................................................................. 122
8.14.6. Array Input and Output Syntax................................................................ 123
8.15. Composite Types .................................................................................................. 124
8.15.1. Declaration of Composite Types.............................................................. 124
8.15.2. Composite Value Input............................................................................. 125
8.15.3. Accessing Composite Types .................................................................... 126
8.15.4. Modifying Composite Types.................................................................... 127
8.15.5. Composite Type Input and Output Syntax............................................... 127
8.16. Object Identifier Types ......................................................................................... 128
8.17. Pseudo-Types........................................................................................................ 129
9. Functions and Operators ..................................................................................................... 131
9.1. Logical Operators ................................................................................................... 131
9.2. Comparison Operators............................................................................................ 131
9.3. Mathematical Functions and Operators.................................................................. 133
9.4. String Functions and Operators .............................................................................. 136
9.5. Binary String Functions and Operators .................................................................. 147
9.6. Bit String Functions and Operators ........................................................................ 149
9.7. Pattern Matching .................................................................................................... 150
9.7.1. LIKE........................................................................................................... 150
9.7.2. SIMILAR TO Regular Expressions ............................................................ 151
9.7.3. POSIX Regular Expressions ...................................................................... 152
9.7.3.1. Regular Expression Details ........................................................... 155
9.7.3.2. Bracket Expressions ...................................................................... 157
9.7.3.3. Regular Expression Escapes.......................................................... 158
9.7.3.4. Regular Expression Metasyntax.................................................... 161
9.7.3.5. Regular Expression Matching Rules ............................................. 162
9.7.3.6. Limits and Compatibility .............................................................. 163
9.7.3.7. Basic Regular Expressions ............................................................ 164
9.8. Data Type Formatting Functions ............................................................................ 164
9.9. Date/Time Functions and Operators....................................................................... 170
vi
9.9.1. EXTRACT, date_part............................................................................... 174
9.9.2. date_trunc.............................................................................................. 178
9.9.3. AT TIME ZONE.......................................................................................... 178
9.9.4. Current Date/Time ..................................................................................... 179
9.9.5. Delaying Execution.................................................................................... 181
9.10. Enum Support Functions ...................................................................................... 181
9.11. Geometric Functions and Operators ..................................................................... 182
9.12. Network Address Functions and Operators.......................................................... 186
9.13. Text Search Functions and Operators ................................................................... 188
9.14. XML Functions .................................................................................................... 192
9.14.1. Producing XML Content.......................................................................... 192
9.14.1.1. xmlcomment ............................................................................... 193
9.14.1.2. xmlconcat ................................................................................. 193
9.14.1.3. xmlelement ............................................................................... 194
9.14.1.4. xmlforest ................................................................................. 195
9.14.1.5. xmlpi .......................................................................................... 195
9.14.1.6. xmlroot...................................................................................... 196
9.14.1.7. xmlagg........................................................................................ 196
9.14.1.8. XML Predicates........................................................................... 197
9.14.2. Processing XML ...................................................................................... 197
9.14.3. Mapping Tables to XML.......................................................................... 197
9.15. Sequence Manipulation Functions ....................................................................... 201
9.16. Conditional Expressions....................................................................................... 203
9.16.1. CASE......................................................................................................... 203
9.16.2. COALESCE ................................................................................................ 204
9.16.3. NULLIF..................................................................................................... 205
9.16.4. GREATEST and LEAST.............................................................................. 205
9.17. Array Functions and Operators ............................................................................ 205
9.18. Aggregate Functions............................................................................................. 207
9.19. Subquery Expressions .......................................................................................... 210
9.19.1. EXISTS..................................................................................................... 210
9.19.2. IN ............................................................................................................. 211
9.19.3. NOT IN..................................................................................................... 211
9.19.4. ANY/SOME ................................................................................................. 212
9.19.5. ALL ........................................................................................................... 212
9.19.6. Row-wise Comparison............................................................................. 213
9.20. Row and Array Comparisons ............................................................................... 213
9.20.1. IN ............................................................................................................. 213
9.20.2. NOT IN..................................................................................................... 214
9.20.3. ANY/SOME (array) ..................................................................................... 214
9.20.4. ALL (array) ............................................................................................... 214
9.20.5. Row-wise Comparison............................................................................. 215
9.21. Set Returning Functions ....................................................................................... 215
9.22. System Information Functions ............................................................................. 216
9.23. System Administration Functions ........................................................................ 223
10. Type Conversion................................................................................................................ 230
10.1. Overview .............................................................................................................. 230
10.2. Operators .............................................................................................................. 231
10.3. Functions .............................................................................................................. 234
10.4. Value Storage........................................................................................................ 236
10.5. UNION, CASE, and Related Constructs.................................................................. 237
11. Indexes .............................................................................................................................. 240
vii
11.1. Introduction .......................................................................................................... 240
11.2. Index Types........................................................................................................... 241
11.3. Multicolumn Indexes............................................................................................ 242
11.4. Indexes and ORDER BY......................................................................................... 243
11.5. Combining Multiple Indexes ................................................................................ 244
11.6. Unique Indexes ..................................................................................................... 245
11.7. Indexes on Expressions ........................................................................................ 245
11.8. Partial Indexes ...................................................................................................... 246
11.9. Operator Classes and Operator Families .............................................................. 248
11.10. Examining Index Usage...................................................................................... 249
12. Full Text Search ................................................................................................................ 251
12.1. Introduction .......................................................................................................... 251
12.1.1. What Is a Document?............................................................................... 252
12.1.2. Basic Text Matching ................................................................................ 252
12.1.3. Configurations.......................................................................................... 253
12.2. Tables and Indexes................................................................................................ 254
12.2.1. Searching a Table ..................................................................................... 254
12.2.2. Creating Indexes ...................................................................................... 255
12.3. Controlling Text Search........................................................................................ 256
12.3.1. Parsing Documents .................................................................................. 256
12.3.2. Parsing Queries ........................................................................................ 257
12.3.3. Ranking Search Results ........................................................................... 258
12.3.4. Highlighting Results ................................................................................ 260
12.4. Additional Features .............................................................................................. 262
12.4.1. Manipulating Documents......................................................................... 262
12.4.2. Manipulating Queries............................................................................... 263
12.4.2.1. Query Rewriting .......................................................................... 264
12.4.3. Triggers for Automatic Updates .............................................................. 265
12.4.4. Gathering Document Statistics ................................................................ 266
12.5. Parsers................................................................................................................... 267
12.6. Dictionaries........................................................................................................... 268
12.6.1. Stop Words............................................................................................... 269
12.6.2. Simple Dictionary .................................................................................... 270
12.6.3. Synonym Dictionary ................................................................................ 271
12.6.4. Thesaurus Dictionary ............................................................................... 272
12.6.4.1. Thesaurus Configuration ............................................................. 273
12.6.4.2. Thesaurus Example ..................................................................... 273
12.6.5. Ispell Dictionary....................................................................................... 274
12.6.6. Snowball Dictionary ................................................................................ 275
12.7. Configuration Example......................................................................................... 276
12.8. Testing and Debugging Text Search ..................................................................... 277
12.8.1. Configuration Testing............................................................................... 277
12.8.2. Parser Testing........................................................................................... 279
12.8.3. Dictionary Testing.................................................................................... 281
12.9. GiST and GIN Index Types .................................................................................. 281
12.10. psql Support........................................................................................................ 283
12.11. Limitations.......................................................................................................... 286
12.12. Migration from Pre-8.3 Text Search................................................................... 286
13. Concurrency Control ......................................................................................................... 288
13.1. Introduction .......................................................................................................... 288
13.2. Transaction Isolation ............................................................................................ 288
13.2.1. Read Committed Isolation Level ............................................................. 289
viii
13.2.2. Serializable Isolation Level...................................................................... 290
13.2.2.1. Serializable Isolation versus True Serializability ........................ 291
13.3. Explicit Locking ................................................................................................... 291
13.3.1. Table-Level Locks.................................................................................... 292
13.3.2. Row-Level Locks ..................................................................................... 294
13.3.3. Deadlocks................................................................................................. 295
13.3.4. Advisory Locks........................................................................................ 296
13.4. Data Consistency Checks at the Application Level.............................................. 296
13.5. Locking and Indexes............................................................................................. 297
14. Performance Tips .............................................................................................................. 299
14.1. Using EXPLAIN .................................................................................................... 299
14.2. Statistics Used by the Planner .............................................................................. 303
14.3. Controlling the Planner with Explicit JOIN Clauses............................................ 305
14.4. Populating a Database .......................................................................................... 307
14.4.1. Disable Autocommit ................................................................................ 307
14.4.2. Use COPY.................................................................................................. 307
14.4.3. Remove Indexes ....................................................................................... 307
14.4.4. Remove Foreign Key Constraints ............................................................ 308
14.4.5. Increase maintenance_work_mem ........................................................ 308
14.4.6. Increase checkpoint_segments .......................................................... 308
14.4.7. Turn off archive_mode ......................................................................... 308
14.4.8. Run ANALYZE Afterwards........................................................................ 308
14.4.9. Some Notes About pg_dump................................................................... 309
III. Server Administration ............................................................................................................. 310
15. Installation Instructions..................................................................................................... 312
15.1. Short Version ........................................................................................................ 312
15.2. Requirements........................................................................................................ 312
15.3. Getting The Source............................................................................................... 314
15.4. Upgrading ............................................................................................................. 314
15.5. Installation Procedure........................................................................................... 315
15.6. Post-Installation Setup.......................................................................................... 323
15.6.1. Shared Libraries ....................................................................................... 323
15.6.2. Environment Variables ............................................................................. 324
15.7. Supported Platforms ............................................................................................. 324
16. Installation on Windows.................................................................................................... 326
16.1. Building with Visual C++ 2005............................................................................ 326
16.1.1. Requirements ........................................................................................... 326
16.1.2. Building ................................................................................................... 327
16.1.3. Cleaning and installing ............................................................................ 328
16.1.4. Running the regression tests .................................................................... 328
16.1.5. Building the documentation..................................................................... 329
16.2. Building libpq with Visual C++ or Borland C++................................................. 329
16.2.1. Generated files ......................................................................................... 330
17. Operating System Environment ........................................................................................ 331
17.1. The PostgreSQL User Account ............................................................................ 331
17.2. Creating a Database Cluster ................................................................................. 331
17.2.1. Network File Systems .............................................................................. 332
17.3. Starting the Database Server................................................................................. 332
17.3.1. Server Start-up Failures ........................................................................... 334
17.3.2. Client Connection Problems .................................................................... 334
17.4. Managing Kernel Resources................................................................................. 335
ix
17.4.1. Shared Memory and Semaphores ............................................................ 335
17.4.2. Resource Limits ....................................................................................... 341
17.4.3. Linux Memory Overcommit .................................................................... 341
17.5. Shutting Down the Server..................................................................................... 342
17.6. Preventing Server Spoofing .................................................................................. 343
17.7. Encryption Options............................................................................................... 343
17.8. Secure TCP/IP Connections with SSL ................................................................. 345
17.8.1. Creating a Self-Signed Certificate ........................................................... 346
17.9. Secure TCP/IP Connections with SSH Tunnels ................................................... 346
18. Server Configuration ......................................................................................................... 348
18.1. Setting Parameters ................................................................................................ 348
18.2. File Locations ....................................................................................................... 349
18.3. Connections and Authentication........................................................................... 350
18.3.1. Connection Settings ................................................................................. 350
18.3.2. Security and Authentication..................................................................... 352
18.4. Resource Consumption......................................................................................... 353
18.4.1. Memory.................................................................................................... 353
18.4.2. Free Space Map........................................................................................ 355
18.4.3. Kernel Resource Usage............................................................................ 355
18.4.4. Cost-Based Vacuum Delay ...................................................................... 356
18.4.5. Background Writer................................................................................... 357
18.5. Write Ahead Log .................................................................................................. 358
18.5.1. Settings..................................................................................................... 358
18.5.2. Checkpoints.............................................................................................. 360
18.5.3. Archiving ................................................................................................. 361
18.6. Query Planning..................................................................................................... 362
18.6.1. Planner Method Configuration................................................................. 362
18.6.2. Planner Cost Constants ............................................................................ 362
18.6.3. Genetic Query Optimizer......................................................................... 363
18.6.4. Other Planner Options.............................................................................. 364
18.7. Error Reporting and Logging ............................................................................... 365
18.7.1. Where To Log .......................................................................................... 365
18.7.2. When To Log ........................................................................................... 367
18.7.3. What To Log ............................................................................................ 369
18.7.4. Using CSV-Format Log Output ............................................................... 372
18.8. Run-Time Statistics .............................................................................................. 373
18.8.1. Query and Index Statistics Collector ....................................................... 373
18.8.2. Statistics Monitoring................................................................................ 373
18.9. Automatic Vacuuming .......................................................................................... 374
18.10. Client Connection Defaults ................................................................................ 375
18.10.1. Statement Behavior ................................................................................ 375
18.10.2. Locale and Formatting ........................................................................... 378
18.10.3. Other Defaults ........................................................................................ 379
18.11. Lock Management .............................................................................................. 380
18.12. Version and Platform Compatibility................................................................... 381
18.12.1. Previous PostgreSQL Versions .............................................................. 381
18.12.2. Platform and Client Compatibility......................................................... 383
18.13. Preset Options..................................................................................................... 383
18.14. Customized Options ........................................................................................... 384
18.15. Developer Options .............................................................................................. 385
18.16. Short Options...................................................................................................... 386
19. Database Roles and Privileges .......................................................................................... 388
x