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. Managing Partitions

5.9.4. Partitioning and Constraint Exclusion

5.9.5. Caveats

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

7.7. VALUES Lists

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

8.14. XML Document Support

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.9.5. Delaying Execution

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.3.4. Advisory Locks

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. Query and Index Statistics Collector

17.8.2. Statistics Monitoring

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. LDAP authentication

20.2.6. 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. Continuous Archiving and Point-In-Time Recovery (PITR)

23.3.1. Setting up WAL archiving

23.3.2. Making a Base Backup

23.3.3. Recovering using a Continuous Archive Backup

23.3.4. Timelines

23.3.5. Caveats

23.4. Warm Standby Servers for High Availability

23.4.1. Planning

23.4.2. Implementation

23.4.3. Failover

23.4.4. Record-based Log Shipping

23.4.5. Incrementally Updated Backups

23.5. Migration Between Releases

24. High Availability and Load Balancing

25. Monitoring Database Activity

25.1. Standard Unix Tools

25.2. The Statistics Collector

25.2.1. Statistics Collection Configuration

25.2.2. Viewing Collected Statistics

25.3. Viewing Locks

25.4. Dynamic Tracing

25.4.1. Compiling for Dynamic Tracing

25.4.2. Built-in Trace Points

25.4.3. Using Trace Points

25.4.4. Defining Trace Points

26. Monitoring Disk Usage

26.1. Determining Disk Usage

26.2. Disk Full Failure

27. Reliability and the Write-Ahead Log

27.1. Reliability

27.2. Write-Ahead Logging (WAL)

27.3. WAL Configuration

27.4. WAL Internals

28. Regression Tests

28.1. Running the Tests

28.2. Test Evaluation

28.2.1. Error message differences

28.2.2. Locale differences

28.2.3. Date and time differences

28.2.4. Floating-point differences

28.2.5. Row ordering differences

28.2.6. Insufficient stack depth

28.2.7. The random test

28.3. Variant Comparison Files

IV. Client Interfaces

29. libpq - C Library

29.1. Database Connection Control Functions

29.2. Connection Status Functions

29.3. Command Execution Functions

29.3.1. Main Functions

29.3.2. Retrieving Query Result Information

29.3.3. Retrieving Result Information for Other Commands

29.3.4. Escaping Strings for Inclusion in SQL Commands

29.3.5. Escaping Binary Strings for Inclusion in SQL Commands

29.4. Asynchronous Command Processing

29.5. Cancelling Queries in Progress

29.6. The Fast-Path Interface

29.7. Asynchronous Notification

29.8. Functions Associated with the COPY Command

29.8.1. Functions for Sending COPY Data

29.8.2. Functions for Receiving COPY Data

29.8.3. Obsolete Functions for COPY

29.9. Control Functions

29.10. Miscellaneous Functions

29.11. Notice Processing

29.12. Environment Variables

29.13. The Password File

29.14. The Connection Service File

29.15. LDAP Lookup of Connection Parameters

29.16. SSL Support

29.17. Behavior in Threaded Programs

29.18. Building libpq Programs

29.19. Example Programs

30. Large Objects

30.1. Introduction

30.2. Implementation Features

30.3. Client Interfaces

30.3.1. Creating a Large Object

30.3.2. Importing a Large Object

30.3.3. Exporting a Large Object

30.3.4. Opening an Existing Large Object

30.3.5. Writing Data to a Large Object

30.3.6. Reading Data from a Large Object

30.3.7. Seeking in a Large Object

30.3.8. Obtaining the Seek Position of a Large Object

30.3.9. Closing a Large Object Descriptor

30.3.10. Removing a Large Object

30.4. Server-Side Functions

30.5. Example Program

31. ECPG - Embedded SQL in C

31.1. The Concept

31.2. Connecting to the Database Server

31.3. Closing a Connection

31.4. Running SQL Commands

31.5. Choosing a Connection

31.6. Using Host Variables

31.6.1. Overview

31.6.2. Declare Sections

31.6.3. Different types of host variables

31.6.4. SELECT INTO and FETCH INTO

31.6.5. Indicators

31.7. Dynamic SQL

31.8. pgtypes library

31.8.1. The numeric type

31.8.2. The date type

31.8.3. The timestamp type

31.8.4. The interval type

31.8.5. The decimal type

31.8.6. errno values of pgtypeslib

31.8.7. Special constants of pgtypeslib

31.9. Informix compatibility mode

31.9.1. Additional embedded SQL statements

