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
