PostgreSQL 8.0.0beta5 Documentation

The PostgreSQL Global Development Group

Legal Notice

Table of Contents

1. Preface
1.1. What is PostgreSQL?
1.2. A Brief History of PostgreSQL
1.2.1. The Berkeley POSTGRES Project
1.2.2. Postgres95
1.2.3. PostgreSQL
1.3. Conventions
1.4. Further Information
1.5. Bug Reporting Guidelines
1.5.1. Identifying Bugs
1.5.2. What to report
1.5.3. Where to report bugs
I. Tutorial
1. Getting Started
1.1. Installation
1.2. Architectural Fundamentals
1.3. Creating a Database
1.4. Accessing a Database
2. The SQL Language
2.1. Introduction
2.2. Concepts
2.3. Creating a New Table
2.4. Populating a Table With Rows
2.5. Querying a Table
2.6. Joins Between Tables
2.7. Aggregate Functions
2.8. Updates
2.9. Deletions
3. Advanced Features
3.1. Introduction
3.2. Views
3.3. Foreign Keys
3.4. Transactions
3.5. Inheritance
3.6. Conclusion
II. The SQL Language
4. SQL Syntax
4.1. Lexical Structure
4.1.1. Identifiers and Key Words
4.1.2. Constants
4.1.3. Operators
4.1.4. Special Characters
4.1.5. Comments
4.1.6. Lexical Precedence
4.2. Value Expressions
4.2.1. Column References
4.2.2. Positional Parameters
4.2.3. Subscripts
4.2.4. Field Selection
4.2.5. Operator Invocations
4.2.6. Function Calls
4.2.7. Aggregate Expressions
4.2.8. Type Casts
4.2.9. Scalar Subqueries
4.2.10. Array Constructors
4.2.11. Row Constructors
4.2.12. Expression Evaluation Rules
5. Data Definition
5.1. Table Basics
5.2. System Columns
5.3. Default Values
5.4. Constraints
5.4.1. Check Constraints
5.4.2. Not-Null Constraints
5.4.3. Unique Constraints
5.4.4. Primary Keys
5.4.5. Foreign Keys
5.5. Inheritance
5.6. Modifying Tables
5.6.1. Adding a Column
5.6.2. Removing a Column
5.6.3. Adding a Constraint
5.6.4. Removing a Constraint
5.6.5. Changing the Default
5.6.6. Renaming a Column
5.6.7. Renaming a Table
5.7. Privileges
5.8. Schemas
5.8.1. Creating a Schema
5.8.2. The Public Schema
5.8.3. The Schema Search Path
5.8.4. Schemas and Privileges
5.8.5. The System Catalog Schema
5.8.6. Usage Patterns
5.8.7. Portability
5.9. Other Database Objects
5.10. Dependency Tracking
6. Data Manipulation
6.1. Inserting Data
6.2. Updating Data
6.3. Deleting Data
7. Queries
7.1. Overview
7.2. Table Expressions
7.2.1. The FROM Clause
7.2.2. The WHERE Clause
7.2.3. The GROUP BY and HAVING Clauses
7.3. Select Lists
7.3.1. Select-List Items
7.3.2. Column Labels
7.3.3. DISTINCT
7.4. Combining Queries
7.5. Sorting Rows
7.6. LIMIT and OFFSET
8. Data Types
8.1. Numeric Types
8.1.1. Integer Types
8.1.2. Arbitrary Precision Numbers
8.1.3. Floating-Point Types
8.1.4. Serial Types
8.2. Monetary Types
8.3. Character Types
8.4. Binary Data Types
8.5. Date/Time Types
8.5.1. Date/Time Input
8.5.2. Date/Time Output
8.5.3. Time Zones
8.5.4. Internals
8.6. Boolean Type
8.7. Geometric Types
8.7.1. Points
8.7.2. Line Segments
8.7.3. Boxes
8.7.4. Paths
8.7.5. Polygons
8.7.6. Circles
8.8. Network Address Types
8.8.1. inet
8.8.2. cidr
8.8.3. inet vs. cidr
8.8.4. macaddr
8.9. Bit String Types
8.10. Arrays
8.10.1. Declaration of Array Types
8.10.2. Array Value Input
8.10.3. Accessing Arrays
8.10.4. Modifying Arrays
8.10.5. Searching in Arrays
8.10.6. Array Input and Output Syntax
8.11. Composite Types
8.11.1. Declaration of Composite Types
8.11.2. Composite Value Input
8.11.3. Accessing Composite Types
8.11.4. Modifying Composite Types
8.11.5. Composite Type Input and Output Syntax
8.12. Object Identifier Types
8.13. Pseudo-Types
9. Functions and Operators
9.1. Logical Operators
9.2. Comparison Operators
9.3. Mathematical Functions and Operators
9.4. String Functions and Operators
9.5. Binary String Functions and Operators
9.6. Bit String Functions and Operators
9.7. Pattern Matching
9.7.1. LIKE
9.7.2. SIMILAR TO and SQL99 Regular Expressions
9.7.3. POSIX Regular Expressions
9.8. Data Type Formatting Functions
9.9. Date/Time Functions and Operators
9.9.1. EXTRACT, date_part
9.9.2. date_trunc
9.9.3. AT TIME ZONE
9.9.4. Current Date/Time
9.10. Geometric Functions and Operators
9.11. Network Address Type Functions
9.12. Sequence Manipulation Functions
9.13. Conditional Expressions
9.13.1. CASE
9.13.2. COALESCE
9.13.3. NULLIF
9.14. Array Functions and Operators
9.15. Aggregate Functions
9.16. Subquery Expressions
9.16.1. EXISTS
9.16.2. IN
9.16.3. NOT IN
9.16.4. ANY/SOME
9.16.5. ALL
9.16.6. Row-wise Comparison
9.17. Row and Array Comparisons
9.17.1. IN
9.17.2. NOT IN
9.17.3. ANY/SOME (array)
9.17.4. ALL (array)
9.17.5. Row-wise Comparison
9.18. Set Returning Functions
9.19. System Information Functions
9.20. System Administration Functions
10. Type Conversion
10.1. Overview
10.2. Operators
10.3. Functions
10.4. Value Storage
10.5. UNION, CASE, and ARRAY Constructs
11. Indexes
11.1. Introduction
11.2. Index Types
11.3. Multicolumn Indexes
11.4. Unique Indexes
11.5. Indexes on Expressions
11.6. Operator Classes
11.7. Partial Indexes
11.8. Examining Index Usage
12. Concurrency Control
12.1. Introduction
12.2. Transaction Isolation
12.2.1. Read Committed Isolation Level
12.2.2. Serializable Isolation Level
12.3. Explicit Locking
12.3.1. Table-Level Locks
12.3.2. Row-Level Locks
12.3.3. Deadlocks
12.4. Data Consistency Checks at the Application Level
12.5. Locking and Indexes
13. Performance Tips
13.1. Using EXPLAIN
13.2. Statistics Used by the Planner
13.3. Controlling the Planner with Explicit JOIN Clauses
13.4. Populating a Database
13.4.1. Disable Autocommit
13.4.2. Use COPY
13.4.3. Remove Indexes
13.4.4. Increase maintenance_work_mem
13.4.5. Increase checkpoint_segments
13.4.6. Run ANALYZE Afterwards
III. Server Administration
14. Installation Instructions
14.1. Short Version
14.2. Requirements
14.3. Getting The Source
14.4. If You Are Upgrading
14.5. Installation Procedure
14.6. Post-Installation Setup
14.6.1. Shared Libraries
14.6.2. Environment Variables
14.7. Supported Platforms
15. Installation on Windows
16. Server Run-time Environment
16.1. The PostgreSQL User Account
16.2. Creating a Database Cluster
16.3. Starting the Database Server
16.3.1. Server Start-up Failures
16.3.2. Client Connection Problems
16.4. Run-time Configuration
16.4.1. File Locations
16.4.2. Connections and Authentication
16.4.3. Resource Consumption
16.4.4. Write Ahead Log
16.4.5. Query Planning
16.4.6. Error Reporting and Logging
16.4.7. Runtime Statistics
16.4.8. Client Connection Defaults
16.4.9. Lock Management
16.4.10. Version and Platform Compatibility
16.4.11. Preset Options
16.4.12. Customized Options
16.4.13. Developer Options
16.4.14. Short Options
16.5. Managing Kernel Resources
16.5.1. Shared Memory and Semaphores
16.5.2. Resource Limits
16.5.3. Linux Memory Overcommit
16.6. Shutting Down the Server
16.7. Secure TCP/IP Connections with SSL
16.8. Secure TCP/IP Connections with SSH Tunnels
17. Database Users and Privileges
17.1. Database Users
17.2. User Attributes
17.3. Groups
17.4. Privileges
17.5. Functions and Triggers
18. Managing Databases
18.1. Overview
18.2. Creating a Database
18.3. Template Databases
18.4. Database Configuration
18.5. Destroying a Database
18.6. Tablespaces
19. Client Authentication
19.1. The pg_hba.conf file
19.2. Authentication methods
19.2.1. Trust authentication
19.2.2. Password authentication
19.2.3. Kerberos authentication
19.2.4. Ident-based authentication
19.2.5. PAM Authentication
19.3. Authentication problems
20. Localization
20.1. Locale Support
20.1.1. Overview
20.1.2. Benefits
20.1.3. Problems
20.2. Character Set Support
20.2.1. Supported Character Sets
20.2.2. Setting the Character Set
20.2.3. Automatic Character Set Conversion Between Server and Client
20.2.4. Further Reading
21. Routine Database Maintenance Tasks
21.1. Routine Vacuuming
21.1.1. Recovering disk space
21.1.2. Updating planner statistics
21.1.3. Preventing transaction ID wraparound failures
21.2. Routine Reindexing
21.3. Log File Maintenance
22. Backup and Restore
22.1. SQL Dump
22.1.1. Restoring the dump
22.1.2. Using pg_dumpall
22.1.3. Large Databases
22.1.4. Caveats
22.2. File system level backup
22.3. On-line backup and point-in-time recovery (PITR)
22.3.1. Setting up WAL archiving
22.3.2. Making a Base Backup
22.3.3. Recovering with an On-line Backup
22.3.4. Timelines
22.3.5. Caveats
22.4. Migration Between Releases
23. Monitoring Database Activity
23.1. Standard Unix Tools
23.2. The Statistics Collector
23.2.1. Statistics Collection Configuration
23.2.2. Viewing Collected Statistics
23.3. Viewing Locks
24. Monitoring Disk Usage
24.1. Determining Disk Usage
24.2. Disk Full Failure
25. Write-Ahead Logging (WAL)
25.1. Benefits of WAL
25.2. WAL Configuration
25.3. Internals
26. Regression Tests
26.1. Running the Tests
26.2. Test Evaluation
26.2.1. Error message differences
26.2.2. Locale differences
26.2.3. Date and time differences
26.2.4. Floating-point differences
26.2.5. Row ordering differences
26.2.6. The random test
26.3. Platform-specific comparison files
IV. Client Interfaces
27. libpq - C Library
27.1. Database Connection Control Functions
27.2. Connection Status Functions
27.3. Command Execution Functions
27.3.1. Main Functions
27.3.2. Retrieving Query Result Information
27.3.3. Retrieving Result Information for Other Commands
27.3.4. Escaping Strings for Inclusion in SQL Commands
27.3.5. Escaping Binary Strings for Inclusion in SQL Commands
27.4. Asynchronous Command Processing
27.5. Cancelling Queries in Progress
27.6. The Fast-Path Interface
27.7. Asynchronous Notification
27.8. Functions Associated with the COPY Command
27.8.1. Functions for Sending COPY Data
27.8.2. Functions for Receiving COPY Data
27.8.3. Obsolete Functions for COPY
27.9. Control Functions
27.10. Notice Processing
27.11. Environment Variables
27.12. The Password File
27.13. SSL Support
27.14. Behavior in Threaded Programs
27.15. Building libpq Programs
27.16. Example Programs
28. Large Objects
28.1. History
28.2. Implementation Features
28.3. Client Interfaces
28.3.1. Creating a Large Object
28.3.2. Importing a Large Object
28.3.3. Exporting a Large Object
28.3.4. Opening an Existing Large Object
28.3.5. Writing Data to a Large Object
28.3.6. Reading Data from a Large Object
28.3.7. Seeking on a Large Object
28.3.8. Obtaining the Seek Position of a Large Object
28.3.9. Closing a Large Object Descriptor
28.3.10. Removing a Large Object
28.4. Server-Side Functions
28.5. Example Program
29. ECPG - Embedded SQL in C
29.1. The Concept
29.2. Connecting to the Database Server
29.3. Closing a Connection
29.4. Running SQL Commands
29.5. Choosing a Connection
29.6. Using Host Variables
29.6.1. Overview
29.6.2. Declare Sections
29.6.3. SELECT INTO and FETCH INTO
29.6.4. Indicators
29.7. Dynamic SQL
29.8. Using SQL Descriptor Areas
29.9. Error Handling
29.9.1. Setting Callbacks
29.9.2. sqlca
29.9.3. SQLSTATE vs SQLCODE
29.10. Including Files
29.11. Processing Embedded SQL Programs
29.12. Library Functions
29.13. Internals
30. The Information Schema
30.1. The Schema
30.2. Data Types
30.3. information_schema_catalog_name
30.4. applicable_roles
30.5. check_constraints
30.6. column_domain_usage
30.7. column_privileges
30.8. column_udt_usage
30.9. columns
30.10. constraint_column_usage
30.11. constraint_table_usage
30.12. data_type_privileges
30.13. domain_constraints
30.14. domain_udt_usage
30.15. domains
30.16. element_types
30.17. enabled_roles
30.18. key_column_usage
30.19. parameters
30.20. referential_constraints
30.21. role_column_grants
30.22. role_routine_grants
30.23. role_table_grants
30.24. role_usage_grants
30.25. routine_privileges
30.26. routines
30.27. schemata
30.28. sql_features
30.29. sql_implementation_info
30.30. sql_languages
30.31. sql_packages
30.32. sql_sizing
30.33. sql_sizing_profiles
30.34. table_constraints
30.35. table_privileges
30.36. tables
30.37. triggers
30.38. usage_privileges
30.39. view_column_usage
30.40. view_table_usage
30.41. views
V. Server Programming
31. Extending SQL
31.1. How Extensibility Works
31.2. The PostgreSQL Type System
31.2.1. Base Types
31.2.2. Composite Types
31.2.3. Domains
31.2.4. Pseudo-Types
31.2.5. Polymorphic Types
31.3. User-Defined Functions
31.4. Query Language (SQL) Functions
31.4.1. SQL Functions on Base Types
31.4.2. SQL Functions on Composite Types
31.4.3. SQL Functions as Table Sources
31.4.4. SQL Functions Returning Sets
31.4.5. Polymorphic SQL Functions
31.5. Procedural Language Functions
31.6. Internal Functions
31.7. C-Language Functions
31.7.1. Dynamic Loading
31.7.2. Base Types in C-Language Functions
31.7.3. Calling Conventions Version 0 for C-Language Functions
31.7.4. Calling Conventions Version 1 for C-Language Functions
31.7.5. Writing Code
31.7.6. Compiling and Linking Dynamically-Loaded Functions
31.7.7. Extension build infrastructure
31.7.8. Composite-Type Arguments in C-Language Functions
31.7.9. Returning Rows (Composite Types) from C-Language Functions
31.7.10. Returning Sets from C-Language Functions
31.7.11. Polymorphic Arguments and Return Types
31.8. Function Overloading
31.9. Function Volatility Categories
31.10. User-Defined Aggregates
31.11. User-Defined Types
31.12. User-Defined Operators
31.13. Operator Optimization Information
31.13.1. COMMUTATOR
31.13.2. NEGATOR
31.13.3. RESTRICT
31.13.4. JOIN
31.13.5. HASHES
31.13.6. MERGES (SORT1, SORT2, LTCMP, GTCMP)
31.14. Interfacing Extensions To Indexes
31.14.1. Index Methods and Operator Classes
31.14.2. Index Method Strategies
31.14.3. Index Method Support Routines
31.14.4. An Example
31.14.5. Cross-Data-Type Operator Classes
31.14.6. System Dependencies on Operator Classes
31.14.7. Special Features of Operator Classes
32. The Rule System
32.1. The Query Tree
32.2. Views and the Rule System
32.2.1. How SELECT Rules Work
32.2.2. View Rules in Non-SELECT Statements
32.2.3. The Power of Views in PostgreSQL
32.2.4. Updating a View
32.3. Rules on INSERT, UPDATE, and DELETE
32.3.1. How Update Rules Work
32.3.2. Cooperation with Views
32.4. Rules and Privileges
32.5. Rules and Command Status
32.6. Rules versus Triggers
33. Triggers
33.1. Overview of Trigger Behavior
33.2. Visibility of Data Changes
33.3. Writing Trigger Functions in C
33.4. A Complete Example
34. Procedural Languages
34.1. Installing Procedural Languages
35. PL/pgSQL - SQL Procedural Language
35.1. Overview
35.1.1. Advantages of Using PL/pgSQL
35.1.2. Supported Argument and Result Data Types
35.2. Tips for Developing in PL/pgSQL
35.2.1. Handling of Quotation Marks
35.3. Structure of PL/pgSQL
35.4. Declarations
35.4.1. Aliases for Function Parameters
35.4.2. Copying Types
35.4.3. Row Types
35.4.4. Record Types
35.4.5. RENAME
35.5. Expressions
35.6. Basic Statements
35.6.1. Assignment
35.6.2. SELECT INTO
35.6.3. Executing an Expression or Query With No Result
35.6.4. Doing Nothing At All
35.6.5. Executing Dynamic Commands
35.6.6. Obtaining the Result Status
35.7. Control Structures
35.7.1. Returning From a Function
35.7.2. Conditionals
35.7.3. Simple Loops
35.7.4. Looping Through Query Results
35.7.5. Trapping Errors
35.8. Cursors
35.8.1. Declaring Cursor Variables
35.8.2. Opening Cursors
35.8.3. Using Cursors
35.9. Errors and Messages
35.10. Trigger Procedures
35.11. Porting from Oracle PL/SQL
35.11.1. Porting Examples
35.11.2. Other Things to Watch For
35.11.3. Appendix
36. PL/Tcl - Tcl Procedural Language
36.1. Overview
36.2. PL/Tcl Functions and Arguments
36.3. Data Values in PL/Tcl
36.4. Global Data in PL/Tcl
36.5. Database Access from PL/Tcl
36.6. Trigger Procedures in PL/Tcl
36.7. Modules and the unknown command
36.8. Tcl Procedure Names
37. PL/Perl - Perl Procedural Language
37.1. PL/Perl Functions and Arguments
37.2. Database Access from PL/Perl
37.3. Data Values in PL/Perl
37.4. Global Values in PL/Perl
37.5. Trusted and Untrusted PL/Perl
37.6. PL/Perl Triggers
37.7. Limitations and Missing Features
38. PL/Python - Python Procedural Language
38.1. PL/Python Functions
38.2. Trigger Functions
38.3. Database Access
39. Server Programming Interface
39.1. Interface Functions
39.2. Interface Support Functions
39.3. Memory Management
39.4. Visibility of Data Changes
39.5. Examples
VI. Reference
I. SQL Commands
ABORT - abort the current transaction
ALTER AGGREGATE - change the definition of an aggregate function
ALTER CONVERSION - change the definition of a conversion
ALTER DATABASE - change a database
ALTER DOMAIN - change the definition of a domain
ALTER FUNCTION - change the definition of a function
ALTER GROUP - change a user group
ALTER INDEX - change the definition of an index
ALTER LANGUAGE - change the definition of a procedural language
ALTER OPERATOR - change the definition of an operator
ALTER OPERATOR CLASS - change the definition of an operator class
ALTER SCHEMA - change the definition of a schema
ALTER SEQUENCE - change the definition of a sequence generator
ALTER TABLE - change the definition of a table
ALTER TABLESPACE - change the definition of a tablespace
ALTER TRIGGER - change the definition of a trigger
ALTER TYPE - change the definition of a type
ALTER USER - change a database user account
ANALYZE - collect statistics about a database
BEGIN - start a transaction block
CHECKPOINT - force a transaction log checkpoint
CLOSE - close a cursor
CLUSTER - cluster a table according to an index
COMMENT - define or change the comment of an object
COMMIT - commit the current transaction
COPY - copy data between a file and a table
CREATE AGGREGATE - define a new aggregate function
CREATE CAST - define a new cast
CREATE CONSTRAINT TRIGGER - define a new constraint trigger
CREATE CONVERSION - define a new conversion
CREATE DATABASE - create a new database
CREATE DOMAIN - define a new domain
CREATE FUNCTION - define a new function
CREATE GROUP - define a new user group
CREATE INDEX - define a new index
CREATE LANGUAGE - define a new procedural language
CREATE OPERATOR - define a new operator
CREATE OPERATOR CLASS - define a new operator class
CREATE RULE - define a new rewrite rule
CREATE SCHEMA - define a new schema
CREATE SEQUENCE - define a new sequence generator
CREATE TABLE - define a new table
CREATE TABLE AS - define a new table from the results of a query
CREATE TABLESPACE - define a new tablespace
CREATE TRIGGER - define a new trigger
CREATE TYPE - define a new data type
CREATE USER - define a new database user account
CREATE VIEW - define a new view
DEALLOCATE - deallocate a prepared statement
DECLARE - define a cursor
DELETE - delete rows of a table
DROP AGGREGATE - remove an aggregate function
DROP CAST - remove a cast
DROP CONVERSION - remove a conversion
DROP DATABASE - remove a database
DROP DOMAIN - remove a domain
DROP FUNCTION - remove a function
DROP GROUP - remove a user group
DROP INDEX - remove an index
DROP LANGUAGE - remove a procedural language
DROP OPERATOR - remove an operator
DROP OPERATOR CLASS - remove an operator class
DROP RULE - remove a rewrite rule
DROP SCHEMA - remove a schema
DROP SEQUENCE - remove a sequence
DROP TABLE - remove a table
DROP TABLESPACE - remove a tablespace
DROP TRIGGER - remove a trigger
DROP TYPE - remove a data type
DROP USER - remove a database user account
DROP VIEW - remove a view
END - commit the current transaction
EXECUTE - execute a prepared statement
EXPLAIN - show the execution plan of a statement
FETCH - retrieve rows from a query using a cursor
GRANT - define access privileges
INSERT - create new rows in a table
LISTEN - listen for a notification
LOAD - load or reload a shared library file
LOCK - lock a table
MOVE - position a cursor
NOTIFY - generate a notification
PREPARE - prepare a statement for execution
REINDEX - rebuild indexes
RELEASE SAVEPOINT - destroy a previously defined savepoint
RESET - restore the value of a run-time parameter to the default value
REVOKE - remove access privileges
ROLLBACK - abort the current transaction
ROLLBACK TO SAVEPOINT - roll back to a savepoint
SAVEPOINT - define a new savepoint within the current transaction
SELECT - retrieve rows from a table or view
SELECT INTO - define a new table from the results of a query
SET - change a run-time parameter
SET CONSTRAINTS - set constraint checking modes for the current transaction
SET SESSION AUTHORIZATION - set the session user identifier and the current user identifier of the current session
SET TRANSACTION - set the characteristics of the current transaction
SHOW - show the value of a run-time parameter
START TRANSACTION - start a transaction block
TRUNCATE - empty a table
UNLISTEN - stop listening for a notification
UPDATE - update rows of a table
VACUUM - garbage-collect and optionally analyze a database
II. PostgreSQL Client Applications
clusterdb - cluster a PostgreSQL database
createdb - create a new PostgreSQL database
createlang - define a new PostgreSQL procedural language
createuser - define a new PostgreSQL user account
dropdb - remove a PostgreSQL database
droplang - remove a PostgreSQL procedural language
dropuser - remove a PostgreSQL user account
ecpg - embedded SQL C preprocessor
pg_config - retrieve information about the installed version of PostgreSQL
pg_dump - extract a PostgreSQL database into a script file or other archive file
pg_dumpall - extract a PostgreSQL database cluster into a script file
pg_restore - restore a PostgreSQL database from an archive file created by pg_dump
psql - PostgreSQL interactive terminal
vacuumdb - garbage-collect and analyze a PostgreSQL database
III. PostgreSQL Server Applications
initdb - create a new PostgreSQL database cluster
ipcclean - remove shared memory and semaphores from an aborted PostgreSQL server
pg_controldata - display control information of a PostgreSQL database cluster
pg_ctl - start, stop, or restart a PostgreSQL server
pg_resetxlog - reset the write-ahead log and other control information of a PostgreSQL database cluster
postgres - run a PostgreSQL server in single-user mode
postmaster - PostgreSQL multiuser database server
VII. Internals
40. Overview of PostgreSQL Internals
40.1. The Path of a Query
40.2. How Connections are Established
40.3. The Parser Stage
40.3.1. Parser
40.3.2. Transformation Process
40.4. The PostgreSQL Rule System
40.5. Planner/Optimizer
40.5.1. Generating Possible Plans
40.6. Executor
41. System Catalogs
41.1. Overview
41.2. pg_aggregate
41.3. pg_am
41.4. pg_amop
41.5. pg_amproc
41.6. pg_attrdef
41.7. pg_attribute
41.8. pg_cast
41.9. pg_class
41.10. pg_constraint
41.11. pg_conversion
41.12. pg_database
41.13. pg_depend
41.14. pg_description
41.15. pg_group
41.16. pg_index
41.17. pg_inherits
41.18. pg_language
41.19. pg_largeobject
41.20. pg_listener
41.21. pg_namespace
41.22. pg_opclass
41.23. pg_operator
41.24. pg_proc
41.25. pg_rewrite
41.26. pg_shadow
41.27. pg_statistic
41.28. pg_tablespace
41.29. pg_trigger
41.30. pg_type
41.31. System Views
41.32. pg_indexes
41.33. pg_locks
41.34. pg_rules
41.35. pg_settings
41.36. pg_stats
41.37. pg_tables
41.38. pg_user
41.39. pg_views
42. Frontend/Backend Protocol
42.1. Overview
42.1.1. Messaging Overview
42.1.2. Extended Query Overview
42.1.3. Formats and Format Codes
42.2. Message Flow
42.2.1. Start-Up
42.2.2. Simple Query
42.2.3. Extended Query
42.2.4. Function Call
42.2.5. COPY Operations
42.2.6. Asynchronous Operations
42.2.7. Cancelling Requests in Progress
42.2.8. Termination
42.2.9. SSL Session Encryption
42.3. Message Data Types
42.4. Message Formats
42.5. Error and Notice Message Fields
42.6. Summary of Changes since Protocol 2.0
43. PostgreSQL Coding Conventions
43.1. Formatting
43.2. Reporting Errors Within the Server
43.3. Error Message Style Guide
44. Native Language Support
44.1. For the Translator
44.1.1. Requirements
44.1.2. Concepts
44.1.3. Creating and maintaining message catalogs
44.1.4. Editing the PO files
44.2. For the Programmer
44.2.1. Mechanics
44.2.2. Message-writing guidelines
45. Writing A Procedural Language Handler
46. Genetic Query Optimizer
46.1. Query Handling as a Complex Optimization Problem
46.2. Genetic Algorithms
46.3. Genetic Query Optimization (GEQO) in PostgreSQL
46.3.1. Future Implementation Tasks for PostgreSQL GEQO
46.4. Further Readings
47. Index Cost Estimation Functions
48. GiST Indexes
48.1. Introduction
48.2. Extensibility
48.3. Implementation
48.4. Limitations
48.5. Examples
49. Database File Layout
50. Database Page Layout
51. BKI Backend Interface
51.1. BKI File Format
51.2. BKI Commands
51.3. Example
VIII. Appendixes
A. PostgreSQL Error Codes
B. Date/Time Support
B.1. Date/Time Input Interpretation
B.2. Date/Time Key Words
B.3. History of Units
C. SQL Key Words
D. SQL Conformance
D.1. Supported Features
D.2. Unsupported Features
E. Release Notes
E.1. Release 8.0
E.1.1. Overview
E.1.2. Migration to version 8.0
E.1.3. Deprecated Features
E.1.4. Changes
E.2. Release 7.4.6
E.2.1. Migration to version 7.4.6
E.2.2. Changes
E.3. Release 7.4.5
E.3.1. Migration to version 7.4.5
E.3.2. Changes
E.4. Release 7.4.4
E.4.1. Migration to version 7.4.4
E.4.2. Changes
E.5. Release 7.4.3
E.5.1. Migration to version 7.4.3
E.5.2. Changes
E.6. Release 7.4.2
E.6.1. Migration to version 7.4.2
E.6.2. Changes
E.7. Release 7.4.1
E.7.1. Migration to version 7.4.1
E.7.2. Changes
E.8. Release 7.4
E.8.1. Overview
E.8.2. Migration to version 7.4
E.8.3. Changes
E.9. Release 7.3.8
E.9.1. Migration to version 7.3.8
E.9.2. Changes
E.10. Release 7.3.7
E.10.1. Migration to version 7.3.7
E.10.2. Changes
E.11. Release 7.3.6
E.11.1. Migration to version 7.3.6
E.11.2. Changes
E.12. Release 7.3.5
E.12.1. Migration to version 7.3.5
E.12.2. Changes
E.13. Release 7.3.4
E.13.1. Migration to version 7.3.4
E.13.2. Changes
E.14. Release 7.3.3
E.14.1. Migration to version 7.3.3
E.14.2. Changes
E.15. Release 7.3.2
E.15.1. Migration to version 7.3.2
E.15.2. Changes
E.16. Release 7.3.1
E.16.1. Migration to version 7.3.1
E.16.2. Changes
E.17. Release 7.3
E.17.1. Overview
E.17.2. Migration to version 7.3
E.17.3. Changes
E.18. Release 7.2.6
E.18.1. Migration to version 7.2.6
E.18.2. Changes
E.19. Release 7.2.5
E.19.1. Migration to version 7.2.5
E.19.2. Changes
E.20. Release 7.2.4
E.20.1. Migration to version 7.2.4
E.20.2. Changes
E.21. Release 7.2.3
E.21.1. Migration to version 7.2.3
E.21.2. Changes
E.22. Release 7.2.2
E.22.1. Migration to version 7.2.2
E.22.2. Changes
E.23. Release 7.2.1
E.23.1. Migration to version 7.2.1
E.23.2. Changes
E.24. Release 7.2
E.24.1. Overview
E.24.2. Migration to version 7.2
E.24.3. Changes
E.25. Release 7.1.3
E.25.1. Migration to version 7.1.3
E.25.2. Changes
E.26. Release 7.1.2
E.26.1. Migration to version 7.1.2
E.26.2. Changes
E.27. Release 7.1.1
E.27.1. Migration to version 7.1.1
E.27.2. Changes
E.28. Release 7.1
E.28.1. Migration to version 7.1
E.28.2. Changes
E.29. Release 7.0.3
E.29.1. Migration to version 7.0.3
E.29.2. Changes
E.30. Release 7.0.2
E.30.1. Migration to version 7.0.2
E.30.2. Changes
E.31. Release 7.0.1
E.31.1. Migration to version 7.0.1
E.31.2. Changes
E.32. Release 7.0
E.32.1. Migration to version 7.0
E.32.2. Changes
E.33. Release 6.5.3
E.33.1. Migration to version 6.5.3
E.33.2. Changes
E.34. Release 6.5.2
E.34.1. Migration to version 6.5.2
E.34.2. Changes
E.35. Release 6.5.1
E.35.1. Migration to version 6.5.1
E.35.2. Changes
E.36. Release 6.5
E.36.1. Migration to version 6.5
E.36.2. Changes
E.37. Release 6.4.2
E.37.1. Migration to version 6.4.2
E.37.2. Changes
E.38. Release 6.4.1
E.38.1. Migration to version 6.4.1
E.38.2. Changes
E.39. Release 6.4
E.39.1. Migration to version 6.4
E.39.2. Changes
E.40. Release 6.3.2
E.40.1. Changes
E.41. Release 6.3.1
E.41.1. Changes
E.42. Release 6.3
E.42.1. Migration to version 6.3
E.42.2. Changes
E.43. Release 6.2.1
E.43.1. Migration from version 6.2 to version 6.2.1
E.43.2. Changes
E.44. Release 6.2
E.44.1. Migration from version 6.1 to version 6.2
E.44.2. Migration from version 1.x to version 6.2
E.44.3. Changes
E.45. Release 6.1.1
E.45.1. Migration from version 6.1 to version 6.1.1
E.45.2. Changes
E.46. Release 6.1
E.46.1. Migration to version 6.1
E.46.2. Changes
E.47. Release 6.0
E.47.1. Migration from version 1.09 to version 6.0
E.47.2. Migration from pre-1.09 to version 6.0
E.47.3. Changes
E.48. Release 1.09
E.49. Release 1.02
E.49.1. Migration from version 1.02 to version 1.02.1
E.49.2. Dump/Reload Procedure
E.49.3. Changes
E.50. Release 1.01
E.50.1. Migration from version 1.0 to version 1.01
E.50.2. Changes
E.51. Release 1.0
E.51.1. Changes
E.52. Postgres95 Release 0.03
E.52.1. Changes
E.53. Postgres95 Release 0.02
E.53.1. Changes
E.54. Postgres95 Release 0.01
F. The CVS Repository
F.1. Getting The Source Via Anonymous CVS
F.2. CVS Tree Organization
F.3. Getting The Source Via CVSup
F.3.1. Preparing A CVSup Client System
F.3.2. Running a CVSup Client
F.3.3. Installing CVSup
F.3.4. Installation from Sources
G. Documentation
G.1. DocBook
G.2. Tool Sets
G.2.1. Linux RPM Installation
G.2.2. FreeBSD Installation
G.2.3. Debian Packages
G.2.4. Manual Installation from Source
G.2.5. Detection by configure
G.3. Building The Documentation
G.3.1. HTML
G.3.2. Manpages
G.3.3. Print Output via JadeTex
G.3.4. Print Output via RTF
G.3.5. Plain Text Files
G.3.6. Syntax Check
G.4. Documentation Authoring
G.4.1. Emacs/PSGML
G.4.2. Other Emacs modes
G.5. Style Guide
G.5.1. Reference Pages
Bibliography