31.9.2. Additional functions

31.9.3. Additional constants

31.10. Using SQL Descriptor Areas

31.11. Error Handling

31.11.1. Setting Callbacks

31.11.2. sqlca

31.11.3. SQLSTATE vs SQLCODE

31.12. Preprocessor directives

31.12.1. Including files

31.12.2. The #define and #undef directives

31.12.3. ifdef, ifndef, else, elif and endif directives

31.13. Processing Embedded SQL Programs

31.14. Library Functions

31.15. Internals

32. The Information Schema

32.1. The Schema

32.2. Data Types

32.3. information_schema_catalog_name

32.4. administrable_role_authorizations

32.5. applicable_roles

32.6. attributes

32.7. check_constraint_routine_usage

32.8. check_constraints

32.9. column_domain_usage

32.10. column_privileges

32.11. column_udt_usage

32.12. columns

32.13. constraint_column_usage

32.14. constraint_table_usage

32.15. data_type_privileges

32.16. domain_constraints

32.17. domain_udt_usage

32.18. domains

32.19. element_types

32.20. enabled_roles

32.21. key_column_usage

32.22. parameters

32.23. referential_constraints

32.24. role_column_grants

32.25. role_routine_grants

32.26. role_table_grants

32.27. role_usage_grants

32.28. routine_privileges

32.29. routines

32.30. schemata

32.31. sequences

32.32. sql_features

32.33. sql_implementation_info

32.34. sql_languages

32.35. sql_packages

32.36. sql_parts

32.37. sql_sizing

32.38. sql_sizing_profiles

32.39. table_constraints

32.40. table_privileges

32.41. tables

32.42. triggers

32.43. usage_privileges

32.44. view_column_usage

32.45. view_routine_usage

32.46. view_table_usage

32.47. views

V. Server Programming

33. Extending SQL

33.1. How Extensibility Works

33.2. The PostgreSQL Type System

33.2.1. Base Types

33.2.2. Composite Types

33.2.3. Domains

33.2.4. Pseudo-Types

33.2.5. Polymorphic Types

33.3. User-Defined Functions

33.4. Query Language (SQL) Functions

33.4.1. SQL Functions on Base Types

33.4.2. SQL Functions on Composite Types

33.4.3. Functions with Output Parameters

33.4.4. SQL Functions as Table Sources

33.4.5. SQL Functions Returning Sets

33.4.6. Polymorphic SQL Functions

33.5. Function Overloading

33.6. Function Volatility Categories

33.7. Procedural Language Functions

33.8. Internal Functions

33.9. C-Language Functions

33.9.1. Dynamic Loading

33.9.2. Base Types in C-Language Functions

33.9.3. Version 0 Calling Conventions

33.9.4. Version 1 Calling Conventions

33.9.5. Writing Code

33.9.6. Compiling and Linking Dynamically-Loaded Functions

33.9.7. Extension Building Infrastructure

33.9.8. Composite-Type Arguments

33.9.9. Returning Rows (Composite Types)

33.9.10. Returning Sets

33.9.11. Polymorphic Arguments and Return Types

33.9.12. Shared Memory and LWLocks

33.10. User-Defined Aggregates

33.11. User-Defined Types

33.12. User-Defined Operators

33.13. Operator Optimization Information

33.13.1. COMMUTATOR

33.13.2. NEGATOR

33.13.3. RESTRICT

33.13.4. JOIN

33.13.5. HASHES

33.13.6. MERGES (SORT1, SORT2, LTCMP, GTCMP)

33.14. Interfacing Extensions To Indexes

33.14.1. Index Methods and Operator Classes

33.14.2. Index Method Strategies

33.14.3. Index Method Support Routines

33.14.4. An Example

33.14.5. Cross-Data-Type Operator Classes

33.14.6. System Dependencies on Operator Classes

33.14.7. Special Features of Operator Classes

34. Triggers

34.1. Overview of Trigger Behavior

34.2. Visibility of Data Changes

34.3. Writing Trigger Functions in C

34.4. A Complete Example

35. The Rule System

35.1. The Query Tree

35.2. Views and the Rule System

35.2.1. How SELECT Rules Work

35.2.2. View Rules in Non-SELECT Statements

35.2.3. The Power of Views in PostgreSQL

35.2.4. Updating a View

35.3. Rules on INSERT, UPDATE, and DELETE

35.3.1. How Update Rules Work

35.3.2. Cooperation with Views

35.4. Rules and Privileges

35.5. Rules and Command Status

