PostgreSQL 7.4.2 Documentation
The PostgreSQL Global Development Group
by The PostgreSQL Global Development Group
Copyright © 1996-2003 by The PostgreSQL Global Development Group Legal Notice
PostgreSQL is Copyright © 1996-2002 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 ...i
1. What is PostgreSQL? ...i
2. A Brief History of PostgreSQL... ii
2.1. The Berkeley POSTGRES Project ... ii
2.2. Postgres95... iii
2.3. PostgreSQL... iii
3. Conventions... iii
4. Further Information...iv
5. Bug Reporting Guidelines...iv
5.1. Identifying Bugs ...v
5.2. What to report...v
5.3. Where to report bugs ... vii
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...9
2.7. Aggregate Functions...11
2.8. Updates ...13
2.9. Deletions...13
3. Advanced Features ...15
3.1. Introduction ...15
3.2. Views ...15
3.3. Foreign Keys...15
3.4. Transactions...16
3.5. Inheritance ...17
3.6. Conclusion ...19
II. The SQL Language...20
4. SQL Syntax ...22
4.1. Lexical Structure...22
4.1.1. Identifiers and Key Words...22
4.1.2. Constants...23
4.1.2.1. String Constants ...23
4.1.2.2. Bit-String Constants ...24
4.1.2.3. Numeric Constants ...24
4.1.2.4. Constants of Other Types ...25
4.1.3. Operators...26
4.1.5. Comments ...27
4.1.6. Lexical Precedence ...27
4.2. Value Expressions...28
4.2.1. Column References...29
4.2.2. Positional Parameters...29
4.2.3. Subscripts...30
4.2.4. Field Selection ...30
4.2.5. Operator Invocations...31
4.2.6. Function Calls ...31
4.2.7. Aggregate Expressions...31
4.2.8. Type Casts ...32
4.2.9. Scalar Subqueries...33
4.2.10. Array Constructors...33
4.2.11. Expression Evaluation Rules ...34
5. Data Definition ...36
5.1. Table Basics ...36
5.2. System Columns ...37
5.3. Default Values ...38
5.4. Constraints ...39
5.4.1. Check Constraints ...39
5.4.2. Not-Null Constraints...40
5.4.3. Unique Constraints...41
5.4.4. Primary Keys...42
5.4.5. Foreign Keys ...43
5.5. Inheritance ...45
5.6. Modifying Tables...48
5.6.1. Adding a Column...48
5.6.2. Removing a Column ...48
5.6.3. Adding a Constraint ...49
5.6.4. Removing a Constraint ...49
5.6.5. Changing the Default ...49
5.6.6. Renaming a Column ...49
5.6.7. Renaming a Table ...50
5.7. Privileges ...50
5.8. Schemas ...51
5.8.1. Creating a Schema ...51
5.8.2. The Public Schema ...52
5.8.3. The Schema Search Path...52
5.8.4. Schemas and Privileges...54
5.8.5. The System Catalog Schema ...54
5.8.6. Usage Patterns...54
5.8.7. Portability...55
5.9. Other Database Objects ...55
5.10. Dependency Tracking ...55
6. Data Manipulation...57
6.1. Inserting Data ...57
6.2. Updating Data...58
7. Queries ...60
7.1. Overview ...60
7.2. Table Expressions ...60
7.2.1. TheFROMClause...61
7.2.1.1. Joined Tables ...61
7.2.1.2. Table and Column Aliases...64
7.2.1.3. Subqueries ...65
7.2.1.4. Table Functions ...66
7.2.2. TheWHEREClause...67
7.2.3. TheGROUP BYandHAVINGClauses...68
7.3. Select Lists...70
7.3.1. Select-List Items ...70
7.3.2. Column Labels ...71
7.3.3.DISTINCT...71
7.4. Combining Queries...71
7.5. Sorting Rows ...72
7.6.LIMITandOFFSET...73
8. Data Types...75
8.1. Numeric Types...76
8.1.1. Integer Types...77
8.1.2. Arbitrary Precision Numbers ...78
8.1.3. Floating-Point Types ...78
8.1.4. Serial Types...79
8.2. Monetary Types ...80
8.3. Character Types ...80
8.4. Binary Data Types ...82
8.5. Date/Time Types...84
8.5.1. Date/Time Input ...85
8.5.1.1. Dates...85
8.5.1.2. Times ...86
8.5.1.3. Time Stamps...87
8.5.1.4. Intervals ...87
8.5.1.5. Special Values ...88
8.5.2. Date/Time Output ...88
8.5.3. Time Zones ...89
8.5.4. Internals...90
8.6. Boolean Type ...90
8.7. Geometric Types...91
8.7.1. Points ...92
8.7.2. Line Segments...92
8.7.3. Boxes...92
8.7.4. Paths ...93
8.7.5. Polygons...93
8.7.6. Circles ...93
8.8. Network Address Types...94
8.8.1.inet...94
8.8.2.cidr...94
8.8.4.macaddr...95
8.9. Bit String Types ...96
8.10. Arrays ...96
8.10.1. Declaration of Array Types...96
8.10.2. Array Value Input...97
8.10.3. Accessing Arrays ...99
8.10.4. Modifying Arrays...100
8.10.5. Searching in Arrays...103
8.10.6. Array Input and Output Syntax...103
8.11. Object Identifier Types ...104
8.12. Pseudo-Types...105
9. Functions and Operators ...107
9.1. Logical Operators ...107
9.2. Comparison Operators...107
9.3. Mathematical Functions and Operators...109
9.4. String Functions and Operators ...112
9.5. Binary String Functions and Operators ...120
9.6. Pattern Matching ...122
9.6.1.LIKE...122
9.6.2.SIMILAR TOand SQL99 Regular Expressions...123
9.6.3. POSIX Regular Expressions ...124
9.6.3.1. Regular Expression Details ...125
9.6.3.2. Bracket Expressions ...127
9.6.3.3. Regular Expression Escapes...128
9.6.3.4. Regular Expression Metasyntax...131
9.6.3.5. Regular Expression Matching Rules ...132
9.6.3.6. Limits and Compatibility ...133
9.6.3.7. Basic Regular Expressions ...134
9.7. Data Type Formatting Functions ...134
9.8. Date/Time Functions and Operators...139
9.8.1.EXTRACT,date_part...142
9.8.2.date_trunc...145
9.8.3.AT TIME ZONE...146
9.8.4. Current Date/Time ...146
9.9. Geometric Functions and Operators ...148
9.10. Network Address Type Functions ...151
9.11. Sequence-Manipulation Functions ...153
9.12. Conditional Expressions ...154
9.12.1.CASE...155
9.12.2.COALESCE...156
9.12.3.NULLIF...156
9.13. Miscellaneous Functions ...157
9.14. Array Functions and Operators ...162
9.15. Aggregate Functions...164
9.16. Subquery Expressions ...165
9.16.1.EXISTS...166
9.16.2.IN...166
9.16.4.ANY/SOME...167
9.16.5.ALL...168
9.16.6. Row-wise Comparison...169
9.17. Row and Array Comparisons ...169
9.17.1.IN...169
9.17.2.NOT IN...169
9.17.3.ANY/SOME(array) ...170
9.17.4.ALL(array) ...170
9.17.5. Row-wise Comparison...170
10. Type Conversion...172
10.1. Overview ...172
10.2. Operators ...173
10.3. Functions ...176
10.4. Value Storage...179
10.5.UNION,CASE, andARRAYConstructs ...179
11. Indexes ...182
11.1. Introduction ...182
11.2. Index Types...183
11.3. Multicolumn Indexes ...183
11.4. Unique Indexes ...184
11.5. Indexes on Expressions ...185
11.6. Operator Classes ...185
11.7. Partial Indexes ...186
11.8. Examining Index Usage...189
12. Concurrency Control ...191
12.1. Introduction ...191
12.2. Transaction Isolation ...191
12.2.1. Read Committed Isolation Level ...192
12.2.2. Serializable Isolation Level...193
12.3. Explicit Locking ...193
12.3.1. Table-Level Locks...194
12.3.2. Row-Level Locks ...195
12.3.3. Deadlocks...196
12.4. Data Consistency Checks at the Application Level...196
12.5. Locking and Indexes...197
13. Performance Tips ...199
13.1. UsingEXPLAIN...199
13.2. Statistics Used by the Planner ...202
13.3. Controlling the Planner with ExplicitJOINClauses...204
13.4. Populating a Database ...205
13.4.1. Disable Autocommit ...206
13.4.2. UseCOPY FROM...206
13.4.3. Remove Indexes ...206
13.4.4. Increasesort_mem...206
13.4.5. RunANALYZEAfterwards...206
14. Installation Instructions...209
14.1. Short Version ...209
14.2. Requirements ...209
14.3. Getting The Source...211
14.4. If You Are Upgrading ...211
14.5. Installation Procedure ...213
14.6. Post-Installation Setup...218
14.6.1. Shared Libraries ...218
14.6.2. Environment Variables ...219
14.7. Supported Platforms ...220
15. Installation on Windows...225
16. Server Run-time Environment ...227
16.1. The PostgreSQL User Account ...227
16.2. Creating a Database Cluster ...227
16.3. Starting the Database Server...228
16.3.1. Server Start-up Failures ...229
16.3.2. Client Connection Problems ...230
16.4. Run-time Configuration...231
16.4.1. Connections and Authentication ...232
16.4.1.1. Connection Settings...232
16.4.1.2. Security and Authentication ...233
16.4.2. Resource Consumption ...234
16.4.2.1. Memory ...234
16.4.2.2. Free Space Map ...235
16.4.2.3. Kernel Resource Usage ...235
16.4.3. Write Ahead Log...235
16.4.3.1. Settings ...236
16.4.3.2. Checkpoints ...236
16.4.4. Query Planning ...237
16.4.4.1. Planner Method Configuration ...237
16.4.4.2. Planner Cost Constants...238
16.4.4.3. Genetic Query Optimizer ...239
16.4.4.4. Other Planner Options ...239
16.4.5. Error Reporting and Logging...240
16.4.5.1. Syslog ...240
16.4.5.2. When To Log...240
16.4.5.3. What To Log...242
16.4.6. Runtime Statistics ...243
16.4.6.1. Statistics Monitoring ...243
16.4.6.2. Query and Index Statistics Collector...243
16.4.7. Client Connection Defaults...244
16.4.7.1. Statement Behavior ...244
16.4.7.2. Locale and Formatting...245
16.4.7.3. Other Defaults ...246
16.4.8. Lock Management ...247
16.4.9. Version and Platform Compatibility ...247
16.4.9.1. Previous PostgreSQL Versions...247
16.4.10. Developer Options ...248
16.4.11. Short Options ...249
16.5. Managing Kernel Resources...250
16.5.1. Shared Memory and Semaphores ...250
16.5.2. Resource Limits ...254
16.5.3. Linux Memory Overcommit ...255
16.6. Shutting Down the Server...256
16.7. Secure TCP/IP Connections with SSL ...256
16.8. Secure TCP/IP Connections with SSH Tunnels ...257
17. Database Users and Privileges ...259
17.1. Database Users ...259
17.2. User Attributes...259
17.3. Groups ...260
17.4. Privileges ...260
17.5. Functions and Triggers ...261
18. Managing Databases ...262
18.1. Overview ...262
18.2. Creating a Database ...262
18.3. Template Databases ...263
18.4. Database Configuration ...264
18.5. Alternative Locations ...265
18.6. Destroying a Database ...266
19. Client Authentication ...267
19.1. Thepg_hba.conffile ...267
19.2. Authentication methods...272
19.2.1. Trust authentication...272
19.2.2. Password authentication...272
19.2.3. Kerberos authentication ...273
19.2.4. Ident-based authentication ...274
19.2.4.1. Ident Authentication over TCP/IP ...274
19.2.4.2. Ident Authentication over Local Sockets ...274
19.2.4.3. Ident Maps...274
19.2.5. PAM Authentication ...275
19.3. Authentication problems ...275
20. Localization...277
20.1. Locale Support...277
20.1.1. Overview ...277
20.1.2. Benefits ...278
20.1.3. Problems ...279
20.2. Character Set Support...279
20.2.1. Supported Character Sets...279
20.2.2. Setting the Character Set...280
20.2.3. Automatic Character Set Conversion Between Server and Client...281
20.2.4. Further Reading ...283
21. Routine Database Maintenance Tasks...285
21.1. Routine Vacuuming ...285
21.1.1. Recovering disk space...285
21.1.3. Preventing transaction ID wraparound failures ...287
21.2. Routine Reindexing ...288
21.3. Log File Maintenance...288
22. Backup and Restore ...290
22.1. SQL Dump...290
22.1.1. Restoring the dump ...290
22.1.2. Usingpg_dumpall...291
22.1.3. Large Databases ...292
22.1.4. Caveats ...292
22.2. File system level backup...293
22.3. Migration Between Releases ...293
23. Monitoring Database Activity...295
23.1. Standard Unix Tools ...295
23.2. The Statistics Collector...295
23.2.1. Statistics Collection Configuration ...296
23.2.2. Viewing Collected Statistics ...296
23.3. Viewing Locks ...300
24. Monitoring Disk Usage ...302
24.1. Determining Disk Usage ...302
24.2. Disk Full Failure...303
25. Write-Ahead Logging (WAL) ...304
25.1. Benefits of WAL ...304
25.2. Future Benefits...304
25.3. WAL Configuration ...305
25.4. Internals ...306
26. Regression Tests...308
26.1. Running the Tests ...308
26.2. Test Evaluation ...309
26.2.1. Error message differences...309
26.2.2. Locale differences ...309
26.2.3. Date and time differences ...310
26.2.4. Floating-point differences ...310
26.2.5. Row ordering differences...311
26.2.6. The “random” test ...311
26.3. Platform-specific comparison files ...311
IV. Client Interfaces ...313
27. libpq - C Library ...315
27.1. Database Connection Control Functions ...315
27.2. Connection Status Functions ...321
27.3. Command Execution Functions ...324
27.3.1. Main Functions ...324
27.3.2. Retrieving Query Result Information ...329
27.3.3. Retrieving Result Information for Other Commands ...333
27.3.4. Escaping Strings for Inclusion in SQL Commands ...333
27.3.5. Escaping Binary Strings for Inclusion in SQL Commands ...334
27.4. Asynchronous Command Processing ...335
27.6. Asynchronous Notification ...340
27.7. Functions Associated with theCOPYCommand ...341
27.7.1. Functions for SendingCOPYData...342
27.7.2. Functions for ReceivingCOPYData...343
27.7.3. Obsolete Functions forCOPY...344
27.8. Control Functions ...346
27.9. Notice Processing ...346
27.10. Environment Variables ...348
27.11. The Password File ...349
27.12. Behavior in Threaded Programs ...349
27.13. Building libpq Programs...350
27.14. Example Programs...351
28. Large Objects ...360
28.1. History ...360
28.2. Implementation Features ...360
28.3. Client Interfaces...360
28.3.1. Creating a Large Object ...360
28.3.2. Importing a Large Object...361
28.3.3. Exporting a Large Object...361
28.3.4. Opening an Existing Large Object...361
28.3.5. Writing Data to a Large Object...361
28.3.6. Reading Data from a Large Object ...362
28.3.7. Seeking on a Large Object ...362
28.3.8. Obtaining the Seek Position of a Large Object...362
28.3.9. Closing a Large Object Descriptor ...362
28.3.10. Removing a Large Object ...362
28.4. Server-Side Functions...363
28.5. Example Program ...363
29. pgtcl - Tcl Binding Library ...369
29.1. Overview ...369
29.2. Loading pgtcl into an Application ...369
29.3. pgtcl Command Reference ...370
pg_connect ...370
pg_disconnect ...372
pg_conndefaults ...373
pg_exec ...374
pg_result...375
pg_select ...377
pg_execute ...379
pg_listen...381
pg_on_connection_loss...382
pg_lo_creat...383
pg_lo_open...384
pg_lo_close ...385
pg_lo_read...386
pg_lo_write ...387
pg_lo_lseek ...388
pg_lo_unlink ...390
pg_lo_import...391
pg_lo_export ...392
29.4. Example Program ...393
30. ECPG - Embedded SQL in C...394
30.1. The Concept...394
30.2. Connecting to the Database Server...394
30.3. Closing a Connection ...395
30.4. Running SQL Commands...396
30.5. Choosing a Connection...397
30.6. Using Host Variables ...397
30.6.1. Overview ...397
30.6.2. Declare Sections...398
30.6.3.SELECT INTOandFETCH INTO...398
30.6.4. Indicators...399
30.7. Dynamic SQL ...400
30.8. Using SQL Descriptor Areas...401
30.9. Error Handling...403
30.9.1. Setting Callbacks ...403
30.9.2. sqlca ...404
30.9.3.SQLSTATEvsSQLCODE...405
30.10. Including Files ...408
30.11. Processing Embedded SQL Programs...408
30.12. Library Functions ...409
30.13. Internals ...410
31. JDBC Interface...412
31.1. Setting up the JDBC Driver...412
31.1.1. Getting the Driver ...412
31.1.2. Setting up the Class Path...412
31.1.3. Preparing the Database Server for JDBC...412
31.2. Initializing the Driver ...413
31.2.1. Importing JDBC...413
31.2.2. Loading the Driver ...413
31.2.3. Connecting to the Database ...414
31.2.4. Closing the Connection...414
31.3. Issuing a Query and Processing the Result...415
31.3.1. Getting results based on a cursor ...415
31.3.2. Using theStatementorPreparedStatementInterface ...416
31.3.3. Using theResultSetInterface ...416
31.4. Performing Updates...417
31.5. Calling Stored Functions ...417
31.5.1. Using theCallableStatementInterface ...417
31.5.2. ObtainingResultSetfrom a stored function ...418
31.6. Creating and Modifying Database Objects...418
31.7. Storing Binary Data ...419
31.8. PostgreSQL Extensions to the JDBC API...422
31.8.1. Accessing the Extensions...422
31.8.1.1.1. Methods ...422
31.8.1.2. Classorg.postgresql.Fastpath...423
31.8.1.2.1. Methods ...424
31.8.1.3. Classorg.postgresql.fastpath.FastpathArg...425
31.8.1.3.1. Constructors...426
31.8.2. Geometric Data Types...426
31.8.3. Large Objects ...440
31.8.3.1. Classorg.postgresql.largeobject.LargeObject...440
31.8.3.1.1. Variables ...440
31.8.3.1.2. Methods ...441
31.8.3.2. Classorg.postgresql.largeobject.LargeObjectManager.442 31.8.3.2.1. Variables ...442
31.8.3.2.2. Methods ...443
31.9. Using the Driver in a Multithreaded or a Servlet Environment...443
31.10. Connection Pools and Data Sources...444
31.10.1. Overview ...444
31.10.2. Application Servers:ConnectionPoolDataSource...444
31.10.3. Applications:DataSource...446
31.10.4. Data Sources and JNDI...447
31.11. Further Reading ...448
32. The Information Schema...450
32.1. The Schema ...450
32.2. Data Types ...450
32.3.information_schema_catalog_name...451
32.4.applicable_roles...451
32.5.check_constraints...451
32.6.column_domain_usage...452
32.7.column_privileges...452
32.8.column_udt_usage...453
32.9.columns...454
32.10.constraint_column_usage...458
32.11.constraint_table_usage...458
32.12.data_type_privileges...459
32.13.domain_constraints...460
32.14.domain_udt_usage...460
32.15.domains...461
32.16.element_types...464
32.17.enabled_roles...466
32.18.key_column_usage...467
32.19.parameters...467
32.20.referential_constraints...470
32.21.role_column_grants...471
32.22.role_routine_grants...471
32.23.role_table_grants...472
32.24.role_usage_grants...473
32.25.routine_privileges...473
32.26.routines...474
32.28.sql_features...479
32.29.sql_implementation_info...479
32.30.sql_languages...480
32.31.sql_packages...481
32.32.sql_sizing...481
32.33.sql_sizing_profiles...482
32.34.table_constraints...482
32.35.table_privileges...483
32.36.tables...484
32.37.triggers...484
32.38.usage_privileges...486
32.39.view_column_usage...486
32.40.view_table_usage...487
32.41.views...488
V. Server Programming ...489
33. Extending SQL...491
33.1. How Extensibility Works...491
33.2. The PostgreSQL Type System...491
33.2.1. Base Types ...491
33.2.2. Composite Types...491
33.2.3. Domains ...492
33.2.4. Pseudo-Types ...492
33.2.5. Polymorphic Types ...492
33.3. User-Defined Functions...493
33.4. Query Language (SQL) Functions ...493
33.4.1. SQL Functions on Base Types ...493
33.4.2. SQL Functions on Composite Types ...495
33.4.3. SQL Functions as Table Sources ...497
33.4.4. SQL Functions Returning Sets ...498
33.4.5. Polymorphic SQL Functions ...499
33.5. Procedural Language Functions ...500
33.6. Internal Functions ...500
33.7. C-Language Functions...501
33.7.1. Dynamic Loading...501
33.7.2. Base Types in C-Language Functions...502
33.7.3. Calling Conventions Version 0 for C-Language Functions ...504
33.7.4. Calling Conventions Version 1 for C-Language Functions ...507
33.7.5. Writing Code...509
33.7.6. Compiling and Linking Dynamically-Loaded Functions ...510
33.7.7. Composite-Type Arguments in C-Language Functions...513
33.7.8. Returning Rows (Composite Types) from C-Language Functions...514
33.7.9. Returning Sets from C-Language Functions...516
33.7.10. Polymorphic Arguments and Return Types ...520
33.8. Function Overloading ...522
33.9. User-Defined Aggregates ...522
33.10. User-Defined Types ...524
33.12. Operator Optimization Information...528
33.12.1.COMMUTATOR...528
33.12.2.NEGATOR...529
33.12.3.RESTRICT...530
33.12.4.JOIN...531
33.12.5.HASHES...531
33.12.6.MERGES(SORT1,SORT2,LTCMP,GTCMP)...532
33.13. Interfacing Extensions To Indexes...533
33.13.1. Index Methods and Operator Classes ...533
33.13.2. Index Method Strategies ...534
33.13.3. Index Method Support Routines ...535
33.13.4. An Example ...536
33.13.5. System Dependencies on Operator Classes ...539
33.13.6. Special Features of Operator Classes...539
34. The Rule System ...541
34.1. The Query Tree...541
34.2. Views and the Rule System ...543
34.2.1. HowSELECTRules Work ...543
34.2.2. View Rules in Non-SELECTStatements ...548
34.2.3. The Power of Views in PostgreSQL ...549
34.2.4. Updating a View...550
34.3. Rules onINSERT,UPDATE, andDELETE...550
34.3.1. How Update Rules Work ...550
34.3.1.1. A First Rule Step by Step ...551
34.3.2. Cooperation with Views...555
34.4. Rules and Privileges ...560
34.5. Rules and Command Status...561
34.6. Rules versus Triggers ...562
35. Triggers ...565
35.1. Overview of Trigger Behavior...565
35.2. Visibility of Data Changes...566
35.3. Writing Trigger Functions in C ...566
35.4. A Complete Example ...568
36. Procedural Languages ...573
36.1. Installing Procedural Languages ...573
37. PL/pgSQL - SQL Procedural Language ...575
37.1. Overview ...575
37.1.1. Advantages of Using PL/pgSQL ...576
37.1.2. Supported Argument and Result Data Types ...576
37.2. Tips for Developing in PL/pgSQL...577
37.2.1. Handling of Quotation Marks ...577
37.3. Structure of PL/pgSQL...578
37.4. Declarations ...579
37.4.1. Aliases for Function Parameters ...580
37.4.2. Copying Types ...581
37.4.3. Row Types...582
37.4.4. Record Types ...582
37.5. Expressions...583
37.6. Basic Statements...584
37.6.1. Assignment ...585
37.6.2.SELECT INTO...585
37.6.3. Executing an Expression or Query With No Result...586
37.6.4. Executing Dynamic Commands ...587
37.6.5. Obtaining the Result Status...588
37.7. Control Structures...589
37.7.1. Returning From a Function...589
37.7.1.1.RETURN...589
37.7.1.2.RETURN NEXT...589
37.7.2. Conditionals ...590
37.7.2.1.IF-THEN...590
37.7.2.2.IF-THEN-ELSE...590
37.7.2.3.IF-THEN-ELSE IF...591
37.7.2.4.IF-THEN-ELSIF-ELSE...591
37.7.3. Simple Loops ...592
37.7.3.1.LOOP...592
37.7.3.2.EXIT...592
37.7.3.3.WHILE...593
37.7.3.4.FOR(integer variant)...593
37.7.4. Looping Through Query Results ...594
37.8. Cursors...595
37.8.1. Declaring Cursor Variables ...595
37.8.2. Opening Cursors ...596
37.8.2.1.OPEN FOR SELECT...596
37.8.2.2.OPEN FOR EXECUTE...596
37.8.2.3. Opening a Bound Cursor...597
37.8.3. Using Cursors...597
37.8.3.1.FETCH...597
37.8.3.2.CLOSE...597
37.8.3.3. Returning Cursors ...598
37.9. Errors and Messages...599
37.10. Trigger Procedures ...600
37.11. Porting from Oracle PL/SQL...602
37.11.1. Porting Examples ...602
37.11.2. Other Things to Watch For...607
37.11.2.1.EXECUTE...607
37.11.2.2. Optimizing PL/pgSQL Functions...607
37.11.3. Appendix...608
38. PL/Tcl - Tcl Procedural Language...611
38.1. Overview ...611
38.2. PL/Tcl Functions and Arguments...611
38.3. Data Values in PL/Tcl...612
38.4. Global Data in PL/Tcl ...613
38.5. Database Access from PL/Tcl ...613
38.6. Trigger Procedures in PL/Tcl ...615
38.8. Tcl Procedure Names ...617
39. PL/Perl - Perl Procedural Language...618
39.1. PL/Perl Functions and Arguments...618
39.2. Data Values in PL/Perl...619
39.3. Database Access from PL/Perl ...619
39.4. Trusted and Untrusted PL/Perl ...620
39.5. Missing Features...620
40. PL/Python - Python Procedural Language...622
40.1. PL/Python Functions ...622
40.2. Trigger Functions ...623
40.3. Database Access ...623
41. Server Programming Interface ...625
41.1. Interface Functions ...625
SPI_connect ...625
SPI_finish...627
SPI_exec...628
SPI_prepare...631
SPI_execp...633
SPI_cursor_open ...635
SPI_cursor_find...636
SPI_cursor_fetch...637
SPI_cursor_move ...638
SPI_cursor_close...639
SPI_saveplan ...640
41.2. Interface Support Functions ...641
SPI_fname...641
SPI_fnumber ...642
SPI_getvalue ...643
SPI_getbinval ...644
SPI_gettype ...645
SPI_gettypeid...646
SPI_getrelname ...647
41.3. Memory Management ...648
SPI_palloc ...648
SPI_repalloc...650
SPI_pfree...651
SPI_copytuple ...652
SPI_copytupledesc ...653
SPI_copytupleintoslot ...654
SPI_modifytuple ...655
SPI_freetuple...657
SPI_freetuptable...658
SPI_freeplan...659
41.4. Visibility of Data Changes...660
41.5. Examples ...660
I. SQL Commands...666
ABORT...667
ALTER AGGREGATE...669
ALTER CONVERSION...670
ALTER DATABASE ...671
ALTER DOMAIN ...673
ALTER FUNCTION ...676
ALTER GROUP ...677
ALTER LANGUAGE...679
ALTER OPERATOR CLASS...680
ALTER SCHEMA ...681
ALTER SEQUENCE...682
ALTER TABLE ...684
ALTER TRIGGER ...689
ALTER USER ...690
ANALYZE...693
BEGIN ...695
CHECKPOINT...697
CLOSE ...698
CLUSTER ...699
COMMENT...702
COMMIT...705
COPY ...707
CREATE AGGREGATE ...714
CREATE CAST...717
CREATE CONSTRAINT TRIGGER ...720
CREATE CONVERSION ...721
CREATE DATABASE...723
CREATE DOMAIN...726
CREATE FUNCTION...728
CREATE GROUP...732
CREATE INDEX...734
CREATE LANGUAGE ...737
CREATE OPERATOR ...740
CREATE OPERATOR CLASS ...744
CREATE RULE...747
CREATE SCHEMA ...750
CREATE SEQUENCE ...752
CREATE TABLE ...755
CREATE TABLE AS ...765
CREATE TRIGGER...767
CREATE TYPE ...770
CREATE USER...776
CREATE VIEW...779
DEALLOCATE ...782
DECLARE...783
DELETE ...786
DROP CAST ...790
DROP CONVERSION...792
DROP DATABASE ...793
DROP DOMAIN ...794
DROP FUNCTION ...795
DROP GROUP ...797
DROP INDEX ...798
DROP LANGUAGE...799
DROP OPERATOR ...800
DROP OPERATOR CLASS...802
DROP RULE ...804
DROP SCHEMA ...806
DROP SEQUENCE...808
DROP TABLE ...809
DROP TRIGGER ...811
DROP TYPE...813
DROP USER ...814
DROP VIEW ...816
END ...817
EXECUTE ...819
EXPLAIN ...820
FETCH ...823
GRANT ...827
INSERT ...832
LISTEN ...835
LOAD ...837
LOCK ...838
MOVE...841
NOTIFY...843
PREPARE ...845
REINDEX...847
RESET ...850
REVOKE ...852
ROLLBACK ...855
SELECT ...857
SELECT INTO ...869
SET ...871
SET CONSTRAINTS ...874
SET SESSION AUTHORIZATION...875
SET TRANSACTION ...877
SHOW ...879
START TRANSACTION ...882
TRUNCATE ...883
UNLISTEN...884
UPDATE ...886
VACUUM ...888
II. PostgreSQL Client Applications ...891
createdb...895
createlang...898
createuser ...901
dropdb...904
droplang ...907
dropuser ...910
ecpg...913
pg_config ...915
pg_dump ...917
pg_dumpall ...924
pg_restore ...928
pgtclsh...935
pgtksh ...936
psql ...937
vacuumdb...960
III. PostgreSQL Server Applications ...963
initdb...964
initlocation ...967
ipcclean...968
pg_controldata ...969
pg_ctl ...970
pg_resetxlog ...974
postgres...976
postmaster...980
VII. Internals...985
42. Overview of PostgreSQL Internals ...987
42.1. The Path of a Query...987
42.2. How Connections are Established ...987
42.3. The Parser Stage ...988
42.3.1. Parser...988
42.3.2. Transformation Process...989
42.4. The PostgreSQL Rule System ...989
42.5. Planner/Optimizer...990
42.5.1. Generating Possible Plans...990
42.6. Executor...991
43. System Catalogs ...993
43.1. Overview ...993
43.2.pg_aggregate...994
43.3.pg_am...994
43.4.pg_amop...996
43.5.pg_amproc...996
43.6.pg_attrdef...997
43.7.pg_attribute...997
43.8.pg_cast...1000
43.9.pg_class...1001
43.10.pg_constraint...1003
43.12.pg_database...1005
43.13.pg_depend...1007
43.14.pg_description...1008
43.15.pg_group...1009
43.16.pg_index...1009
43.17.pg_inherits...1011
43.18.pg_language...1011
43.19.pg_largeobject...1012
43.20.pg_listener...1013
43.21.pg_namespace...1014
43.22.pg_opclass...1014
43.23.pg_operator...1015
43.24.pg_proc...1016
43.25.pg_rewrite...1018
43.26.pg_shadow...1019
43.27.pg_statistic...1020
43.28.pg_trigger...1022
43.29.pg_type...1023
43.30. System Views ...1029
43.31.pg_indexes...1030
43.32.pg_locks...1030
43.33.pg_rules...1032
43.34.pg_settings...1032
43.35.pg_stats...1033
43.36.pg_tables...1035
43.37.pg_user...1036
43.38.pg_views...1036
44. Frontend/Backend Protocol...1038
44.1. Overview ...1038
44.1.1. Messaging Overview...1038
44.1.2. Extended Query Overview...1039
44.1.3. Formats and Format Codes ...1039
44.2. Message Flow ...1040
44.2.1. Start-Up...1040
44.2.2. Simple Query ...1042
44.2.3. Extended Query ...1043
44.2.4. Function Call...1046
44.2.5. COPY Operations ...1046
44.2.6. Asynchronous Operations...1047
44.2.7. Cancelling Requests in Progress...1048
44.2.8. Termination ...1049
44.2.9. SSL Session Encryption...1049
44.3. Message Data Types ...1049
44.4. Message Formats ...1050
44.5. Error and Notice Message Fields ...1067
44.6. Summary of Changes since Protocol 2.0...1068
45. PostgreSQL Coding Conventions ...1070
45.2. Reporting Errors Within the Server ...1070 45.3. Error Message Style Guide...1072 45.3.1. What goes where...1072 45.3.2. Formatting...1073 45.3.3. Quotation marks...1073 45.3.4. Use of quotes...1073 45.3.5. Grammar and punctuation...1074 45.3.6. Upper case vs. lower case ...1074 45.3.7. Avoid passive voice...1074 45.3.8. Present vs past tense...1074 45.3.9. Type of the object...1075 45.3.10. Brackets...1075 45.3.11. Assembling error messages...1075 45.3.12. Reasons for errors ...1075 45.3.13. Function names ...1075 45.3.14. Tricky words to avoid ...1076 45.3.15. Proper spelling ...1076 45.3.16. Localization...1077 46. Native Language Support...1078 46.1. For the Translator ...1078 46.1.1. Requirements ...1078 46.1.2. Concepts...1078 46.1.3. Creating and maintaining message catalogs ...1079 46.1.4. Editing the PO files ...1080 46.2. For the Programmer...1081 46.2.1. Mechanics ...1081 46.2.2. Message-writing guidelines ...1082 47. Writing A Procedural Language Handler ...1084 48. Genetic Query Optimizer ...1086 48.1. Query Handling as a Complex Optimization Problem...1086 48.2. Genetic Algorithms ...1086 48.3. Genetic Query Optimization (GEQO) in PostgreSQL ...1087 48.3.1. Future Implementation Tasks for PostgreSQL GEQO ...1088 48.4. Further Readings ...1088 49. Index Cost Estimation Functions ...1089 50. GiST Indexes...1092 50.1. Introduction ...1092 50.2. Extensibility...1092 50.3. Implementation...1092 50.4. Limitations...1093 50.5. Examples ...1093 51. Page Files ...1095 52. BKI Backend Interface...1098 52.1. BKI File Format ...1098 52.2. BKI Commands ...1098 52.3. Example ...1099
A. PostgreSQL Error Codes...1101 B. Date/Time Support ...1108 B.1. Date/Time Input Interpretation ...1108 B.2. Date/Time Key Words...1109 B.3. History of Units ...1114 C. SQL Key Words...1116 D. SQL Conformance ...1133 D.1. Supported Features ...1133 D.2. Unsupported Features ...1144 E. Release Notes ...1149 E.1. Release 7.4.2 ...1149 E.1.1. Migration to version 7.4.2 ...1149 E.1.2. Changes ...1150 E.2. Release 7.4.1 ...1151 E.2.1. Migration to version 7.4.1 ...1151 E.2.2. Changes ...1151 E.3. Release 7.4 ...1152 E.3.1. Overview ...1153 E.3.2. Migration to version 7.4 ...1154 E.3.3. Changes ...1155 E.3.3.1. Server Operation Changes ...1155 E.3.3.2. Performance Improvements ...1157 E.3.3.3. Server Configuration Changes ...1158 E.3.3.4. Query Changes...1160 E.3.3.5. Object Manipulation Changes ...1160 E.3.3.6. Utility Command Changes...1162 E.3.3.7. Data Type and Function Changes ...1163 E.3.3.8. Server-Side Language Changes ...1165 E.3.3.9. psql Changes ...1166 E.3.3.10. pg_dump Changes...1166 E.3.3.11. libpq Changes ...1167 E.3.3.12. JDBC Changes ...1168 E.3.3.13. Miscellaneous Interface Changes ...1168 E.3.3.14. Source Code Changes ...1168 E.3.3.15. Contrib Changes ...1169 E.4. Release 7.3.6 ...1170 E.4.1. Migration to version 7.3.6 ...1170 E.4.2. Changes ...1170 E.5. Release 7.3.5 ...1171 E.5.1. Migration to version 7.3.5 ...1171 E.5.2. Changes ...1171 E.6. Release 7.3.4 ...1172 E.6.1. Migration to version 7.3.4 ...1172 E.6.2. Changes ...1172 E.7. Release 7.3.3 ...1172 E.7.1. Migration to version 7.3.3 ...1173 E.7.2. Changes ...1173
E.8.1. Migration to version 7.3.2 ...1175 E.8.2. Changes ...1175 E.9. Release 7.3.1 ...1176 E.9.1. Migration to version 7.3.1 ...1176 E.9.2. Changes ...1176 E.10. Release 7.3 ...1177 E.10.1. Overview ...1177 E.10.2. Migration to version 7.3 ...1178 E.10.3. Changes ...1179 E.10.3.1. Server Operation ...1179 E.10.3.2. Performance ...1179 E.10.3.3. Privileges...1180 E.10.3.4. Server Configuration...1180 E.10.3.5. Queries ...1180 E.10.3.6. Object Manipulation ...1181 E.10.3.7. Utility Commands...1182 E.10.3.8. Data Types and Functions ...1183 E.10.3.9. Internationalization ...1184 E.10.3.10. Server-side Languages ...1185 E.10.3.11. psql...1185 E.10.3.12. libpq ...1186 E.10.3.13. JDBC...1186 E.10.3.14. Miscellaneous Interfaces...1186 E.10.3.15. Source Code ...1187 E.10.3.16. Contrib ...1188 E.11. Release 7.2.4 ...1189 E.11.1. Migration to version 7.2.4 ...1189 E.11.2. Changes ...1189 E.12. Release 7.2.3 ...1189 E.12.1. Migration to version 7.2.3 ...1190 E.12.2. Changes ...1190 E.13. Release 7.2.2 ...1190 E.13.1. Migration to version 7.2.2 ...1190 E.13.2. Changes ...1190 E.14. Release 7.2.1 ...1191 E.14.1. Migration to version 7.2.1 ...1191 E.14.2. Changes ...1191 E.15. Release 7.2 ...1192 E.15.1. Overview ...1192 E.15.2. Migration to version 7.2 ...1192 E.15.3. Changes ...1193 E.15.3.1. Server Operation ...1193 E.15.3.2. Performance ...1194 E.15.3.3. Privileges...1194 E.15.3.4. Client Authentication ...1194 E.15.3.5. Server Configuration...1195 E.15.3.6. Queries ...1195
E.15.3.8. Utility Commands...1196 E.15.3.9. Data Types and Functions ...1196 E.15.3.10. Internationalization ...1197 E.15.3.11. PL/pgSQL ...1198 E.15.3.12. PL/Perl ...1198 E.15.3.13. PL/Tcl ...1198 E.15.3.14. PL/Python ...1198 E.15.3.15. psql...1198 E.15.3.16. libpq ...1199 E.15.3.17. JDBC...1199 E.15.3.18. ODBC ...1200 E.15.3.19. ECPG ...1200 E.15.3.20. Misc. Interfaces...1201 E.15.3.21. Build and Install...1201 E.15.3.22. Source Code ...1201 E.15.3.23. Contrib ...1202 E.16. Release 7.1.3 ...1202 E.16.1. Migration to version 7.1.3 ...1202 E.16.2. Changes ...1202 E.17. Release 7.1.2 ...1203 E.17.1. Migration to version 7.1.2 ...1203 E.17.2. Changes ...1203 E.18. Release 7.1.1 ...1203 E.18.1. Migration to version 7.1.1 ...1203 E.18.2. Changes ...1203 E.19. Release 7.1 ...1204 E.19.1. Migration to version 7.1 ...1205 E.19.2. Changes ...1205 E.20. Release 7.0.3 ...1208 E.20.1. Migration to version 7.0.3 ...1209 E.20.2. Changes ...1209 E.21. Release 7.0.2 ...1210 E.21.1. Migration to version 7.0.2 ...1210 E.21.2. Changes ...1210 E.22. Release 7.0.1 ...1210 E.22.1. Migration to version 7.0.1 ...1210 E.22.2. Changes ...1210 E.23. Release 7.0 ...1211 E.23.1. Migration to version 7.0 ...1212 E.23.2. Changes ...1212 E.24. Release 6.5.3 ...1218 E.24.1. Migration to version 6.5.3 ...1218 E.24.2. Changes ...1219 E.25. Release 6.5.2 ...1219 E.25.1. Migration to version 6.5.2 ...1219 E.25.2. Changes ...1219 E.26. Release 6.5.1 ...1220
E.26.2. Changes ...1220 E.27. Release 6.5 ...1221 E.27.1. Migration to version 6.5 ...1222 E.27.1.1. Multiversion Concurrency Control ...1222 E.27.2. Changes ...1222 E.28. Release 6.4.2 ...1226 E.28.1. Migration to version 6.4.2 ...1226 E.28.2. Changes ...1226 E.29. Release 6.4.1 ...1226 E.29.1. Migration to version 6.4.1 ...1226 E.29.2. Changes ...1226 E.30. Release 6.4 ...1227 E.30.1. Migration to version 6.4 ...1228 E.30.2. Changes ...1228 E.31. Release 6.3.2 ...1232 E.31.1. Changes ...1232 E.32. Release 6.3.1 ...1233 E.32.1. Changes ...1233 E.33. Release 6.3 ...1234 E.33.1. Migration to version 6.3 ...1235 E.33.2. Changes ...1235 E.34. Release 6.2.1 ...1238 E.34.1. Migration from version 6.2 to version 6.2.1...1239 E.34.2. Changes ...1239 E.35. Release 6.2 ...1240 E.35.1. Migration from version 6.1 to version 6.2...1240 E.35.2. Migration from version 1.xto version 6.2 ...1240 E.35.3. Changes ...1240 E.36. Release 6.1.1 ...1242 E.36.1. Migration from version 6.1 to version 6.1.1...1242 E.36.2. Changes ...1242 E.37. Release 6.1 ...1243 E.37.1. Migration to version 6.1 ...1243 E.37.2. Changes ...1244 E.38. Release 6.0 ...1245 E.38.1. Migration from version 1.09 to version 6.0...1246 E.38.2. Migration from pre-1.09 to version 6.0 ...1246 E.38.3. Changes ...1246 E.39. Release 1.09 ...1248 E.40. Release 1.02 ...1248 E.40.1. Migration from version 1.02 to version 1.02.1...1248 E.40.2. Dump/Reload Procedure ...1249 E.40.3. Changes ...1249 E.41. Release 1.01 ...1250 E.41.1. Migration from version 1.0 to version 1.01...1250 E.41.2. Changes ...1252 E.42. Release 1.0 ...1252
E.43. Postgres95 Release 0.03...1253 E.43.1. Changes ...1254 E.44. Postgres95 Release 0.02...1256 E.44.1. Changes ...1256 E.45. Postgres95 Release 0.01...1257 F. The CVS Repository ...1258 F.1. Getting The Source Via Anonymous CVS ...1258 F.2. CVS Tree Organization ...1259 F.3. Getting The Source Via CVSup...1261 F.3.1. Preparing A CVSup Client System...1261 F.3.2. Running a CVSup Client ...1261 F.3.3. Installing CVSup...1263 F.3.4. Installation from Sources ...1264 G. Documentation ...1266 G.1. DocBook ...1266 G.2. Tool Sets ...1266 G.2.1. Linux RPM Installation...1267 G.2.2. FreeBSD Installation...1267 G.2.3. Debian Packages ...1268 G.2.4. Manual Installation from Source...1268 G.2.4.1. Installing OpenJade ...1268 G.2.4.2. Installing the DocBook DTD Kit ...1269 G.2.4.3. Installing the DocBook DSSSL Style Sheets ...1270 G.2.4.4. Installing JadeTeX ...1270 G.2.5. Detection byconfigure...1270 G.3. Building The Documentation ...1271 G.3.1. HTML ...1271 G.3.2. Manpages ...1271 G.3.3. Print Output via JadeTex...1272 G.3.4. Print Output via RTF...1272 G.3.5. Plain Text Files...1274 G.3.6. Syntax Check ...1274 G.4. Documentation Authoring ...1274 G.4.1. Emacs/PSGML...1274 G.4.2. Other Emacs modes ...1276 G.5. Style Guide ...1276 G.5.1. Reference Pages ...1276 Bibliography ...1279 Index...1281
List of Tables
4-1. Operator Precedence (decreasing)...27 8-1. Data Types ...75 8-2. Numeric Types...76 8-3. Monetary Types ...80 8-4. Character Types ...80 8-5. Special Character Types ...82 8-6. Binary Data Types ...82 8-7.byteaLiteral Escaped Octets ...83 8-8.byteaOutput Escaped Octets...83 8-9. Date/Time Types...84 8-10. Date Input ...85 8-11. Time Input ...86 8-12. Time Zone Input ...87 8-13. Special Date/Time Inputs ...88 8-14. Date/Time Output Styles ...88 8-15. Date Order Conventions ...89 8-16. Geometric Types...91 8-17. Network Address Types ...94 8-18.cidrType Input Examples ...94 8-19. Object Identifier Types ...105 8-20. Pseudo-Types...106 9-1. Comparison Operators...107 9-2. Mathematical Operators ...109 9-3. Bit String Bitwise Operators ...110 9-4. Mathematical Functions ...110 9-5. Trigonometric Functions ...111 9-6. SQL String Functions and Operators ...112 9-7. Other String Functions ...113 9-8. Built-in Conversions...117 9-9. SQL Binary String Functions and Operators ...120 9-10. Other Binary String Functions ...121 9-11. Regular Expression Match Operators...124 9-12. Regular Expression Atoms ...126 9-13. Regular Expression Quantifiers...126 9-14. Regular Expression Constraints ...127 9-15. Regular Expression Character-Entry Escapes ...129 9-16. Regular Expression Class-Shorthand Escapes ...130 9-17. Regular Expression Constraint Escapes ...130 9-18. Regular Expression Back References...130 9-19. ARE Embedded-Option Letters ...131 9-20. Formatting Functions ...134 9-21. Template Patterns for Date/Time Formatting ...135 9-22. Template Pattern Modifiers for Date/Time Formatting ...136 9-23. Template Patterns for Numeric Formatting ...137 9-24.to_charExamples ...138 9-25. Date/Time Operators ...139
9-27.AT TIME ZONEVariants ...146 9-28. Geometric Operators ...148 9-29. Geometric Functions ...149 9-30. Geometric Type Conversion Functions ...150 9-31.cidrandinetOperators ...151 9-32.cidrandinetFunctions ...152 9-33.macaddrFunctions ...153 9-34. Sequence Functions...153 9-35. Session Information Functions...157 9-36. Configuration Settings Functions ...158 9-37. Access Privilege Inquiry Functions...158 9-38. Schema Visibility Inquiry Functions ...160 9-39. System Catalog Information Functions...161 9-40. Comment Information Functions ...161 9-41.arrayOperators ...162 9-42.arrayFunctions ...163 9-43. Aggregate Functions...164 12-1. SQL Transaction Isolation Levels ...191 16-1. Short option key ...249 16-2. System V IPC parameters...250 20-1. Server Character Sets ...279 20-2. Client/Server Character Set Conversions ...281 23-1. Standard Statistics Views ...297 23-2. Statistics Access Functions ...298 29-1. pgtcl Commands...369 31-1.ConnectionPoolDataSourceImplementations ...445 31-2.ConnectionPoolDataSourceConfiguration Properties...445 31-3.DataSourceImplementations ...446 31-4.DataSourceConfiguration Properties ...446 31-5. Additional PoolingDataSourceConfiguration Properties ...447 32-1.information_schema_catalog_nameColumns...451 32-2.applicable_rolesColumns ...451 32-3.check_constraintsColumns...451 32-4.column_domain_usageColumns ...452 32-5.column_privilegesColumns...452 32-6.column_udt_usageColumns ...453 32-7.columnsColumns ...454 32-8.constraint_column_usageColumns...458 32-9.constraint_table_usageColumns ...458 32-10.domain_constraintsColumns...459 32-11.domain_constraintsColumns...460 32-12.domain_udt_usageColumns ...461 32-13.domainsColumns ...461 32-14.element_typesColumns ...464 32-15.enabled_rolesColumns ...466 32-16.key_column_usageColumns ...467 32-17.parametersColumns ...467
32-19.role_column_grantsColumns...471 32-20.role_routine_grantsColumns ...471 32-21.role_table_grantsColumns...472 32-22.role_usage_grantsColumns...473 32-23.routine_privilegesColumns...473 32-24.routinesColumns ...474 32-25.schemataColumns ...478 32-26.sql_featuresColumns...479 32-27.sql_implementation_infoColumns...480 32-28.sql_languagesColumns ...480 32-29.sql_packagesColumns...481 32-30.sql_sizingColumns ...481 32-31.sql_sizing_profilesColumns ...482 32-32.table_constraintsColumns...482 32-33.table_privilegesColumns ...483 32-34.tablesColumns...484 32-35.triggersColumns ...485 32-36.usage_privilegesColumns ...486 32-37.view_column_usageColumns...486 32-38.view_table_usageColumns ...487 32-39.viewsColumns...488 33-1. Equivalent C Types for Built-In SQL Types ...503 33-2. B-tree Strategies ...534 33-3. Hash Strategies ...534 33-4. R-tree Strategies ...535 33-5. B-tree Support Functions...535 33-6. Hash Support Functions ...536 33-7. R-tree Support Functions...536 33-8. GiST Support Functions...536 43-1. System Catalogs ...993 43-2.pg_aggregateColumns...994 43-3.pg_amColumns...995 43-4.pg_amopColumns ...996 43-5.pg_amprocColumns ...996 43-6.pg_attrdefColumns ...997 43-7.pg_attributeColumns...997 43-8.pg_castColumns ...1000 43-9.pg_classColumns ...1001 43-10.pg_constraintColumns ...1004 43-11.pg_conversionColumns ...1005 43-12.pg_databaseColumns...1005 43-13.pg_dependColumns ...1007 43-14.pg_descriptionColumns ...1009 43-15.pg_groupColumns ...1009 43-16.pg_indexColumns ...1010 43-17.pg_inheritsColumns...1011 43-18.pg_languageColumns...1011
43-20.pg_listenerColumns...1013 43-21.pg_namespaceColumns...1014 43-22.pg_opclassColumns ...1014 43-23.pg_operatorColumns...1015 43-24.pg_procColumns ...1016 43-25.pg_rewriteColumns ...1018 43-26.pg_shadowColumns ...1019 43-27.pg_statisticColumns...1020 43-28.pg_triggerColumns ...1022 43-29.pg_typeColumns ...1023 43-30. System Views ...1030 43-31.pg_indexesColumns ...1030 43-32.pg_locksColumns ...1031 43-33.pg_rulesColumns ...1032 43-34.pg_settingsColumns...1032 43-35.pg_statsColumns ...1033 43-36.pg_tablesColumns ...1035 43-37.pg_userColumns ...1036 43-38.pg_viewsColumns ...1036 51-1. Sample Page Layout ...1095 51-2. PageHeaderData Layout...1095 51-3. HeapTupleHeaderData Layout ...1096 A-1. PostgreSQL Error Codes ...1101 B-1. Month Abbreviations ...1109 B-2. Day of the Week Abbreviations...1109 B-3. Date/Time Field Modifiers...1110 B-4. Time Zone Abbreviations ...1110 B-5. Australian Time Zone Abbreviations...1114 C-1. SQL Key Words ...1116
List of Figures
48-1. Structured Diagram of a Genetic Algorithm ...1087
List of Examples
8-1. Using the character types ...81 8-2. Using thebooleantype...91 8-3. Using the bit string types...96 10-1. Exponentiation Operator Type Resolution ...175 10-2. String Concatenation Operator Type Resolution...175 10-3. Absolute-Value and Factorial Operator Type Resolution...175 10-4. Rounding Function Argument Type Resolution...177 10-5. Substring Function Type Resolution ...178 10-6.characterStorage Type Conversion ...179
10-8. Type Resolution in a Simple Union...180 10-9. Type Resolution in a Transposed Union...180 11-1. Setting up a Partial Index to Exclude Common Values...187 11-2. Setting up a Partial Index to Exclude Uninteresting Values...187 11-3. Setting up a Partial Unique Index...188 19-1. An examplepg_hba.conffile...270 19-2. An examplepg_ident.conffile ...275 27-1. libpq Example Program 1...351 27-2. libpq Example Program 2...353 27-3. libpq Example Program 3...356 28-1. Large Objects with libpq Example Program ...363 29-1. pgtcl Example Program ...393 31-1. Processing a Simple Query in JDBC...415 31-2. Setting fetch size to turn cursors on and off. ...415 31-3. Deleting Rows in JDBC ...417 31-4. Calling a built in stored function ...417 31-5. Gettingrefcursorvalues from a function...418 31-6. Treatingrefcursoras a distinct type ...418 31-7. Dropping a Table in JDBC ...419 31-8. Processing Binary Data in JDBC ...420 31-9.DataSourceCode Example...447 31-10.DataSourceJNDI Code Example ...448 36-1. Manual Installation of PL/pgSQL ...574 37-1. A PL/pgSQL Trigger Procedure...601 37-2. Porting a Simple Function from PL/SQL to PL/pgSQL ...602 37-3. Porting a Function that Creates Another Function from PL/SQL to PL/pgSQL ...603 37-4. Porting a Procedure With String Manipulation andOUTParameters from PL/SQL to PL/pgSQL604 37-5. Porting a Procedure from PL/SQL to PL/pgSQL...606
Preface
This book is the official documentation of PostgreSQL. It is being written by the PostgreSQL develop- ers and other volunteers in parallel to the development of the PostgreSQL software. It describes all the functionality that the current version of PostgreSQL officially supports.
To make the large amount of information about PostgreSQL manageable, this book has been organized in several parts. Each part is targeted at a different class of users, or at users in different stages of their PostgreSQL experience:
• Part I is an informal introduction for new users.
• Part II documents the SQL query language environment, including data types and functions, as well as user-level performance tuning. Every PostgreSQL user should read this.
• Part III describes the installation and administration of the server. Everyone that runs a PostgreSQL server, be it for private use or for others, should read this part.
• Part IV describes the programming interfaces for PostgreSQL client programs.
• Part V contains information for advanced users about the extensibility capabilities of the server. Topics are, for instance, user-defined data types and functions.
• Part VI contains information about the syntax of SQL commands, client and server programs. This part supports the other parts with structured information sorted by command or program.
• Part VII contains assorted information that can be of use to PostgreSQL developers.
1. What is PostgreSQL?
PostgreSQL is an object-relational database management system (ORDBMS) based on POSTGRES, Version 4.21, developed at the University of California at Berkeley Computer Science Department. POST- GRES pioneered many concepts that only became available in some commercial database systems much later.
PostgreSQL is an open-source descendant of this original Berkeley code. It supports SQL92 and SQL99 and offers many modern features:
• complex queries
• foreign keys
• triggers
• views
• transactional integrity
• multiversion concurrency control
Also, PostgreSQL can be extended by the user in many ways, for example by adding new
• data types
• functions
1. http://s2k-ftp.CS.Berkeley.EDU:8000/postgres/postgres.html
• operators
• aggregate functions
• index methods
• procedural languages
And because of the liberal license, PostgreSQL can be used, modified, and distributed by everyone free of charge for any purpose, be it private, commercial, or academic.
2. A Brief History of PostgreSQL
The object-relational database management system now known as PostgreSQL is derived from the POST- GRES package written at the University of California at Berkeley. With over a decade of development behind it, PostgreSQL is now the most advanced open-source database available anywhere.
2.1. The Berkeley POSTGRES Project
The POSTGRES project, led by Professor Michael Stonebraker, was sponsored by the Defense Advanced Research Projects Agency (DARPA), the Army Research Office (ARO), the National Science Foundation (NSF), and ESL, Inc. The implementation of POSTGRES began in 1986. The initial concepts for the system were presented in The design of POSTGRES and the definition of the initial data model appeared in The POSTGRES data model. The design of the rule system at that time was described in The design of the POSTGRES rules system. The rationale and architecture of the storage manager were detailed in The design of the POSTGRES storage system.
POSTGRES has undergone several major releases since then. The first “demoware” system became op- erational in 1987 and was shown at the 1988 ACM-SIGMOD Conference. Version 1, described in The implementation of POSTGRES, was released to a few external users in June 1989. In response to a critique of the first rule system (A commentary on the POSTGRES rules system), the rule system was redesigned (On Rules, Procedures, Caching and Views in Database Systems) and Version 2 was released in June 1990 with the new rule system. Version 3 appeared in 1991 and added support for multiple storage managers, an improved query executor, and a rewritten rule system. For the most part, subsequent releases until Postgres95 (see below) focused on portability and reliability.
POSTGRES has been used to implement many different research and production applications. These in- clude: a financial data analysis system, a jet engine performance monitoring package, an asteroid tracking database, a medical information database, and several geographic information systems. POSTGRES has also been used as an educational tool at several universities. Finally, Illustra Information Technologies (later merged into Informix2, which is now owned by IBM3.) picked up the code and commercialized it.
In late 1992, POSTGRES became the primary data manager for the Sequoia 20004scientific computing project.
The size of the external user community nearly doubled during 1993. It became increasingly obvious that maintenance of the prototype code and support was taking up large amounts of time that should have been devoted to database research. In an effort to reduce this support burden, the Berkeley POSTGRES project officially ended with Version 4.2.
2. http://www.informix.com/
3. http://www.ibm.com/
4. http://meteora.ucsd.edu/s2k/s2k_home.html
2.2. Postgres95
In 1994, Andrew Yu and Jolly Chen added a SQL language interpreter to POSTGRES. Under a new name, Postgres95 was subsequently released to the web to find its own way in the world as an open- source descendant of the original POSTGRES Berkeley code.
Postgres95 code was completely ANSI C and trimmed in size by 25%. Many internal changes improved performance and maintainability. Postgres95 release 1.0.x ran about 30-50% faster on the Wisconsin Benchmark compared to POSTGRES, Version 4.2. Apart from bug fixes, the following were the major enhancements:
• The query language PostQUEL was replaced with SQL (implemented in the server). Subqueries were not supported until PostgreSQL (see below), but they could be imitated in Postgres95 with user-defined SQL functions. Aggregate functions were re-implemented. Support for theGROUP BYquery clause was also added.
• In addition to the monitor program, a new program (psql) was provided for interactive SQL queries, which used GNU Readline.
• A new front-end library,libpgtcl, supported Tcl-based clients. A sample shell,pgtclsh, provided new Tcl commands to interface Tcl programs with the Postgres95 server.
• The large-object interface was overhauled. The inversion large objects were the only mechanism for storing large objects. (The inversion file system was removed.)
• The instance-level rule system was removed. Rules were still available as rewrite rules.
• A short tutorial introducing regular SQL features as well as those of Postgres95 was distributed with the source code
• GNU make (instead of BSD make) was used for the build. Also, Postgres95 could be compiled with an unpatched GCC (data alignment of doubles was fixed).
2.3. PostgreSQL
By 1996, it became clear that the name “Postgres95” would not stand the test of time. We chose a new name, PostgreSQL, to reflect the relationship between the original POSTGRES and the more recent ver- sions with SQL capability. At the same time, we set the version numbering to start at 6.0, putting the numbers back into the sequence originally begun by the Berkeley POSTGRES project.
The emphasis during development of Postgres95 was on identifying and understanding existing problems in the server code. With PostgreSQL, the emphasis has shifted to augmenting features and capabilities, although work continues in all areas.
Details about what has happened in PostgreSQL since then can be found in Appendix E.
3. Conventions
This book uses the following typographical conventions to mark certain portions of text: new terms, foreign phrases, and other important passages are emphasized in italics. Everything that represents in-
put or output of the computer, in particular commands, program code, and screen output, is shown in a monospaced font (example). Within such passages, italics (example) indicate placeholders; you must insert an actual value instead of the placeholder. On occasion, parts of program code are emphasized in bold face (example), if they have been added or changed since the preceding example.
The following conventions are used in the synopsis of a command: brackets ([and]) indicate optional parts. (In the synopsis of a Tcl command, question marks (?) are used instead, as is usual in Tcl.) Braces ({and}) and vertical lines (|) indicate that you must choose one alternative. Dots (...) mean that the preceding element can be repeated.
Where it enhances the clarity, SQL commands are preceded by the prompt=>, and shell commands are preceded by the prompt$. Normally, prompts are not shown, though.
An administrator is generally a person who is in charge of installing and running the server. A user could be anyone who is using, or wants to use, any part of the PostgreSQL system. These terms should not be interpreted too narrowly; this book does not have fixed presumptions about system administration procedures.
4. Further Information
Besides the documentation, that is, this book, there are other resources about PostgreSQL:
FAQs
The FAQ list contains continuously updated answers to frequently asked questions.
READMEs
READMEfiles are available for most contributed packages.
Web Site
The PostgreSQL web site5carries details on the latest release and other information to make your work or play with PostgreSQL more productive.
Mailing Lists
The mailing lists are a good place to have your questions answered, to share experiences with other users, and to contact the developers. Consult the PostgreSQL web site for details.
Yourself!
PostgreSQL is an open-source project. As such, it depends on the user community for ongoing sup- port. As you begin to use PostgreSQL, you will rely on others for help, either through the documen- tation or through the mailing lists. Consider contributing your knowledge back. Read the mailing lists and answer questions. If you learn something which is not in the documentation, write it up and contribute it. If you add features to the code, contribute them.
5. http://www.postgresql.org
5. Bug Reporting Guidelines
When you find a bug in PostgreSQL we want to hear about it. Your bug reports play an important part in making PostgreSQL more reliable because even the utmost care cannot guarantee that every part of PostgreSQL will work on every platform under every circumstance.
The following suggestions are intended to assist you in forming bug reports that can be handled in an effective fashion. No one is required to follow them but it tends to be to everyone’s advantage.
We cannot promise to fix every bug right away. If the bug is obvious, critical, or affects a lot of users, chances are good that someone will look into it. It could also happen that we tell you to update to a newer version to see if the bug happens there. Or we might decide that the bug cannot be fixed before some major rewrite we might be planning is done. Or perhaps it is simply too hard and there are more important things on the agenda. If you need help immediately, consider obtaining a commercial support contract.
5.1. Identifying Bugs
Before you report a bug, please read and re-read the documentation to verify that you can really do whatever it is you are trying. If it is not clear from the documentation whether you can do something or not, please report that too; it is a bug in the documentation. If it turns out that a program does something different from what the documentation says, that is a bug. That might include, but is not limited to, the following circumstances:
• A program terminates with a fatal signal or an operating system error message that would point to a problem in the program. (A counterexample might be a “disk full” message, since you have to fix that yourself.)
• A program produces the wrong output for any given input.
• A program refuses to accept valid input (as defined in the documentation).
• A program accepts invalid input without a notice or error message. But keep in mind that your idea of invalid input might be our idea of an extension or compatibility with traditional practice.
• PostgreSQL fails to compile, build, or install according to the instructions on supported platforms.
Here “program” refers to any executable, not only the backend server.
Being slow or resource-hogging is not necessarily a bug. Read the documentation or ask on one of the mailing lists for help in tuning your applications. Failing to comply to the SQL standard is not necessarily a bug either, unless compliance for the specific feature is explicitly claimed.
Before you continue, check on the TODO list and in the FAQ to see if your bug is already known. If you cannot decode the information on the TODO list, report your problem. The least we can do is make the TODO list clearer.
5.2. What to report
The most important thing to remember about bug reporting is to state all the facts and only facts. Do not speculate what you think went wrong, what “it seemed to do”, or which part of the program has a fault.
If you are not familiar with the implementation you would probably guess wrong and not help us a bit.
And even if you are, educated explanations are a great supplement to but no substitute for facts. If we are going to fix the bug we still have to see it happen for ourselves first. Reporting the bare facts is relatively
straightforward (you can probably copy and paste them from the screen) but all too often important details are left out because someone thought it does not matter or the report would be understood anyway.
The following items should be contained in every bug report:
• The exact sequence of steps from program start-up necessary to reproduce the problem. This should be self-contained; it is not enough to send in a bareSELECTstatement without the precedingCREATE TABLEandINSERTstatements, if the output should depend on the data in the tables. We do not have the time to reverse-engineer your database schema, and if we are supposed to make up our own data we would probably miss the problem.
The best format for a test case for SQL-related problems is a file that can be run through the psql frontend that shows the problem. (Be sure to not have anything in your~/.psqlrcstart-up file.) An easy start at this file is to use pg_dump to dump out the table declarations and data needed to set the scene, then add the problem query. You are encouraged to minimize the size of your example, but this is not absolutely necessary. If the bug is reproducible, we will find it either way.
If your application uses some other client interface, such as PHP, then please try to isolate the offending queries. We will probably not set up a web server to reproduce your problem. In any case remember to provide the exact input files; do not guess that the problem happens for “large files” or “midsize databases”, etc. since this information is too inexact to be of use.
• The output you got. Please do not say that it “didn’t work” or “crashed”. If there is an error message, show it, even if you do not understand it. If the program terminates with an operating system error, say which. If nothing at all happens, say so. Even if the result of your test case is a program crash or otherwise obvious it might not happen on our platform. The easiest thing is to copy the output from the terminal, if possible.
Note: If you are reporting an error message, please obtain the most verbose form of the message.
In psql, say \set VERBOSITY verbosebeforehand. If you are extracting the message from the server log, set the run-time parameterlog_error_verbosityto verboseso that all details are logged.
Note: In case of fatal errors, the error message reported by the client might not contain all the information available. Please also look at the log output of the database server. If you do not keep your server’s log output, this would be a good time to start doing so.
• The output you expected is very important to state. If you just write “This command gives me that output.” or “This is not what I expected.”, we might run it ourselves, scan the output, and think it looks OK and is exactly what we expected. We should not have to spend the time to decode the exact semantics behind your commands. Especially refrain from merely saying that “This is not what SQL says/Oracle does.” Digging out the correct behavior from SQL is not a fun undertaking, nor do we all know how all the other relational databases out there behave. (If your problem is a program crash, you can obviously omit this item.)
• Any command line options and other start-up options, including concerned environment variables or configuration files that you changed from the default. Again, be exact. If you are using a prepackaged distribution that starts the database server at boot time, you should try to find out how that is done.
• Anything you did at all differently from the installation instructions.
• The PostgreSQL version. You can run the commandSELECT version();to find out the version of the server you are connected to. Most executable programs also support a--versionoption; at least postmaster --versionandpsql --versionshould work. If the function or the options do not exist then your version is more than old enough to warrant an upgrade. If you run a prepackaged version, such as RPMs, say so, including any subversion the package may have. If you are talking about a CVS snapshot, mention that, including its date and time.
If your version is older than 7.4.2 we will almost certainly tell you to upgrade. There are tons of bug fixes in each new release, that is why we make new releases.
• Platform information. This includes the kernel name and version, C library, processor, memory infor- mation. In most cases it is sufficient to report the vendor and version, but do not assume everyone knows what exactly “Debian” contains or that everyone runs on Pentiums. If you have installation problems then information about compilers, make, etc. is also necessary.
Do not be afraid if your bug report becomes rather lengthy. That is a fact of life. It is better to report everything the first time than us having to squeeze the facts out of you. On the other hand, if your input files are huge, it is fair to ask first whether somebody is interested in looking into it.
Do not spend all your time to figure out which changes in the input make the problem go away. This will probably not help solving it. If it turns out that the bug cannot be fixed right away, you will still have time to find and share your work-around. Also, once again, do not waste your time guessing why the bug exists.
We will find that out soon enough.
When writing a bug report, please choose non-confusing terminology. The software package in total is called “PostgreSQL”, sometimes “Postgres” for short. If you are specifically talking about the backend server, mention that, do not just say “PostgreSQL crashes”. A crash of a single backend server process is quite different from crash of the parent “postmaster” process; please don’t say “the postmaster crashed”
when you mean a single backend process went down, nor vice versa. Also, client programs such as the interactive frontend “psql” are completely separate from the backend. Please try to be specific about whether the problem is on the client or server side.
5.3. Where to report bugs
In general, send bug reports to the bug report mailing list at <[email protected]>. You are requested to use a descriptive subject for your email message, perhaps parts of the error message.
Another method is to fill in the bug report web-form available at the project’s web site http://www.postgresql.org/. Entering a bug report this way causes it to be mailed to the
<[email protected]> mailing list.
Do not send bug reports to any of the user mailing lists, such as <[email protected]> or
<[email protected]>. These mailing lists are for answering user questions, and their subscribers normally do not wish to receive bug reports. More importantly, they are unlikely to fix them.
