PostgreSQL

The PostgreSQL Development Team

Edited by

Thomas Lockhart

PostgreSQL is Copyright © 1996-2000 by PostgreSQL Inc.


Table of Contents
Summary
I. User's Guide
1. Introduction
What is Postgres?
A Short History of Postgres
About This Release
Resources
Terminology
Notation
Problem Reporting Guidelines
Y2K Statement
Copyrights and Trademarks
2. SQL Syntax
Key Words
Comments
Names
Constants
Fields and Columns
Operators
Expressions
3. Data Types
Numeric Types
Monetary Type
Character Types
Date/Time Types
Boolean Type
Geometric Types
IP Version 4 Networks and Host Addresses
4. Operators
Lexical Precedence
General Operators
Numerical Operators
Geometric Operators
Time Interval Operators
IP V4 CIDR Operators
IP V4 INET Operators
5. Functions
SQL Functions
Mathematical Functions
String Functions
Date/Time Functions
Formatting Functions
Geometric Functions
IP V4 Functions
6. Type Conversion
Overview
Operators
Functions
Query Targets
UNION Queries
7. Indices and Keys
Keys
Partial Indices
8. Arrays
9. Inheritance
10. PL/pgSQL - SQL Procedural Language
Overview
Description
Examples
11. PL/Tcl - TCL Procedural Language
Overview
Description
12. PL/perl - Perl Procedural Language
Overview
Building and Installing
Using PL/Perl
13. Multi-Version Concurrency Control
Introduction
Transaction Isolation
Read Committed Isolation Level
Serializable Isolation Level
Locking and Tables
Locking and Indices
Data consistency checks at the application level
14. Setting Up Your Environment
15. Managing a Database
Database Creation
Alternate Database Locations
Accessing a Database
Destroying a Database
16. Disk Storage
17. Understanding Performance
Using EXPLAIN
18. Populating a Database
Disable Auto-commit
Use COPY FROM
Remove Indices
19. SQL Commands
ABORT
ALTER GROUP
ALTER TABLE
ALTER USER
BEGIN
CLOSE
CLUSTER
COMMENT
COMMIT
COPY
CREATE AGGREGATE
CREATE CONSTRAINT TRIGGER
CREATE DATABASE
CREATE FUNCTION
CREATE GROUP
CREATE INDEX
CREATE LANGUAGE
CREATE OPERATOR
CREATE RULE
CREATE SEQUENCE
CREATE TABLE
CREATE TABLE AS
CREATE TRIGGER
CREATE TYPE
CREATE USER
CREATE VIEW
DECLARE
DELETE
DROP AGGREGATE
DROP DATABASE
DROP FUNCTION
DROP GROUP
DROP INDEX
DROP LANGUAGE
DROP OPERATOR
DROP RULE
DROP SEQUENCE
DROP TABLE
DROP TRIGGER
DROP TYPE
DROP USER
DROP VIEW
END
EXPLAIN
FETCH
GRANT
INSERT
LISTEN
LOAD
LOCK
MOVE
NOTIFY
REINDEX
RESET
REVOKE
ROLLBACK
SELECT
SELECT INTO
SET
SHOW
TRUNCATE
UNLISTEN
UPDATE
VACUUM
20. Applications
createdb
createlang
createuser
dropdb
droplang
dropuser
ecpg
pgaccess
pgadmin
pg_ctl
pg_dump
pg_dumpall
psql
pgtclsh
pgtksh
vacuumdb
21. System Applications
initdb
initlocation
ipcclean
pg_passwd
pg_upgrade
postgres
postmaster
II. Administrator's Guide
22. Ports
Currently Supported Platforms
Unsupported Platforms
23. Configuration Options
Parameters for Configuration (configure)
Parameters for Building (make)
Locale Support
Kerberos Authentication
24. System Layout
25. Installation
Before you start
Installation Procedure
26. Installation on Win32
Building the libraries
Installing the libraries
Using the libraries
27. Runtime Environment
Using Postgres from Unix
Starting postmaster
Using pg_options
28. Security
User Authentication
User Names and Groups
Access Control
Functions and Rules
Secure TCP/IP Connection
29. Adding and Deleting Users
30. Disk Management
Alternate Locations
31. Managing a Database
Creating a Database
Accessing a Database
Destroying a Database
Backup and Restore
32. Troubleshooting
Postmaster Startup Failures
Client Connection Problems
Debugging Messages
33. Database Recovery
34. Regression Test
Regression Environment
Regression Test Procedure
Regression Analysis
Platform-specific comparison files
35. Release Notes
Release 7.0
Release 6.5.3
Release 6.5.2
Release 6.5.1
Release 6.5
Release 6.4.2
Release 6.4.1
Release 6.4
Release 6.3.2
Release 6.3.1
Release 6.3
Release 6.2.1
Release 6.2
Release 6.1.1
Release 6.1
Release v6.0
Release v1.09
Release v1.02
Release v1.01
Release v1.0
Postgres95 Beta 0.03
Postgres95 Beta 0.02
Postgres95 Beta 0.01
Timing Results
III. Programmer's Guide
36. Architecture
Postgres Architectural Concepts
37. Extending SQL: An Overview
How Extensibility Works
The Postgres Type System
About the Postgres System Catalogs
38. Extending SQL: Functions
Query Language (SQL) Functions
Procedural Language Functions
Internal Functions
Compiled (C) Language Functions
Function Overloading
39. Extending SQL: Types
User-Defined Types
40. Extending SQL: Operators
Operator Optimization Information
41. Extending SQL: Aggregates
42. The Postgres Rule System
What is a Querytree?
Views and the Rule System
Rules on INSERT, UPDATE and DELETE
Rules and Permissions
Rules versus Triggers
43. Interfacing Extensions To Indices
44. Index Cost Estimation Functions
45. GiST Indices
46. Linking Dynamically-Loaded Functions
Linux
DEC OSF/1
SunOS 4.x, Solaris 2.x and HP-UX
47. Triggers
Trigger Creation
Interaction with the Trigger Manager
Visibility of Data Changes
Examples
48. Server Programming Interface
Interface Functions
Interface Support Functions
Memory Management
Visibility of Data Changes
Examples
49. Procedural Languages
Installing Procedural Languages
IV. Interfaces
50. Functions
51. Large Objects
Historical Note
Implementation Features
Interfaces
Built in registered functions
Accessing Large Objects from LIBPQ
Sample Program
52. ecpg - Embedded SQL in C
Why Embedded SQL?
The Concept
How To Use ecpg
Limitations
Porting From Other RDBMS Packages
Installation
For the Developer
53. libpq - C Library
Database Connection Functions
Query Execution Functions
Asynchronous Query Processing
Fast Path
Asynchronous Notification
Functions Associated with the COPY Command
libpq Tracing Functions
libpq Control Functions
Environment Variables
Threading Behavior
Sample Programs
54. libpq - C++ Binding Library
Control and Initialization
libpq++ Classes
Database Connection Functions
Query Execution Functions
Asynchronous Notification
Functions Associated with the COPY Command
55. pgtcl - TCL Binding Library
Commands
Examples
pgtcl Command Reference Information
56. libpgeasy - Simplified C Binding Library
57. ODBC Interface
Background
Windows Applications
Unix Installation
Configuration Files
ApplixWare
58. JDBC Interface
Building the JDBC Interface
Preparing the Database for JDBC
Using the Driver
Importing JDBC
Loading the Driver
Connecting to the Database
Issuing a Query and Processing the Result
Performing Updates
Closing the Connection
Using Large Objects
Postgres Extensions to the JDBC API
Further Reading
59. Lisp Programming Interface
V. Developer's Guide
60. Postgres Source Code
Formatting
61. Overview of PostgreSQL Internals
The Path of a Query
How Connections are Established
The Parser Stage
The Postgres Rule System
Planner/Optimizer
Executor
62. pg_options
63. Genetic Query Optimization in Database Systems
Query Handling as a Complex Optimization Problem
Genetic Algorithms (GA)
Genetic Query Optimization (GEQO) in Postgres
Future Implementation Tasks for Postgres GEQO
64. Frontend/Backend Protocol
Overview
Protocol
Message Data Types
Message Formats
65. Postgres Signals
66. gcc Default Optimizations
67. Backend Interface
BKI File Format
General Commands
Macro Commands
Debugging Commands
Example
68. Page Files
Page Structure
Files
Bugs
VI. Tutorial
69. SQL
The Relational Data Model
Relational Data Model Formalities
Operations in the Relational Data Model
The SQL Language
70. Architecture
Postgres Architectural Concepts
71. Getting Started
Setting Up Your Environment
Starting the Interactive Monitor (psql)
Managing a Database
72. The Query Language
Interactive Monitor
Concepts
Creating a New Class
Populating a Class with Instances
Querying a Class
Redirecting SELECT Queries
Joins Between Classes
Updates
Deletions
Using Aggregate Functions
73. Advanced Postgres SQL Features
Inheritance
Non-Atomic Values
More Advanced Features
VII. Appendices
UG1. Date/Time Support
Time Zones
History of Units
DG1. The CVS Repository
CVS Tree Organization
Getting The Source Via Anonymous CVS
Getting The Source Via CVSup
DG2. Documentation
Documentation Roadmap
The Documentation Project
Documentation Sources
Building Documentation
Manpages
Hardcopy Generation for v7.0
Toolsets
Alternate Toolsets
Bibliography