Home

·

·

·

·

·

·

·

pgAdmin 1.4 online documentation

PostgreSQL 8.1.11 Documentation

The PostgreSQL Global Development Group

Copyright � 1996-2005 The PostgreSQL Global Development Group

Table of Contents

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.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. The Connection Service File

28.14. SSL Support

28.15. Behavior in Threaded Programs

28.16. Building libpq Programs

28.17. 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
32.4.4. SQL Functions as Table Sources
32.4.5. SQL Functions Returning Sets
32.4.6. Polymorphic SQL Functions

32.5. Function Overloading

32.6. Function Volatility Categories

32.7. Procedural Language Functions

32.8. Internal Functions

32.9. C-Language Functions

32.9.1. Dynamic Loading
32.9.2. Base Types in C-Language Functions
32.9.3. Calling Conventions Version 0 for C-Language Functions
32.9.4. Calling Conventions Version 1 for C-Language Functions
32.9.5. Writing Code
32.9.6. Compiling and Linking Dynamically-Loaded Functions
32.9.7. Extension Building Infrastructure
32.9.8. Composite-Type Arguments in C-Language Functions
32.9.9. Returning Rows (Composite Types) from C-Language Functions
32.9.10. Returning Sets from C-Language Functions
32.9.11. Polymorphic Arguments and Return Types

32.10. User-Defined Aggregates

32.11. User-Defined Types

32.12. User-Defined Operators

32.13. Operator Optimization Information

32.13.1. COMMUTATOR
32.13.2. NEGATOR
32.13.3. RESTRICT
32.13.4. JOIN
32.13.5. HASHES
32.13.6. MERGES (SORT1, SORT2, LTCMP, GTCMP)

32.14. Interfacing Extensions To Indexes

32.14.1. Index Methods and Operator Classes
32.14.2. Index Method Strategies
32.14.3. Index Method Support Routines
32.14.4. An Example
32.14.5. Cross-Data-Type Operator Classes
32.14.6. System Dependencies on Operator Classes
32.14.7. Special Features of Operator Classes

33. Triggers

33.1. Overview of Trigger Behavior
33.2. Visibility of Data Changes
33.3. Writing Trigger Functions in C
33.4. A Complete Example

34. The Rule System

34.1. The Query Tree

34.2. Views and the Rule System

34.2.1. How SELECT Rules Work
34.2.2. View Rules in Non-SELECT Statements
34.2.3. The Power of Views in PostgreSQL
34.2.4. Updating a View

34.3. Rules on INSERT, UPDATE, and DELETE

34.3.1. How Update Rules Work
34.3.2. Cooperation with Views

34.4. Rules and Privileges

34.5. Rules and Command Status

34.6. Rules versus Triggers

35. Procedural Languages

35.1. Installing Procedural Languages

36. PL/pgSQL - SQL Procedural Language

36.1. Overview

36.1.1. Advantages of Using PL/pgSQL
36.1.2. Supported Argument and Result Data Types

36.2. Tips for Developing in PL/pgSQL

36.2.1. Handling of Quotation Marks

36.3. Structure of PL/pgSQL

36.4. Declarations

36.4.1. Aliases for Function Parameters
36.4.2. Copying Types
36.4.3. Row Types
36.4.4. Record Types
36.4.5. RENAME

36.5. Expressions

36.6. Basic Statements

36.6.1. Assignment
36.6.2. SELECT INTO
36.6.3. Executing an Expression or Query With No Result
36.6.4. Doing Nothing At All
36.6.5. Executing Dynamic Commands
36.6.6. Obtaining the Result Status

36.7. Control Structures

36.7.1. Returning From a Function
36.7.2. Conditionals
36.7.3. Simple Loops
36.7.4. Looping Through Query Results
36.7.5. Trapping Errors

36.8. Cursors

36.8.1. Declaring Cursor Variables
36.8.2. Opening Cursors
36.8.3. Using Cursors

36.9. Errors and Messages

36.10. Trigger Procedures

36.11. Porting from Oracle PL/SQL

36.11.1. Porting Examples
36.11.2. Other Things to Watch For
36.11.3. Appendix

37. PL/Tcl - Tcl Procedural Language

