|
PostgreSQL 8.1.3 Documentation
The PostgreSQL Global Development Group
Copyright © 1996-2005 The PostgreSQL Global Development Group
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. Default Values
5.3. Constraints
5.3.1. Check Constraints
5.3.2. Not-Null Constraints
5.3.3. Unique Constraints
5.3.4. Primary Keys
5.3.5. Foreign Keys
5.4. System Columns
5.5. Modifying Tables
5.5.1. Adding a Column
5.5.2. Removing a Column
5.5.3. Adding a Constraint
5.5.4. Removing a Constraint
5.5.5. Changing a Column's Default Value
5.5.6. Changing a Column's Data Type
5.5.7. Renaming a Column
5.5.8. Renaming a Table
5.6. Privileges
5.7. Schemas
5.7.1. Creating a Schema
5.7.2. The Public Schema
5.7.3. The Schema Search Path
5.7.4. Schemas and Privileges
5.7.5. The System Catalog Schema
5.7.6. Usage Patterns
5.7.7. Portability
5.8. Inheritance
5.8.1. Caveats
5.9. Partitioning
5.9.1. Overview
5.9.2. Implementing Partitioning
5.9.3. Partitioning and Constraint Exclusion
5.10. Other Database Objects
5.11. 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 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 Functions and Operators
9.12. Sequence Manipulation Functions
9.13. Conditional Expressions
9.13.1. CASE
9.13.2. COALESCE
9.13.3. NULLIF
9.13.4. GREATEST and LEAST
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 Related Constructs
11. Indexes
11.1. Introduction
11.2. Index Types
11.3. Multicolumn Indexes
11.4. Combining Multiple Indexes
11.5. Unique Indexes
11.6. Indexes on Expressions
11.7. Partial Indexes
11.8. Operator Classes
11.9. 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. Remove Foreign Key Constraints
13.4.5. Increase maintenance_work_mem
13.4.6. Increase checkpoint_segments
13.4.7. Run ANALYZE Afterwards
13.4.8. Some Notes About pg_dump
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. Client-Only Installation on Windows
16. Operating System 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. Managing Kernel Resources
16.4.1. Shared Memory and Semaphores
16.4.2. Resource Limits
16.4.3. Linux Memory Overcommit
16.5. Shutting Down the Server
16.6. Encryption Options
16.7. Secure TCP/IP Connections with SSL
16.8. Secure TCP/IP Connections with SSH Tunnels
17. Server Configuration
17.1. Setting Parameters
17.2. File Locations
17.3. Connections and Authentication
17.3.1. Connection Settings
17.3.2. Security and Authentication
17.4. Resource Consumption
17.4.1. Memory
17.4.2. Free Space Map
17.4.3. Kernel Resource Usage
17.4.4. Cost-Based Vacuum Delay
17.4.5. Background Writer
17.5. Write Ahead Log
17.5.1. Settings
17.5.2. Checkpoints
17.5.3. Archiving
17.6. Query Planning
17.6.1. Planner Method Configuration
17.6.2. Planner Cost Constants
17.6.3. Genetic Query Optimizer
17.6.4. Other Planner Options
17.7. Error Reporting and Logging
17.7.1. Where To Log
17.7.2. When To Log
17.7.3. What To Log
17.8. Run-Time Statistics
17.8.1. Statistics Monitoring
17.8.2. Query and Index Statistics Collector
17.9. Automatic Vacuuming
17.10. Client Connection Defaults
17.10.1. Statement Behavior
17.10.2. Locale and Formatting
17.10.3. Other Defaults
17.11. Lock Management
17.12. Version and Platform Compatibility
17.12.1. Previous PostgreSQL Versions
17.12.2. Platform and Client Compatibility
17.13. Preset Options
17.14. Customized Options
17.15. Developer Options
17.16. Short Options
18. Database Roles and Privileges
18.1. Database Roles
18.2. Role Attributes
18.3. Privileges
18.4. Role Membership
18.5. Functions and Triggers
19. Managing Databases
19.1. Overview
19.2. Creating a Database
19.3. Template Databases
19.4. Database Configuration
19.5. Destroying a Database
19.6. Tablespaces
20. Client Authentication
20.1. The pg_hba.conf file
20.2. Authentication methods
20.2.1. Trust authentication
20.2.2. Password authentication
20.2.3. Kerberos authentication
20.2.4. Ident-based authentication
20.2.5. PAM authentication
20.3. Authentication problems
21. Localization
21.1. Locale Support
21.1.1. Overview
21.1.2. Behavior
21.1.3. Problems
21.2. Character Set Support
21.2.1. Supported Character Sets
21.2.2. Setting the Character Set
21.2.3. Automatic Character Set Conversion Between Server and Client
21.2.4. Further Reading
22. Routine Database Maintenance Tasks
22.1. Routine Vacuuming
22.1.1. Recovering disk space
22.1.2. Updating planner statistics
22.1.3. Preventing transaction ID wraparound failures
22.1.4. The auto-vacuum daemon
22.2. Routine Reindexing
22.3. Log File Maintenance
23. Backup and Restore
23.1. SQL Dump
23.1.1. Restoring the dump
23.1.2. Using pg_dumpall
23.1.3. Handling large databases
23.2. File system level backup
23.3. On-line backup and point-in-time recovery (PITR)
23.3.1. Setting up WAL archiving
23.3.2. Making a Base Backup
23.3.3. Recovering with an On-line Backup
23.3.4. Timelines
23.3.5. Caveats
23.4. Migration Between Releases
24. Monitoring Database Activity
24.1. Standard Unix Tools
24.2. The Statistics Collector
24.2.1. Statistics Collection Configuration
24.2.2. Viewing Collected Statistics
24.3. Viewing Locks
25. Monitoring Disk Usage
25.1. Determining Disk Usage
25.2. Disk Full Failure
26. Reliability and the Write-Ahead Log
26.1. Reliability
26.2. Write-Ahead Logging (WAL)
26.3. WAL Configuration
26.4. WAL Internals
27. Regression Tests
27.1. Running the Tests
27.2. Test Evaluation
27.2.1. Error message differences
27.2.2. Locale differences
27.2.3. Date and time differences
27.2.4. Floating-point differences
27.2.5. Row ordering differences
27.2.6. Insufficient stack depth
27.2.7. The “random” test
27.3. Variant Comparison Files
IV. Client Interfaces
28. libpq - C Library
28.1. Database Connection Control Functions
28.2. Connection Status Functions
28.3. Command Execution Functions
28.3.1. Main Functions
28.3.2. Retrieving Query Result Information
28.3.3. Retrieving Result Information for Other Commands
28.3.4. Escaping Strings for Inclusion in SQL Commands
28.3.5. Escaping Binary Strings for Inclusion in SQL Commands
28.4. Asynchronous Command Processing
28.5. Cancelling Queries in Progress
28.6. The Fast-Path Interface
28.7. Asynchronous Notification
28.8. Functions Associated with the COPY Command
28.8.1. Functions for Sending COPY Data
28.8.2. Functions for Receiving COPY Data
28.8.3. Obsolete Functions for COPY
28.9. Control Functions
28.10. Notice Processing
28.11. Environment Variables
28.12. The Password File
28.13. SSL Support
28.14. Behavior in Threaded Programs
28.15. Building libpq Programs
28.16. Example Programs
29. Large Objects
29.1. History
29.2. Implementation Features
29.3. Client Interfaces
29.3.1. Creating a Large Object
29.3.2. Importing a Large Object
29.3.3. Exporting a Large Object
29.3.4. Opening an Existing Large Object
29.3.5. Writing Data to a Large Object
29.3.6. Reading Data from a Large Object
29.3.7. Seeking in a Large Object
29.3.8. Obtaining the Seek Position of a Large Object
29.3.9. Closing a Large Object Descriptor
29.3.10. Removing a Large Object
29.4. Server-Side Functions
29.5. Example Program
30. ECPG - Embedded SQL in C
30.1. The Concept
30.2. Connecting to the Database Server
30.3. Closing a Connection
30.4. Running SQL Commands
30.5. Choosing a Connection
30.6. Using Host Variables
30.6.1. Overview
30.6.2. Declare Sections
30.6.3. SELECT INTO and FETCH INTO
30.6.4. Indicators
30.7. Dynamic SQL
30.8. Using SQL Descriptor Areas
30.9. Error Handling
30.9.1. Setting Callbacks
30.9.2. sqlca
30.9.3. SQLSTATE vs SQLCODE
30.10. Including Files
30.11. Processing Embedded SQL Programs
30.12. Library Functions
30.13. Internals
31. The Information Schema
31.1. The Schema
31.2. Data Types
31.3. information_schema_catalog_name
31.4. applicable_roles
31.5. check_constraints
31.6. column_domain_usage
31.7. column_privileges
31.8. column_udt_usage
31.9. columns
31.10. constraint_column_usage
31.11. constraint_table_usage
31.12. data_type_privileges
31.13. domain_constraints
31.14. domain_udt_usage
31.15. domains
31.16. element_types
31.17. enabled_roles
31.18. key_column_usage
31.19. parameters
31.20. referential_constraints
31.21. role_column_grants
31.22. role_routine_grants
31.23. role_table_grants
31.24. role_usage_grants
31.25. routine_privileges
31.26. routines
31.27. schemata
31.28. sql_features
31.29. sql_implementation_info
31.30. sql_languages
31.31. sql_packages
31.32. sql_sizing
31.33. sql_sizing_profiles
31.34. table_constraints
31.35. table_privileges
31.36. tables
31.37. triggers
31.38. usage_privileges
31.39. view_column_usage
31.40. view_table_usage
31.41. views
V. Server Programming
32. Extending SQL
32.1. How Extensibility Works
32.2. The PostgreSQL Type System
32.2.1. Base Types
32.2.2. Composite Types
32.2.3. Domains
32.2.4. Pseudo-Types
32.2.5. Polymorphic Types
32.3. User-Defined Functions
32.4. Query Language (SQL) Functions
32.4.1. SQL Functions on Base Types
32.4.2. SQL Functions on Composite Types
32.4.3. Functions with Output Parameters
|