35.6. Rules versus Triggers

36. Procedural Languages

36.1. Installing Procedural Languages

37. PL/pgSQL - SQL Procedural Language

37.1. Overview

37.1.1. Advantages of Using PL/pgSQL

37.1.2. Supported Argument and Result Data Types

37.2. Tips for Developing in PL/pgSQL

37.2.1. Handling of Quotation Marks

37.3. Structure of PL/pgSQL

37.4. Declarations

37.4.1. Aliases for Function Parameters

37.4.2. Copying Types

37.4.3. Row Types

37.4.4. Record Types

37.4.5. RENAME

37.5. Expressions

37.6. Basic Statements

37.6.1. Assignment

37.6.2. Executing a Query With No Result

37.6.3. Executing a Query with a Single-Row Result

37.6.4. Doing Nothing At All

37.6.5. Executing Dynamic Commands

37.6.6. Obtaining the Result Status

37.7. Control Structures

37.7.1. Returning From a Function

37.7.2. Conditionals

37.7.3. Simple Loops

37.7.4. Looping Through Query Results

37.7.5. Trapping Errors

37.8. Cursors

37.8.1. Declaring Cursor Variables

37.8.2. Opening Cursors

37.8.3. Using Cursors

37.9. Errors and Messages

37.10. Trigger Procedures

37.11. Porting from Oracle PL/SQL

37.11.1. Porting Examples

37.11.2. Other Things to Watch For

37.11.3. Appendix

38. PL/Tcl - Tcl Procedural Language

38.1. Overview

38.2. PL/Tcl Functions and Arguments

38.3. Data Values in PL/Tcl

38.4. Global Data in PL/Tcl

38.5. Database Access from PL/Tcl

38.6. Trigger Procedures in PL/Tcl

38.7. Modules and the unknown command

38.8. Tcl Procedure Names

39. PL/Perl - Perl Procedural Language

39.1. PL/Perl Functions and Arguments

39.2. Database Access from PL/Perl

39.3. Data Values in PL/Perl

39.4. Global Values in PL/Perl

39.5. Trusted and Untrusted PL/Perl

39.6. PL/Perl Triggers

39.7. Limitations and Missing Features

40. PL/Python - Python Procedural Language

40.1. PL/Python Functions

40.2. Trigger Functions

40.3. Database Access

41. Server Programming Interface

41.1. Interface Functions

41.2. Interface Support Functions

41.3. Memory Management

41.4. Visibility of Data Changes

41.5. Examples

VI. Reference

I. SQL Commands

ABORT — abort the current transaction

ALTER AGGREGATE — change the definition of an aggregate function

ALTER CONVERSION — change the definition of a conversion

ALTER DATABASE — change a database

ALTER DOMAIN — change the definition of a domain

ALTER FUNCTION — change the definition of a function

ALTER GROUP — change role name or membership

ALTER INDEX — change the definition of an index

ALTER LANGUAGE — change the definition of a procedural language

ALTER OPERATOR — change the definition of an operator

ALTER OPERATOR CLASS — change the definition of an operator class

ALTER ROLE — change a database role

ALTER SCHEMA — change the definition of a schema

ALTER SEQUENCE — change the definition of a sequence generator

ALTER TABLE — change the definition of a table

ALTER TABLESPACE — change the definition of a tablespace

ALTER TRIGGER — change the definition of a trigger

ALTER TYPE — change the definition of a type

ALTER USER — change a database role

ANALYZE — collect statistics about a database

BEGIN — start a transaction block

CHECKPOINT — force a transaction log checkpoint

CLOSE — close a cursor

CLUSTER — cluster a table according to an index

COMMENT — define or change the comment of an object

COMMIT — commit the current transaction

COMMIT PREPARED — commit a transaction that was earlier prepared for two-phase commit

COPY — copy data between a file and a table

CREATE AGGREGATE — define a new aggregate function

CREATE CAST — define a new cast

CREATE CONSTRAINT TRIGGER — define a new constraint trigger

CREATE CONVERSION — define a new encoding conversion

CREATE DATABASE — create a new database

CREATE DOMAIN — define a new domain

CREATE FUNCTION — define a new function

CREATE GROUP — define a new database role

CREATE INDEX — define a new index

CREATE LANGUAGE — define a new procedural language

CREATE OPERATOR — define a new operator

CREATE OPERATOR CLASS — define a new operator class

CREATE ROLE — define a new database role

CREATE RULE — define a new rewrite rule