37.1. Overview
37.2. PL/Tcl Functions and Arguments
37.3. Data Values in PL/Tcl
37.4. Global Data in PL/Tcl
37.5. Database Access from PL/Tcl
37.6. Trigger Procedures in PL/Tcl
37.7. Modules and the unknown command
37.8. Tcl Procedure Names

38. PL/Perl - Perl Procedural Language

38.1. PL/Perl Functions and Arguments
38.2. Database Access from PL/Perl
38.3. Data Values in PL/Perl
38.4. Global Values in PL/Perl
38.5. Trusted and Untrusted PL/Perl
38.6. PL/Perl Triggers
38.7. Limitations and Missing Features

39. PL/Python - Python Procedural Language

39.1. PL/Python Functions
39.2. Trigger Functions
39.3. Database Access

40. Server Programming Interface

40.1. Interface Functions
40.2. Interface Support Functions
40.3. Memory Management
40.4. Visibility of Data Changes
40.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 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
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

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 — run a PostgreSQL server in single-user mode
postmaster — PostgreSQL multiuser database server

VII. Internals

41. Overview of PostgreSQL Internals

41.1. The Path of a Query

41.2. How Connections are Established

41.3. The Parser Stage

41.3.1. Parser
41.3.2. Transformation Process

41.4. The PostgreSQL Rule System

41.5. Planner/Optimizer

41.5.1. Generating Possible Plans

41.6. Executor

42. System Catalogs

43. Frontend/Backend Protocol

43.1. Overview

43.1.1. Messaging Overview
43.1.2. Extended Query Overview
43.1.3. Formats and Format Codes

43.2. Message Flow

43.2.1. Start-Up
43.2.2. Simple Query
43.2.3. Extended Query
43.2.4. Function Call
43.2.5. COPY Operations
43.2.6. Asynchronous Operations
43.2.7. Cancelling Requests in Progress
43.2.8. Termination
43.2.9. SSL Session Encryption

43.3. Message Data Types

43.4. Message Formats

43.5. Error and Notice Message Fields

43.6. Summary of Changes since Protocol 2.0

44. PostgreSQL Coding Conventions

44.1. Formatting
44.2. Reporting Errors Within the Server
44.3. Error Message Style Guide

45. Native Language Support

45.1. For the Translator

45.1.1. Requirements
45.1.2. Concepts
45.1.3. Creating and maintaining message catalogs
45.1.4. Editing the PO files

45.2. For the Programmer

45.2.1. Mechanics
45.2.2. Message-writing guidelines

46. Writing A Procedural Language Handler

47. Genetic Query Optimizer

47.1. Query Handling as a Complex Optimization Problem

47.2. Genetic Algorithms

47.3. Genetic Query Optimization (GEQO) in PostgreSQL

47.3.1. Future Implementation Tasks for PostgreSQL GEQO

47.4. Further Reading

48. Index Access Method Interface Definition

48.1. Catalog Entries for Indexes
48.2. Index Access Method Functions
48.3. Index Scanning
48.4. Index Locking Considerations
48.5. Index Uniqueness Checks
48.6. Index Cost Estimation Functions

49. GiST Indexes

49.1. Introduction
49.2. Extensibility
49.3. Implementation
49.4. Examples
49.5. Crash Recovery

50. Database Physical Storage

50.1. Database File Layout
50.2. TOAST
50.3. Database Page Layout

51. BKI Backend Interface

51.1. BKI File Format
51.2. BKI Commands
51.3. Structure of the Bootstrap BKI File
51.4. Example

52. How the Planner Uses Statistics

52.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. 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.1.11

E.1.1. Migration to Version 8.1.11
E.1.2. Changes

E.2. Release 8.1.10

E.2.1. Migration to Version 8.1.10
E.2.2. Changes

E.3. Release 8.1.9

E.3.1. Migration to Version 8.1.9
E.3.2. Changes

E.4. Release 8.1.8

E.4.1. Migration to Version 8.1.8
E.4.2. Changes

E.5. Release 8.1.7

E.5.1. Migration to Version 8.1.7
E.5.2. Changes

E.6. Release 8.1.6

E.6.1. Migration to Version 8.1.6
E.6.2. Changes

E.7. Release 8.1.5