List of Figures

46.1. Structured Diagram of a Genetic Algorithm

List of Tables

4.1. Operator Precedence (decreasing)
8.1. Data Types
8.2. Numeric Types
8.3. Monetary Types
8.4. Character Types
8.5. Special Character Types
8.6. Binary Data Types
8.7. bytea Literal Escaped Octets
8.8. bytea Output Escaped Octets
8.9. Date/Time Types
8.10. Date Input
8.11. Time Input
8.12. Time Zone Input
8.13. Special Date/Time Inputs
8.14. Date/Time Output Styles
8.15. Date Order Conventions
8.16. Geometric Types
8.17. Network Address Types
8.18. cidr Type Input Examples
8.19. Object Identifier Types
8.20. Pseudo-Types
9.1. Comparison Operators
9.2. Mathematical Operators
9.3. Mathematical Functions
9.4. Trigonometric Functions
9.5. SQL String Functions and Operators
9.6. Other String Functions
9.7. Built-in Conversions
9.8. SQL Binary String Functions and Operators
9.9. Other Binary String Functions
9.10. Bit String Operators
9.11. Regular Expression Match Operators
9.12. Regular Expression Atoms
9.13. Regular Expression Quantifiers
9.14. Regular Expression Constraints
9.15. Regular Expression Character-Entry Escapes
9.16. Regular Expression Class-Shorthand Escapes
9.17. Regular Expression Constraint Escapes
9.18. Regular Expression Back References
9.19. ARE Embedded-Option Letters
9.20. Formatting Functions
9.21. Template Patterns for Date/Time Formatting
9.22. Template Pattern Modifiers for Date/Time Formatting
9.23. Template Patterns for Numeric Formatting
9.24. to_char Examples
9.25. Date/Time Operators
9.26. Date/Time Functions
9.27. AT TIME ZONE Variants
9.28. Geometric Operators
9.29. Geometric Functions
9.30. Geometric Type Conversion Functions
9.31. cidr and inet Operators
9.32. cidr and inet Functions
9.33. macaddr Functions
9.34. Sequence Functions
9.35. array Operators
9.36. array Functions
9.37. Aggregate Functions
9.38. Series Generating Functions
9.39. Session Information Functions
9.40. Access Privilege Inquiry Functions
9.41. Schema Visibility Inquiry Functions
9.42. System Catalog Information Functions
9.43. Comment Information Functions
9.44. Configuration Settings Functions
9.45. Backend Signalling Functions
9.46. Backup Control Functions
12.1. SQL Transaction Isolation Levels
16.1. Short option key
16.2. System V IPC parameters
20.1. Server Character Sets
20.2. Client/Server Character Set Conversions
23.1. Standard Statistics Views
23.2. Statistics Access Functions
30.1. information_schema_catalog_name Columns
30.2. applicable_roles Columns
30.3. check_constraints Columns
30.4. column_domain_usage Columns
30.5. column_privileges Columns
30.6. column_udt_usage Columns
30.7. columns Columns
30.8. constraint_column_usage Columns
30.9. constraint_table_usage Columns
30.10. data_type_privileges Columns
30.11. domain_constraints Columns
30.12. domain_udt_usage Columns
30.13. domains Columns
30.14. element_types Columns
30.15. enabled_roles Columns
30.16. key_column_usage Columns
30.17. parameters Columns
30.18. referential_constraints Columns
30.19. role_column_grants Columns
30.20. role_routine_grants Columns
30.21. role_table_grants Columns
30.22. role_usage_grants Columns
30.23. routine_privileges Columns
30.24. routines Columns
30.25. schemata Columns
30.26. sql_features Columns
30.27. sql_implementation_info Columns
30.28. sql_languages Columns
30.29. sql_packages Columns
30.30. sql_sizing Columns
30.31. sql_sizing_profiles Columns
30.32. table_constraints Columns
30.33. table_privileges Columns
30.34. tables Columns
30.35. triggers Columns
30.36. usage_privileges Columns
30.37. view_column_usage Columns
30.38. view_table_usage Columns
30.39. views Columns
31.1. Equivalent C Types for Built-In SQL Types
31.2. B-tree Strategies
31.3. Hash Strategies
31.4. R-tree Strategies
31.5. B-tree Support Functions
31.6. Hash Support Functions
31.7. R-tree Support Functions
31.8. GiST Support Functions
41.1. System Catalogs
41.2. pg_aggregate Columns
41.3. pg_am Columns
41.4. pg_amop Columns
41.5. pg_amproc Columns
41.6. pg_attrdef Columns
41.7. pg_attribute Columns
41.8. pg_cast Columns
41.9. pg_class Columns
41.10. pg_constraint Columns
41.11. pg_conversion Columns
41.12. pg_database Columns
41.13. pg_depend Columns
41.14. pg_description Columns
41.15. pg_group Columns
41.16. pg_index Columns
41.17. pg_inherits Columns
41.18. pg_language Columns
41.19. pg_largeobject Columns
41.20. pg_listener Columns
41.21. pg_namespace Columns
41.22. pg_opclass Columns
41.23. pg_operator Columns
41.24. pg_proc Columns
41.25. pg_rewrite Columns
41.26. pg_shadow Columns
41.27. pg_statistic Columns
41.28. pg_tablespace Columns
41.29. pg_trigger Columns
41.30. pg_type Columns
41.31. System Views
41.32. pg_indexes Columns
41.33. pg_locks Columns
41.34. pg_rules Columns
41.35. pg_settings Columns
41.36. pg_stats Columns
41.37. pg_tables Columns
41.38. pg_user Columns
41.39. pg_views Columns
49.1. Contents of PGDATA
50.1. Overall Page Layout
50.2. PageHeaderData Layout
50.3. HeapTupleHeaderData Layout
A.1. PostgreSQL Error Codes
B.1. Month Abbreviations
B.2. Day of the Week Abbreviations
B.3. Date/Time Field Modifiers
B.4. Time Zone Abbreviations for Input
B.5. Australian Time Zone Abbreviations for Input
B.6. Time Zone Names for Setting timezone
C.1. SQL Key Words

