Oracle95.pdf - PostgreSQL 10.4 Documentation The PostgreSQL Global Development Group PostgreSQL 10.4 Documentation The PostgreSQL Global Development

Oracle95.pdf - PostgreSQL 10.4 Documentation The PostgreSQL...

This preview shows page 1 out of 3352 pages.

Unformatted text preview: PostgreSQL 10.4 Documentation The PostgreSQL Global Development Group PostgreSQL 10.4 Documentation The PostgreSQL Global Development Group Copyright © 1996-2018 The PostgreSQL Global Development Group Legal Notice PostgreSQL is Copyright © 1996-2018 by the PostgreSQL Global Development Group. 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 PROVIDED 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 ................................................................................................................... xxxv 1. What is PostgreSQL? .................................................................................... xxxv 2. A Brief History of PostgreSQL ....................................................................... xxxv 2.1. The Berkeley POSTGRES Project ........................................................ xxxvi 2.2. Postgres95 ........................................................................................ xxxvi 2.3. PostgreSQL ..................................................................................... xxxvii 3. Conventions ............................................................................................... xxxvii 4. Further Information ..................................................................................... xxxvii 5. Bug Reporting Guidelines ........................................................................... xxxviii 5.1. Identifying Bugs .............................................................................. xxxviii 5.2. What to Report ................................................................................. xxxix 5.3. Where to Report Bugs ............................................................................ xl I. Tutorial .................................................................................................................... 1 1. Getting Started .................................................................................................. 3 1.1. Installation ............................................................................................. 3 1.2. Architectural Fundamentals ....................................................................... 3 1.3. Creating a Database ................................................................................. 4 1.4. Accessing a Database .............................................................................. 5 2. The SQL Language ............................................................................................ 7 2.1. Introduction ............................................................................................ 7 2.2. Concepts ................................................................................................ 7 2.3. Creating a New Table .............................................................................. 7 2.4. Populating a Table With Rows .................................................................. 8 2.5. Querying a Table .................................................................................... 9 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 ........................................................................................... 21 3.7. Conclusion ........................................................................................... 23 II. The SQL Language ................................................................................................. 24 4. SQL Syntax .................................................................................................... 31 4.1. Lexical Structure ................................................................................... 31 4.2. Value Expressions ................................................................................. 39 4.3. Calling Functions .................................................................................. 52 5. Data Definition ................................................................................................ 55 5.1. Table Basics ......................................................................................... 55 5.2. Default Values ...................................................................................... 56 5.3. Constraints ........................................................................................... 57 5.4. System Columns ................................................................................... 64 5.5. Modifying Tables .................................................................................. 65 5.6. Privileges ............................................................................................. 67 5.7. Row Security Policies ............................................................................ 68 5.8. Schemas ............................................................................................... 74 5.9. Inheritance ........................................................................................... 78 5.10. Table Partitioning ................................................................................ 82 5.11. Foreign Data ....................................................................................... 92 5.12. Other Database Objects ......................................................................... 93 5.13. Dependency Tracking ........................................................................... 93 iii PostgreSQL 10.4 Documentation 6. Data Manipulation ............................................................................................ 95 6.1. Inserting Data ....................................................................................... 95 6.2. Updating Data ....................................................................................... 96 6.3. Deleting Data ....................................................................................... 97 6.4. Returning Data From Modified Rows ........................................................ 97 7. Queries ........................................................................................................... 99 7.1. Overview ............................................................................................. 99 7.2. Table Expressions .................................................................................. 99 7.3. Select Lists ......................................................................................... 113 7.4. Combining Queries .............................................................................. 114 7.5. Sorting Rows ...................................................................................... 115 7.6. LIMIT and OFFSET ............................................................................ 116 7.7. VALUES Lists ..................................................................................... 116 7.8. WITH Queries (Common Table Expressions) ............................................ 117 8. Data Types .................................................................................................... 123 8.1. Numeric Types .................................................................................... 124 8.2. Monetary Types ................................................................................... 129 8.3. Character Types ................................................................................... 130 8.4. Binary Data Types ............................................................................... 132 8.5. Date/Time Types ................................................................................. 133 8.6. Boolean Type ...................................................................................... 143 8.7. Enumerated Types ............................................................................... 144 8.8. Geometric Types ................................................................................. 145 8.9. Network Address Types ........................................................................ 148 8.10. Bit String Types ................................................................................ 150 8.11. Text Search Types .............................................................................. 151 8.12. UUID Type ....................................................................................... 153 8.13. XML Type ........................................................................................ 154 8.14. JSON Types ...................................................................................... 156 8.15. Arrays .............................................................................................. 162 8.16. Composite Types ............................................................................... 171 8.17. Range Types ..................................................................................... 177 8.18. Object Identifier Types ....................................................................... 183 8.19. pg_lsn Type ...................................................................................... 184 8.20. Pseudo-Types .................................................................................... 184 9. Functions and Operators .................................................................................. 187 9.1. Logical Operators ................................................................................ 187 9.2. Comparison Functions and Operators ...................................................... 187 9.3. Mathematical Functions and Operators .................................................... 190 9.4. String Functions and Operators .............................................................. 193 9.5. Binary String Functions and Operators .................................................... 208 9.6. Bit String Functions and Operators ......................................................... 210 9.7. Pattern Matching ................................................................................. 210 9.8. Data Type Formatting Functions ............................................................. 225 9.9. Date/Time Functions and Operators ........................................................ 232 9.10. Enum Support Functions ..................................................................... 244 9.11. Geometric Functions and Operators ....................................................... 245 9.12. Network Address Functions and Operators .............................................. 249 9.13. Text Search Functions and Operators ..................................................... 251 9.14. XML Functions ................................................................................. 257 9.15. JSON Functions and Operators ............................................................. 270 9.16. Sequence Manipulation Functions ......................................................... 279 9.17. Conditional Expressions ...................................................................... 281 9.18. Array Functions and Operators ............................................................. 284 9.19. Range Functions and Operators ............................................................ 287 9.20. Aggregate Functions ........................................................................... 289 9.21. Window Functions ............................................................................. 296 9.22. Subquery Expressions ......................................................................... 298 iv PostgreSQL 10.4 Documentation 9.23. Row and Array Comparisons ............................................................... 9.24. Set Returning Functions ...................................................................... 9.25. System Information Functions .............................................................. 9.26. System Administration Functions .......................................................... 9.27. Trigger Functions ............................................................................... 9.28. Event Trigger Functions ...................................................................... 10. Type Conversion .......................................................................................... 10.1. Overview .......................................................................................... 10.2. Operators .......................................................................................... 10.3. Functions .......................................................................................... 10.4. Value Storage .................................................................................... 10.5. UNION, CASE, and Related Constructs .................................................. 10.6. SELECT Output Columns .................................................................... 11. Indexes ....................................................................................................... 11.1. Introduction ....................................................................................... 11.2. Index Types ...................................................................................... 11.3. Multicolumn Indexes .......................................................................... 11.4. Indexes and ORDER BY ..................................................................... 11.5. Combining Multiple Indexes ................................................................ 11.6. Unique Indexes .................................................................................. 11.7. Indexes on Expressions ....................................................................... 11.8. Partial Indexes ................................................................................... 11.9. Operator Classes and Operator Families ................................................. 11.10. Indexes and Collations ...................................................................... 11.11. Index-Only Scans ............................................................................. 11.12. Examining Index Usage ..................................................................... 12. Full Text Search ........................................................................................... 12.1. Introduction ....................................................................................... 12.2. Tables and Indexes ............................................................................. 12.3. Controlling Text Search ...................................................................... 12.4. Additional Features ............................................................................ 12.5. Parsers ............................................................................................. 12.6. Dictionaries ....................................................................................... 12.7. Configuration Example ....................................................................... 12.8. Testing and Debugging Text Search ...................................................... 12.9. GIN and GiST Index Types ................................................................. 12.10. psql Support .................................................................................... 12.11. Limitations ...................................................................................... 13. Concurrency Control ..................................................................................... 13.1. Introduction ....................................................................................... 13.2. Transaction Isolation ........................................................................... 13.3. Explicit Locking ................................................................................ 13.4. Data Consistency Checks at the Application Level ................................... 13.5. Caveats ............................................................................................. 13.6. Locking and Indexes ........................................................................... 14. Performance Tips ......................................................................................... 14.1. Using EXPLAIN ................................................................................ 14.2. Statistics Used by the Planner .............................................................. 14.3. Controlling the Planner with Explicit JOIN Clauses ................................. 14.4. Populating a Database ......................................................................... 14.5. Non-Durable Settings .......................................................................... 15. Parallel Query .............................................................................................. 15.1. How Parallel Query Works .................................................................. 15.2. When Can Parallel Query Be Used? ...................................................... 15.3. Parallel Plans ..................................................................................... 15.4. Parallel Safety ................................................................................... III. Server Administration ............................................................................................ 16. Installation from Source Code ......................................................................... v 300 303 306 323 339 340 343 343 344 348 350 351 352 354 354 355 357 358 358 359 359 360 362 364 364 366 368 368 372 374 380 385 386 395 397 401 402 405 406 406 406 412 417 419 419 420 420 431 434 436 439 440 440 441 442 443 445 451 PostgreSQL 10.4 Documentation 17. 18. 19. 20. 21. 22. 16.1. Short Version ..............
View Full Document

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture