PostgreSQL 7.1.3 User's Guide

The PostgreSQL Global Development Group

Table of Contents

Preface

1. What is PostgreSQL?

2. A Short History of Postgres

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. Function Calls
1.3.4. Aggregate Expressions

1.4. Lexical Precedence

2. Queries

2.1. Table Expressions

2.1.1. FROM clause
2.1.2. WHERE clause
2.1.3. GROUP BY and HAVING clauses

2.2. Select Lists

2.2.1. Column Labels
2.2.2. DISTINCT

2.3. Combining Queries

2.4. Sorting Rows

2.5. LIMIT and OFFSET

3. Data Types

3.1. Numeric Types

3.1.1. The Serial Type

3.2. Monetary Type

3.3. Character Types

3.4. Date/Time Types

3.4.1. Date/Time Input
3.4.2. Date/Time Output
3.4.3. Time Zones
3.4.4. Internals

3.5. Boolean Type

3.6. Geometric Types

3.6.1. Point
3.6.2. Line Segment
3.6.3. Box
3.6.4. Path
3.6.5. Polygon
3.6.6. Circle

3.7. Network Address Data Types

3.7.1. inet
3.7.2. cidr
3.7.3. inet vs cidr
3.7.4. macaddr

3.8. 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. Pattern Matching

4.5.1. Pattern Matching with LIKE
4.5.2. POSIX Regular Expressions

4.6. Formatting Functions

4.7. Date/Time Functions

4.7.1. EXTRACT, date_part
4.7.2. date_trunc
4.7.3. Current Date/Time

4.8. Geometric Functions and Operators

4.9. Network Address Type Functions

4.10. Conditional Expressions

4.11. Miscellaneous Functions

4.12. Aggregate Functions

5. Type Conversion

5.1. Overview

5.1.1. Guidelines

5.2. Operators

5.2.1. Examples

5.3. Functions

5.3.1. Examples

5.4. Query Targets

5.4.1. Examples

5.5. UNION and CASE Constructs

5.5.1. Examples

6. Arrays

7. Indices

7.1. Introduction
7.2. Index Types
7.3. Multi-Column Indices
7.4. Unique Indices
7.5. Functional Indices
7.6. Operator Classes
7.7. Keys
7.8. Partial Indices

8. Inheritance

9. Multi-Version 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 Indices

10. Managing a Database

10.1. Database Creation
10.2. Alternate Database Locations
10.3. Accessing a Database
10.4. Destroying a Database

11. Performance Tips

11.1. Using EXPLAIN

11.2. Controlling the Planner with Explicit JOINs

11.3. Populating a Database

11.3.1. Disable Auto-commit
11.3.2. Use COPY FROM
11.3.3. Remove Indices

A. Date/Time Support

A.1. Time Zones

A.1.1. Australian Time Zones
A.1.2. Date/Time Input Interpretation

A.2. 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. Date/Time Types
3-7. Date Input
3-8. Month Abbreviations
3-9. Day of the Week Abbreviations
3-10. Time Input
3-11. Time With Time Zone Input
3-12. Time Zone Input
3-13. Special Date/Time Constants
3-14. Date/Time Output Styles
3-15. Date Order Conventions
3-16. Geometric Types
3-17. Network Address Data Types
3-18. 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. Regular Expression Match Operators
4-9. Formatting Functions
4-10. Template patterns for date/time conversions
4-11. Template pattern modifiers for date/time conversions
4-12. Template patterns for numeric conversions
4-13. to_char Examples
4-14. Date/Time Functions
4-15. Geometric Operators
4-16. Geometric Functions
4-17. Geometric Type Conversion Functions
4-18. cidr and inet Operators
4-19. cidr and inet Functions
4-20. macaddr Functions
4-21. Miscellaneous Functions
4-22. Aggregate Functions
9-1. ANSI/ISO SQL Isolation Levels
A-1. Postgres Recognized Time Zones
A-2. Postgres Australian Time Zones
B-1. SQL Key Words

List of Examples
3-1. Using the boolean type

Prev	Home	Next
More Advanced Features		Preface