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