CREATE SCHEMA — define a new schema

CREATE SEQUENCE — define a new sequence generator

CREATE TABLE — define a new table

CREATE TABLE AS — define a new table from the results of a query

CREATE TABLESPACE — define a new tablespace

CREATE TRIGGER — define a new trigger

CREATE TYPE — define a new data type

CREATE USER — define a new database role

CREATE VIEW — define a new view

DEALLOCATE — deallocate a prepared statement

DECLARE — define a cursor

DELETE — delete rows of a table

DROP AGGREGATE — remove an aggregate function

DROP CAST — remove a cast

DROP CONVERSION — remove a conversion

DROP DATABASE — remove a database

DROP DOMAIN — remove a domain

DROP FUNCTION — remove a function

DROP GROUP — remove a database role

DROP INDEX — remove an index

DROP LANGUAGE — remove a procedural language

DROP OPERATOR — remove an operator

DROP OPERATOR CLASS — remove an operator class

DROP OWNED — remove database objects owned by a database role

DROP ROLE — remove a database role

DROP RULE — remove a rewrite rule

DROP SCHEMA — remove a schema

DROP SEQUENCE — remove a sequence

DROP TABLE — remove a table

DROP TABLESPACE — remove a tablespace

DROP TRIGGER — remove a trigger

DROP TYPE — remove a data type

DROP USER — remove a database role

DROP VIEW — remove a view

END — commit the current transaction

EXECUTE — execute a prepared statement

EXPLAIN — show the execution plan of a statement

FETCH — retrieve rows from a query using a cursor

GRANT — define access privileges

INSERT — create new rows in a table

LISTEN — listen for a notification

LOAD — load or reload a shared library file

LOCK — lock a table

MOVE — position a cursor

NOTIFY — generate a notification

PREPARE — prepare a statement for execution

PREPARE TRANSACTION — prepare the current transaction for two-phase commit

REASSIGN OWNED — change the ownership of database objects owned by a database role

REINDEX — rebuild indexes

RELEASE SAVEPOINT — destroy a previously defined savepoint

RESET — restore the value of a run-time parameter to the default value

REVOKE — remove access privileges

ROLLBACK — abort the current transaction

ROLLBACK PREPARED — cancel a transaction that was earlier prepared for two-phase commit

ROLLBACK TO SAVEPOINT — roll back to a savepoint

SAVEPOINT — define a new savepoint within the current transaction

SELECT — retrieve rows from a table or view

SELECT INTO — define a new table from the results of a query

SET — change a run-time parameter

SET CONSTRAINTS — set constraint checking modes for the current transaction

SET ROLE — set the current user identifier of the current session

SET SESSION AUTHORIZATION — set the session user identifier and the current user identifier of the current session

SET TRANSACTION — set the characteristics of the current transaction

SHOW — show the value of a run-time parameter

START TRANSACTION — start a transaction block

TRUNCATE — empty a table or set of tables

UNLISTEN — stop listening for a notification

UPDATE — update rows of a table

VACUUM — garbage-collect and optionally analyze a database

VALUES — compute a set of rows

II. PostgreSQL Client Applications

clusterdb — cluster a PostgreSQL database

createdb — create a new PostgreSQL database

createlang — define a new PostgreSQL procedural language

createuser — define a new PostgreSQL user account

dropdb — remove a PostgreSQL database

droplang — remove a PostgreSQL procedural language

dropuser — remove a PostgreSQL user account

ecpg — embedded SQL C preprocessor

pg_config — retrieve information about the installed version of PostgreSQL

pg_dump — extract a PostgreSQL database into a script file or other archive file

pg_dumpall — extract a PostgreSQL database cluster into a script file

pg_restore — restore a PostgreSQL database from an archive file created by pg_dump

psql — PostgreSQL interactive terminal

reindexdb — reindex a PostgreSQL database

vacuumdb — garbage-collect and analyze a PostgreSQL database

III. PostgreSQL Server Applications

initdb — create a new PostgreSQL database cluster

ipcclean — remove shared memory and semaphores from a failed PostgreSQL server

pg_controldata — display control information of a PostgreSQL database cluster

pg_ctl — start, stop, or restart a PostgreSQL server

pg_resetxlog — reset the write-ahead log and other control information of a PostgreSQL database cluster

postgres — PostgreSQL database server

postmaster — PostgreSQL database server

VII. Internals

42. Overview of PostgreSQL Internals

42.1. The Path of a Query

42.2. How Connections are Established

42.3. The Parser Stage

