PostgreSQL 7.2.1 User's Guide

The PostgreSQL Global Development Group

Copyright © 1996-2001 by The PostgreSQL Global Development Group

Table of Contents
Preface
1. What is PostgreSQL?
2. A Short History of PostgreSQL
2.1. The Berkeley POSTGRES Project
2.2. Postgres95
2.3. PostgreSQL
3. Documentation Resources
4. Terminology and Notation
5. Bug Reporting Guidelines
5.1. Identifying Bugs
5.2. What to report
5.3. Where to report bugs
6. Y2K Statement
1. SQL Syntax
1.1. Lexical Structure
1.1.1. Identifiers and Key Words
1.1.2. Constants
1.1.3. Operators
1.1.4. Special Characters
1.1.5. Comments
1.2. Columns
1.3. Value Expressions
1.3.1. Column References
1.3.2. Positional Parameters
1.3.3. Operator Invocations
1.3.4. Function Calls
1.3.5. Aggregate Expressions
1.3.6. Type Casts
1.3.7. Scalar Subqueries
1.4. Lexical Precedence
2. Queries
2.1. Overview
2.2. Table Expressions
2.2.1. FROM clause
2.2.2. WHERE clause
2.2.3. GROUP BY and HAVING clauses
2.3. Select Lists
2.3.1. Column Labels
2.3.2. DISTINCT
2.4. Combining Queries
2.5. Sorting Rows
2.6. LIMIT and OFFSET
3. Data Types
3.1. Numeric Types
3.1.1. The Integer Types
3.1.2. Arbitrary Precision Numbers
3.1.3. Floating-Point Types
3.1.4. The Serial Types
3.2. Monetary Type
3.3. Character Types
3.4. Binary Strings
3.5. Date/Time Types
3.5.1. Date/Time Input
3.5.2. Date/Time Output
3.5.3. Time Zones
3.5.4. Internals
3.6. Boolean Type
3.7. Geometric Types
3.7.1. Point
3.7.2. Line Segment
3.7.3. Box
3.7.4. Path
3.7.5. Polygon
3.7.6. Circle
3.8. Network Address Data Types
3.8.1. inet
3.8.2. cidr
3.8.3. inet vs cidr
3.8.4. macaddr
3.9. Bit String Types
4. Functions and Operators
4.1. Logical Operators
4.2. Comparison Operators
4.3. Mathematical Functions and Operators
4.4. String Functions and Operators
4.5. Binary String Functions and Operators
4.6. Pattern Matching
4.6.1. Pattern Matching with LIKE
4.6.2. POSIX Regular Expressions
4.7. Data Type Formatting Functions
4.8. Date/Time Functions and Operators
4.8.1. EXTRACT, date_part
4.8.2. date_trunc
4.8.3. Current Date/Time
4.9. Geometric Functions and Operators
4.10. Network Address Type Functions
4.11. Sequence-Manipulation Functions
4.12. Conditional Expressions
4.13. Miscellaneous Functions
4.14. Aggregate Functions
4.15. Subquery Expressions
5. Type Conversion
5.1. Introduction
5.2. Overview
5.3. Operators
5.4. Functions
5.5. Query Targets
5.6. UNION and CASE Constructs
6. Arrays
7. Indexes
7.1. Introduction
7.2. Index Types
7.3. Multicolumn Indexes
7.4. Unique Indexes
7.5. Functional Indexes
7.6. Operator Classes
7.7. Keys
7.8. Partial Indexes
7.9. Examining Index Usage
8. Inheritance
9. Multiversion Concurrency Control
9.1. Introduction
9.2. Transaction Isolation
9.3. Read Committed Isolation Level
9.4. Serializable Isolation Level
9.5. Data consistency checks at the application level
9.6. Locking and Tables
9.6.1. Table-level locks
9.6.2. Row-level locks
9.7. Locking and Indexes
10. Managing a Database
10.1. Database Creation
10.2. Accessing a Database
10.3. Destroying a Database
11. Performance Tips
11.1. Using EXPLAIN
11.2. Statistics used by the Planner
11.3. Controlling the Planner with Explicit JOINs
11.4. Populating a Database
11.4.1. Disable Autocommit
11.4.2. Use COPY FROM
11.4.3. Remove Indexes
11.4.4. ANALYZE Afterwards
A. Date/Time Support
A.1. Date/Time Keywords
A.2. Time Zones
A.2.1. Australian Time Zones
A.2.2. Date/Time Input Interpretation
A.3. History of Units
B. SQL Key Words
Bibliography
List of Tables
1-1. Operator Precedence (decreasing)
3-1. Data Types
3-2. Numeric Types
3-3. Monetary Types
3-4. Character Types
3-5. Specialty Character Type
3-6. Binary String Types
3-7. SQL Literal Escaped Octets
3-8. SQL Output Escaped Octets
3-9. Comparison of SQL99 Binary String and PostgreSQL BYTEA types
3-10. Date/Time Types
3-11. Date Input
3-12. Time Input
3-13. Time With Time Zone Input
3-14. Time Zone Input
3-15. Special Date/Time Constants
3-16. Date/Time Output Styles
3-17. Date-Order Conventions
3-18. Geometric Types
3-19. Network Address Data Types
3-20. cidr Type Input Examples
4-1. Comparison Operators
4-2. Mathematical Operators
4-3. Bit String Binary Operators
4-4. Mathematical Functions
4-5. Trigonometric Functions
4-6. SQL String Functions and Operators
4-7. Other String Functions
4-8. SQL Binary String Functions and Operators
4-9. Other Binary String Functions
4-10. Regular Expression Match Operators
4-11. Formatting Functions
4-12. Template patterns for date/time conversions
4-13. Template pattern modifiers for date/time conversions
4-14. Template patterns for numeric conversions
4-15. to_char Examples
4-16. Date/Time Operators
4-17. Date/Time Functions
4-18. Geometric Operators
4-19. Geometric Functions
4-20. Geometric Type Conversion Functions
4-21. cidr and inet Operators
4-22. cidr and inet Functions
4-23. macaddr Functions
4-24. Sequence Functions
4-25. Session Information Functions
4-26. System Information Functions
4-27. Access Privilege Inquiry Functions
4-28. Catalog Information Functions
4-29. Comment Information Functions
4-30. Aggregate Functions
9-1. SQL Transaction Isolation Levels
11-1. pg_stats Columns
A-1. Month Abbreviations
A-2. Day of the Week Abbreviations
A-3. PostgreSQL Field Modifiers
A-4. PostgreSQL Recognized Time Zones
A-5. PostgreSQL Australian Time Zones
B-1. SQL Key Words
List of Examples
3-1. Using the character types
3-2. Using the boolean type
3-3. Using the bit string types
5-1. Exponentiation Operator Type Resolution
5-2. String Concatenation Operator Type Resolution
5-3. Absolute-Value and Factorial Operator Type Resolution
5-4. Factorial Function Argument Type Resolution
5-5. Substring Function Type Resolution
5-6. character Storage Type Conversion
5-7. Underspecified Types in a Union
5-8. Type Conversion in a Simple Union
5-9. Type Conversion in a Transposed Union
7-1. Setting up a Partial Index to Exclude Common Values
7-2. Setting up a Partial Index to Exclude Uninteresting Values
7-3. Setting up a Partial Unique Index
PrevHomeNext
Conclusion Preface