E.7.1. Migration to Version 8.1.5
E.7.2. Changes

E.8. Release 8.1.4

E.8.1. Migration to Version 8.1.4
E.8.2. Changes

E.9. Release 8.1.3

E.9.1. Migration to Version 8.1.3
E.9.2. Changes

E.10. Release 8.1.2

E.10.1. Migration to Version 8.1.2
E.10.2. Changes

E.11. Release 8.1.1

E.11.1. Migration to Version 8.1.1
E.11.2. Changes

E.12. Release 8.1

E.12.1. Overview
E.12.2. Migration to Version 8.1
E.12.3. Additional Changes

E.13. Release 8.0.15

E.13.1. Migration to Version 8.0.15
E.13.2. Changes

E.14. Release 8.0.14

E.14.1. Migration to Version 8.0.14
E.14.2. Changes

E.15. Release 8.0.13

E.15.1. Migration to Version 8.0.13
E.15.2. Changes

E.16. Release 8.0.12

E.16.1. Migration to Version 8.0.12
E.16.2. Changes

E.17. Release 8.0.11

E.17.1. Migration to Version 8.0.11
E.17.2. Changes

E.18. Release 8.0.10

E.18.1. Migration to Version 8.0.10
E.18.2. Changes

E.19. Release 8.0.9

E.19.1. Migration to Version 8.0.9
E.19.2. Changes

E.20. Release 8.0.8

E.20.1. Migration to Version 8.0.8
E.20.2. Changes

E.21. Release 8.0.7

E.21.1. Migration to Version 8.0.7
E.21.2. Changes

E.22. Release 8.0.6

E.22.1. Migration to Version 8.0.6
E.22.2. Changes

E.23. Release 8.0.5

E.23.1. Migration to Version 8.0.5
E.23.2. Changes

E.24. Release 8.0.4

E.24.1. Migration to Version 8.0.4
E.24.2. Changes

E.25. Release 8.0.3

E.25.1. Migration to Version 8.0.3
E.25.2. Changes

E.26. Release 8.0.2

E.26.1. Migration to Version 8.0.2
E.26.2. Changes

E.27. Release 8.0.1

E.27.1. Migration to Version 8.0.1
E.27.2. Changes

E.28. Release 8.0

E.28.1. Overview
E.28.2. Migration to Version 8.0
E.28.3. Deprecated Features
E.28.4. Changes

E.29. Release 7.4.19

E.29.1. Migration to Version 7.4.19
E.29.2. Changes

E.30. Release 7.4.18

E.30.1. Migration to Version 7.4.18
E.30.2. Changes

E.31. Release 7.4.17

E.31.1. Migration to Version 7.4.17
E.31.2. Changes

E.32. Release 7.4.16

E.32.1. Migration to Version 7.4.16
E.32.2. Changes

E.33. Release 7.4.15

E.33.1. Migration to Version 7.4.15
E.33.2. Changes

E.34. Release 7.4.14

E.34.1. Migration to Version 7.4.14
E.34.2. Changes

E.35. Release 7.4.13

E.35.1. Migration to Version 7.4.13
E.35.2. Changes

E.36. Release 7.4.12

E.36.1. Migration to Version 7.4.12
E.36.2. Changes

E.37. Release 7.4.11

E.37.1. Migration to Version 7.4.11
E.37.2. Changes

E.38. Release 7.4.10

E.38.1. Migration to Version 7.4.10
E.38.2. Changes

E.39. Release 7.4.9

E.39.1. Migration to Version 7.4.9
E.39.2. Changes

E.40. Release 7.4.8

E.40.1. Migration to Version 7.4.8
E.40.2. Changes

E.41. Release 7.4.7

E.41.1. Migration to Version 7.4.7
E.41.2. Changes

E.42. Release 7.4.6

E.42.1. Migration to Version 7.4.6
E.42.2. Changes

E.43. Release 7.4.5

E.43.1. Migration to Version 7.4.5
E.43.2. Changes

E.44. Release 7.4.4

E.44.1. Migration to Version 7.4.4
E.44.2. Changes

E.45. Release 7.4.3

E.45.1. Migration to Version 7.4.3
E.45.2. Changes

E.46. Release 7.4.2

E.46.1. Migrat