42.3.1. Parser

42.3.2. Transformation Process

42.4. The PostgreSQL Rule System

42.5. Planner/Optimizer

42.5.1. Generating Possible Plans

42.6. Executor

43. System Catalogs

43.1. Overview

43.2. pg_aggregate

43.3. pg_am

43.4. pg_amop

43.5. pg_amproc

43.6. pg_attrdef

43.7. pg_attribute

43.8. pg_authid

43.9. pg_auth_members

43.10. pg_autovacuum

43.11. pg_cast

43.12. pg_class

43.13. pg_constraint

43.14. pg_conversion

43.15. pg_database

43.16. pg_depend

43.17. pg_description

43.18. pg_index

43.19. pg_inherits

43.20. pg_language

43.21. pg_largeobject

43.22. pg_listener

43.23. pg_namespace

43.24. pg_opclass

43.25. pg_operator

43.26. pg_pltemplate

43.27. pg_proc

43.28. pg_rewrite

43.29. pg_shdepend

43.30. pg_shdescription

43.31. pg_statistic

43.32. pg_tablespace

43.33. pg_trigger

43.34. pg_type

43.35. System Views

43.36. pg_cursors

43.37. pg_group

43.38. pg_indexes

43.39. pg_locks

43.40. pg_prepared_statements

43.41. pg_prepared_xacts

43.42. pg_roles

43.43. pg_rules

43.44. pg_settings

43.45. pg_shadow

43.46. pg_stats

43.47. pg_tables

43.48. pg_timezone_abbrevs

43.49. pg_timezone_names

43.50. pg_user

43.51. pg_views

44. Frontend/Backend Protocol

44.1. Overview

44.1.1. Messaging Overview

44.1.2. Extended Query Overview

44.1.3. Formats and Format Codes

44.2. Message Flow

44.2.1. Start-Up

44.2.2. Simple Query

44.2.3. Extended Query

44.2.4. Function Call

44.2.5. COPY Operations

44.2.6. Asynchronous Operations

44.2.7. Cancelling Requests in Progress

44.2.8. Termination

44.2.9. SSL Session Encryption

44.3. Message Data Types

44.4. Message Formats

44.5. Error and Notice Message Fields

44.6. Summary of Changes since Protocol 2.0

45. PostgreSQL Coding Conventions

45.1. Formatting

45.2. Reporting Errors Within the Server

45.3. Error Message Style Guide

46. Native Language Support

46.1. For the Translator

46.1.1. Requirements

46.1.2. Concepts

46.1.3. Creating and maintaining message catalogs

46.1.4. Editing the PO files

46.2. For the Programmer

46.2.1. Mechanics

46.2.2. Message-writing guidelines

47. Writing A Procedural Language Handler

48. Genetic Query Optimizer

48.1. Query Handling as a Complex Optimization Problem

48.2. Genetic Algorithms

48.3. Genetic Query Optimization (GEQO) in PostgreSQL

48.3.1. Future Implementation Tasks for PostgreSQL GEQO

48.4. Further Reading

49. Index Access Method Interface Definition

49.1. Catalog Entries for Indexes

49.2. Index Access Method Functions

49.3. Index Scanning

49.4. Index Locking Considerations

49.5. Index Uniqueness Checks

49.6. Index Cost Estimation Functions

50. GiST Indexes

50.1. Introduction

50.2. Extensibility

50.3. Implementation

50.4. Examples

50.5. Crash Recovery

51. GIN Indexes

51.1. Introduction

51.2. Extensibility

51.3. Implementation

51.4. GIN tips and tricks

51.5. Limitations

51.6. Examples

52. Database Physical Storage

52.1. Database File Layout

52.2. TOAST

52.3. Database Page Layout

53. BKI Backend Interface

53.1. BKI File Format

53.2. BKI Commands

53.3. Structure of the Bootstrap BKI File

53.4. Example

54. How the Planner Uses Statistics

54.1. Row Estimation Examples

VIII. Appendixes

A. PostgreSQL Error Codes

B. Date/Time Support

B.1. Date/Time Input Interpretation

B.2. Date/Time Key Words

B.3. Date/Time Configuration Files

B.4. History of Units

C. SQL Key Words

D. SQL Conformance

D.1. Supported Features

D.2. Unsupported Features

E. Release Notes

E.1. Release 8.2.10

E.1.1. Migration to Version 8.2.10

E.1.2. Changes

E.2. Release 8.2.9

E.2.1. Migration to Version 8.2.9

