PostgreSQL 7.3.5 User's Guide

The PostgreSQL Global Development Group

Copyright © 1996-2002 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. What's In This Book
4. Overview of Documentation Resources
5. Terminology and Notation
6. Bug Reporting Guidelines
6.1. Identifying Bugs
6.2. What to report
6.3. Where to report bugs
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.1.6. Lexical Precedence
1.2. Value Expressions
1.2.1. Column References
1.2.2. Positional Parameters
1.2.3. Operator Invocations
1.2.4. Function Calls
1.2.5. Aggregate Expressions
1.2.6. Type Casts
1.2.7. Scalar Subqueries
1.2.8. Expression Evaluation
2. Data Definition
2.1. Table Basics
2.2. System Columns
2.3. Default Values
2.4. Constraints
2.4.1. Check Constraints
2.4.2. Not-Null Constraints
2.4.3. Unique Constraints
2.4.4. Primary Keys
2.4.5. Foreign Keys
2.5. Inheritance
2.6. Modifying Tables
2.6.1. Adding a Column
2.6.2. Removing a Column
2.6.3. Adding a Constraint
2.6.4. Removing a Constraint
2.6.5. Changing the Default
2.6.6. Renaming a Column
2.6.7. Renaming a Table
2.7. Privileges
2.8. Schemas
2.8.1. Creating a Schema
2.8.2. The Public Schema
2.8.3. The Schema Search Path
2.8.4. Schemas and Privileges
2.8.5. The System Catalog Schema
2.8.6. Usage Patterns
2.8.7. Portability
2.9. Other Database Objects
2.10. Dependency Tracking
3. Data Manipulation
3.1. Inserting Data
3.2. Updating Data
3.3. Deleting Data
4. Queries
4.1. Overview
4.2. Table Expressions
4.2.1. The FROM Clause
4.2.2. The WHERE Clause
4.2.3. The GROUP BY and HAVING Clauses
4.3. Select Lists
4.3.1. Select-List Items
4.3.2. Column Labels
4.3.3. DISTINCT
4.4. Combining Queries
4.5. Sorting Rows
4.6. LIMIT and OFFSET
5. Data Types
5.1. Numeric Types
5.1.1. The Integer Types
5.1.2. Arbitrary Precision Numbers
5.1.3. Floating-Point Types
5.1.4. The Serial Types
5.2. Monetary Type
5.3. Character Types
5.4. Binary Strings
5.5. Date/Time Types
5.5.1. Date/Time Input
5.5.2. Date/Time Output
5.5.3. Time Zones
5.5.4. Internals
5.6. Boolean Type
5.7. Geometric Types
5.7.1. Point
5.7.2. Line Segment
5.7.3. Box
5.7.4. Path
5.7.5. Polygon
5.7.6. Circle
5.8. Network Address Data Types
5.8.1. inet
5.8.2. cidr
5.8.3. inet vs cidr
5.8.4. macaddr
5.9. Bit String Types
5.10. Object Identifier Types
5.11. Pseudo-Types
5.12. Arrays
6. Functions and Operators
6.1. Logical Operators
6.2. Comparison Operators
6.3. Mathematical Functions and Operators
6.4. String Functions and Operators
6.5. Binary String Functions and Operators
6.6. Pattern Matching
6.6.1. LIKE
6.6.2. SIMILAR TO and SQL99 Regular Expressions
6.6.3. POSIX Regular Expressions
6.7. Data Type Formatting Functions
6.8. Date/Time Functions and Operators
6.8.1. EXTRACT, date_part
6.8.2. date_trunc
6.8.3. AT TIME ZONE
6.8.4. Current Date/Time
6.9. Geometric Functions and Operators
6.10. Network Address Type Functions
6.11. Sequence-Manipulation Functions
6.12. Conditional Expressions
6.12.1. CASE
6.12.2. COALESCE
6.12.3. NULLIF
6.13. Miscellaneous Functions
6.14. Aggregate Functions
6.15. Subquery Expressions
6.15.1. EXISTS
6.15.2. IN (scalar form)
6.15.3. IN (subquery form)
6.15.4. NOT IN (scalar form)
6.15.5. NOT IN (subquery form)
6.15.6. ANY/SOME
6.15.7. ALL
6.15.8. Row-wise Comparison
7. Type Conversion
7.1. Overview
7.2. Operators
7.3. Functions
7.4. Query Targets
7.5. UNION and CASE Constructs
8. Indexes
8.1. Introduction
8.2. Index Types
8.3. Multicolumn Indexes
8.4. Unique Indexes
8.5. Functional Indexes
8.6. Operator Classes
8.7. Partial Indexes
8.8. Examining Index Usage
9. Concurrency Control
9.1. Introduction
9.2. Transaction Isolation
9.2.1. Read Committed Isolation Level
9.2.2. Serializable Isolation Level
9.3. Explicit Locking
9.3.1. Table-Level Locks
9.3.2. Row-Level Locks
9.3.3. Deadlocks
9.4. Data Consistency Checks at the Application Level
9.5. Locking and Indexes
10. Performance Tips
10.1. Using EXPLAIN
10.2. Statistics Used by the Planner
10.3. Controlling the Planner with Explicit JOIN Clauses
10.4. Populating a Database
10.4.1. Disable Autocommit
10.4.2. Use COPY FROM
10.4.3. Remove Indexes
10.4.4. Run ANALYZE Afterwards
A. Date/Time Support
A.1. Date/Time Input Interpretation
A.2. Date/Time Key Words
A.3. History of Units
B. SQL Key Words
C. SQL Conformance
C.1. Supported Features
C.2. Unsupported Features
Bibliography
List of Tables
1-1. Operator Precedence (decreasing)
5-1. Data Types
5-2. Numeric Types
5-3. Monetary Types
5-4. Character Types
5-5. Specialty Character Types
5-6. Binary String Types
5-7. bytea Literal Escaped Octets
5-8. bytea Output Escaped Octets
5-9. Date/Time Types
5-10. Date Input
5-11. Time Input
5-12. Time With Time Zone Input
5-13. Time Zone Input
5-14. Special Date/Time Inputs
5-15. Date/Time Output Styles
5-16. Date Order Conventions
5-17. Geometric Types
5-18. Network Address Data Types
5-19. cidr Type Input Examples
5-20. Object Identifier Types
5-21. Pseudo-Types
6-1. Comparison Operators
6-2. Mathematical Operators
6-3. Bit String Binary Operators
6-4. Mathematical Functions
6-5. Trigonometric Functions
6-6. SQL String Functions and Operators
6-7. Other String Functions
6-8. Built-in Conversions
6-9. SQL Binary String Functions and Operators
6-10. Other Binary String Functions
6-11. Regular Expression Match Operators
6-12. Formatting Functions
6-13. Template patterns for date/time conversions
6-14. Template pattern modifiers for date/time conversions
6-15. Template patterns for numeric conversions
6-16. to_char Examples
6-17. Date/Time Operators
6-18. Date/Time Functions
6-19. AT TIME ZONE Variants
6-20. Geometric Operators
6-21. Geometric Functions
6-22. Geometric Type Conversion Functions
6-23. cidr and inet Operators
6-24. cidr and inet Functions
6-25. macaddr Functions
6-26. Sequence Functions
6-27. Session Information Functions
6-28. Configuration Settings Information Functions
6-29. Access Privilege Inquiry Functions
6-30. Schema Visibility Inquiry Functions
6-31. Catalog Information Functions
6-32. Comment Information Functions
6-33. Aggregate Functions
9-1. SQL Transaction Isolation Levels
10-1. pg_stats Columns
A-1. Month Abbreviations
A-2. Day of the Week Abbreviations
A-3. Date/Time Field Modifiers
A-4. Time Zone Abbreviations
A-5. Australian Time Zone Abbreviations
B-1. SQL Key Words
List of Examples
5-1. Using the character types
5-2. Using the boolean type
5-3. Using the bit string types
7-1. Exponentiation Operator Type Resolution
7-2. String Concatenation Operator Type Resolution
7-3. Absolute-Value and Factorial Operator Type Resolution
7-4. Factorial Function Argument Type Resolution
7-5. Substring Function Type Resolution
7-6. character Storage Type Conversion
7-7. Underspecified Types in a Union
7-8. Type Conversion in a Simple Union
7-9. Type Conversion in a Transposed Union
8-1. Setting up a Partial Index to Exclude Common Values
8-2. Setting up a Partial Index to Exclude Uninteresting Values
8-3. Setting up a Partial Unique Index
PrevHomeNext
Conclusion Preface