PostgreSQL 8.1.3 Documentation

Top  Previous  Next

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

<span style="font-size: 12pt; font-fa