E.2.2. Changes

E.3. Release 8.2.8

E.3.1. Migration to Version 8.2.8

E.3.2. Changes

E.4. Release 8.2.7

E.4.1. Migration to Version 8.2.7

E.4.2. Changes

E.5. Release 8.2.6

E.5.1. Migration to Version 8.2.6

E.5.2. Changes

E.6. Release 8.2.5

E.6.1. Migration to Version 8.2.5

E.6.2. Changes

E.7. Release 8.2.4

E.7.1. Migration to Version 8.2.4

E.7.2. Changes

E.8. Release 8.2.3

E.8.1. Migration to Version 8.2.3

E.8.2. Changes

E.9. Release 8.2.2

E.9.1. Migration to Version 8.2.2

E.9.2. Changes

E.10. Release 8.2.1

E.10.1. Migration to Version 8.2.1

E.10.2. Changes

E.11. Release 8.2

E.11.1. Overview

E.11.2. Migration to Version 8.2

E.11.3. Changes

E.12. Release 8.1.14

E.12.1. Migration to Version 8.1.14

E.12.2. Changes

E.13. Release 8.1.13

E.13.1. Migration to Version 8.1.13

E.13.2. Changes

E.14. Release 8.1.12

E.14.1. Migration to Version 8.1.12

E.14.2. Changes

E.15. Release 8.1.11

E.15.1. Migration to Version 8.1.11

E.15.2. Changes

E.16. Release 8.1.10

E.16.1. Migration to Version 8.1.10

E.16.2. Changes

E.17. Release 8.1.9

E.17.1. Migration to Version 8.1.9

E.17.2. Changes

E.18. Release 8.1.8

E.18.1. Migration to Version 8.1.8

E.18.2. Changes

E.19. Release 8.1.7

E.19.1. Migration to Version 8.1.7

E.19.2. Changes

E.20. Release 8.1.6

E.20.1. Migration to Version 8.1.6

E.20.2. Changes

E.21. Release 8.1.5

E.21.1. Migration to Version 8.1.5

E.21.2. Changes

E.22. Release 8.1.4

E.22.1. Migration to Version 8.1.4

E.22.2. Changes

E.23. Release 8.1.3

E.23.1. Migration to Version 8.1.3

E.23.2. Changes

E.24. Release 8.1.2

E.24.1. Migration to Version 8.1.2

E.24.2. Changes

E.25. Release 8.1.1

E.25.1. Migration to Version 8.1.1

E.25.2. Changes

E.26. Release 8.1

E.26.1. Overview

E.26.2. Migration to Version 8.1

E.26.3. Additional Changes

E.27. Release 8.0.18

E.27.1. Migration to Version 8.0.18

E.27.2. Changes

E.28. Release 8.0.17

E.28.1. Migration to Version 8.0.17

E.28.2. Changes

E.29. Release 8.0.16

E.29.1. Migration to Version 8.0.16

E.29.2. Changes

E.30. Release 8.0.15

E.30.1. Migration to Version 8.0.15

E.30.2. Changes

E.31. Release 8.0.14

E.31.1. Migration to Version 8.0.14

E.31.2. Changes

E.32. Release 8.0.13

E.32.1. Migration to Version 8.0.13

E.32.2. Changes

E.33. Release 8.0.12

E.33.1. Migration to Version 8.0.12

E.33.2. Changes

E.34. Release 8.0.11

E.34.1. Migration to Version 8.0.11

E.34.2. Changes

E.35. Release 8.0.10

E.35.1. Migration to Version 8.0.10

E.35.2. Changes

E.36. Release 8.0.9

E.36.1. Migration to Version 8.0.9

E.36.2. Changes

E.37. Release 8.0.8

E.37.1. Migration to Version 8.0.8

E.37.2. Changes

E.38. Release 8.0.7

E.38.1. Migration to Version 8.0.7

E.38.2. Changes

E.39. Release 8.0.6

E.39.1. Migration to Version 8.0.6

E.39.2. Changes

E.40. Release 8.0.5

E.40.1. Migration to Version 8.0.5

E.40.2. Changes

E.41. Release 8.0.4

E.41.1. Migration to Version 8.0.4

E.41.2. Changes

E.42. Release 8.0.3

E.42.1. Migration to Version 8.0.3

E.42.2. Changes

E.43. Release 8.0.2

E.43.1. Migration to Version 8.0.2

E.43.2. Changes

E.44. Release 8.0.1

E.44.1. Migration to Version 8.0.1