List of Examples

8.1. Using the character types
8.2. Using the boolean type
8.3. Using the bit string types
10.1. Exponentiation Operator Type Resolution
10.2. String Concatenation Operator Type Resolution
10.3. Absolute-Value and Negation Operator Type Resolution
10.4. Rounding Function Argument Type Resolution
10.5. Substring Function Type Resolution
10.6. character Storage Type Conversion
10.7. Type Resolution with Underspecified Types in a Union
10.8. Type Resolution in a Simple Union
10.9. Type Resolution in a Transposed Union
11.1. Setting up a Partial Index to Exclude Common Values
11.2. Setting up a Partial Index to Exclude Uninteresting Values
11.3. Setting up a Partial Unique Index
19.1. An example pg_hba.conf file
19.2. An example pg_ident.conf file
27.1. libpq Example Program 1
27.2. libpq Example Program 2
27.3. libpq Example Program 3
28.1. Large Objects with libpq Example Program
34.1. Manual Installation of PL/pgSQL
35.1. A PL/pgSQL Trigger Procedure
35.2. Porting a Simple Function from PL/SQL to PL/pgSQL
35.3. Porting a Function that Creates Another Function from PL/SQL to PL/pgSQL
35.4. Porting a Procedure With String Manipulation and OUT Parameters from PL/SQL to PL/pgSQL
35.5. Porting a Procedure from PL/SQL to PL/pgSQL