PostgreSQL
The PostgreSQL Development Team
Edited by
Thomas Lockhart
PostgreSQL
is Copyright © 1996-9 by the Postgres Global Development Group.
Table of Contents
Summary
I.
User's Guide
1.
Introduction
What is
Postgres
?
A Short History of
Postgres
About This Release
Resources
Terminology
Notation
Y2K Statement
Copyrights and Trademarks
2.
SQL Syntax
Key Words
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
Geometric Functions
IP V4 Functions
6.
Type Conversion
Overview
Operators
Functions
Query Targets
UNION Queries
7.
Indices and Keys
8.
Arrays
9.
Inheritance
10.
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
11.
Setting Up Your Environment
12.
Managing a Database
Database Creation
Alternate Database Locations
Accessing a Database
Destroying a Database
13.
Disk Storage
14.
SQL Commands
ABORT
ALTER TABLE
ALTER USER
BEGIN
CLOSE
CLUSTER
COMMIT
COPY
CREATE AGGREGATE
CREATE DATABASE
CREATE FUNCTION
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 INDEX
DROP LANGUAGE
DROP OPERATOR
DROP RULE
DROP SEQUENCE
DROP TABLE
DROP TRIGGER
DROP TYPE
DROP USER
DROP VIEW
EXPLAIN
FETCH
GRANT
INSERT
LISTEN
LOAD
LOCK
MOVE
NOTIFY
RESET
REVOKE
ROLLBACK
SELECT
SELECT INTO
SET
SHOW
UNLISTEN
UPDATE
VACUUM
15.
Applications
createdb
createuser
destroydb
destroyuser
initdb
initlocation
pgaccess
pgadmin
pg_dump
pg_dumpall
postgres
postmaster
psql
vacuumdb
II.
Administrator's Guide
16.
Ports
Currently Supported Platforms
Unsupported Platforms
17.
Configuration Options
Parameters for Configuration (
configure
)
Parameters for Building (
make
)
Locale Support
Kerberos Authentication
18.
System Layout
19.
Installation
Requirements to Run
Postgres
Installation Procedure
Playing with
Postgres
The Next Step
Porting Notes
20.
Installation on Win32
Building the libraries
Installing the libraries
Using the libraries
21.
Runtime Environment
Using
Postgres
from Unix
Starting
postmaster
Using pg_options
22.
Security
User Authentication
User Names and Groups
Access Control
Functions and Rules
23.
Adding and Deleting Users
24.
Disk Management
Alternate Locations
25.
Managing a Database
Creating a Database
Accessing a Database
Destroying a Database
Backup and Restore
26.
Troubleshooting
Postmaster Startup Failures
Client Connection Problems
Debugging Messages
27.
Database Recovery
28.
Regression Test
Regression Environment
Directory Layout
Regression Test Procedure
Regression Analysis
29.
Release Notes
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
30.
Architecture
Postgres
Architectural Concepts
31.
Extending
SQL
: An Overview
How Extensibility Works
The
Postgres
Type System
About the
Postgres
System Catalogs
32.
Extending
SQL
: Functions
Query Language (
SQL
) Functions
Programming Language Functions
33.
Extending
SQL
: Types
User-Defined Types
34.
Extending
SQL
: Operators
Operator Optimization Information
35.
Extending
SQL
: Aggregates
36.
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
37.
Interfacing Extensions To Indices
38.
GiST Indices
39.
Linking Dynamically-Loaded Functions
ULTRIX
DEC OSF/1
SunOS 4.x
,
Solaris 2.x
and
HP-UX
40.
Triggers
Trigger Creation
Interaction with the Trigger Manager
Visibility of Data Changes
Examples
41.
Server Programming Interface
Interface Functions
Interface Support Functions
Memory Management
Visibility of Data Changes
Examples
42.
Procedural Languages
Installing Procedural Languages
PL/pgSQL
PL/Tcl
IV.
Interfaces
43.
Functions
44.
Large Objects
Historical Note
Inversion Large Objects
Large Object Interfaces
Built in registered functions
Accessing Large Objects from LIBPQ
Sample Program
45.
ecpg
- Embedded
SQL
in
C
Why Embedded
SQL
?
The Concept
How To Use
egpc
Limitations
Porting From Other
RDBMS
Packages
Installation
For the Developer
46.
libpq
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
User Authentication Functions
Environment Variables
Caveats
Sample Programs
47.
libpq C++ Binding
Control and Initialization
libpq++ Classes
Database Connection Functions
Query Execution Functions
Asynchronous Notification
Functions Associated with the COPY Command
Caveats
48.
pgtcl
Commands
Examples
pgtcl Command Reference Information
49.
ODBC Interface
Background
Windows
Applications
Unix Installation
Configuration Files
ApplixWare
50.
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
V.
Developer's Guide
51.
Overview of PostgreSQL Internals
The Path of a Query
How Connections are Established
The Parser Stage
The
Postgres
Rule System
Planner/Optimizer
Executor
52.
pg_options
53.
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
54.
Frontend/Backend Protocol
Overview
Protocol
Message Data Types
Message Formats
55.
Postgres
Signals
56.
gcc
Default Optimizations
57.
Backend Interface
BKI
File Format
General Commands
Macro Commands
Debugging Commands
Example
58.
Page Files
Page Structure
Files
Bugs
VI.
Tutorial
59.
SQL
The Relational Data Model
Relational Data Model Formalities
Operations in the Relational Data Model
The
SQL
Language
60.
Architecture
Postgres
Architectural Concepts
61.
Getting Started
Setting Up Your Environment
Starting the Interactive Monitor (psql)
Managing a Database
62.
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
63.
Advanced
Postgres
SQL
Features
Inheritance
Non-Atomic Values
Time Travel
More Advanced Features
VII.
Appendices
UG1.
Date/Time Support
Time Zones
History
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 v6.5
Toolsets
Alternate Toolsets
Bibliography
Next
Summary