PostgreSQL

The PostgreSQL Development Team

Edited by

Thomas Lockhart

PostgreSQL is copyright (C) 1998 by the Postgres Global Development Group.


Table of Contents
Summary
I. Tutorial
1. Introduction
What is Postgres?
A Short History of Postgres
About This Release
Resources
Terminology
Notation
Y2K Statement
Copyrights and Trademarks
2. Architecture
Postgres Architectural Concepts
3. Getting Started
Setting Up Your Environment
Starting the Interactive Monitor (psql)
Managing a Database
4. 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
5. Advanced Postgres SQL Features
Inheritance
Non-Atomic Values
Time Travel
More Advanced Features
II. User's Guide
6. Setting Up Your Environment
7. Managing a Database
Database Creation
Alternate Database Locations
Accessing a Database
Destroying a Database
8. Data Types
Numeric Types
Monetary Type
Character Types
Date/Time Types
Boolean Type
Geometric Types
IP Version 4 Networks and Host Addresses
9. Operators
Lexical Precedence
General Operators
Numerical Operators
Geometric Operators
Time Interval Operators
IP V4 Operators
IP V4 Operators
10. Functions
Mathematical Functions
String Functions
Date/Time Functions
Geometric Functions
IP V4 Functions
11. Type Conversion
Overview
Operators
Functions
Query Targets
UNION Queries
12. Indices and Keys
13. Arrays
14. Inheritance
15. The Query Language
Concepts
Creating a New Class
Populating a Class with Instances
Querying a Class
Redirecting SELECT Queries
Joins Between Classes
Updates
Deletions
Using Aggregate Functions
16. Disk Storage
17. psql
Paging To Screen
18. pgaccess
19. SQL Commands
ABORT
ALTER TABLE
ALTER USER
BEGIN WORK
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
20. Utility Applications
createdb
createuser
destroydb
destroyuser
initdb
initlocation
pg_dump
pg_dumpall
psql
III. Administrator's Guide
21. Introduction
Resources
Terminology
Notation
Y2K Statement
Copyrights and Trademarks
22. Ports
Currently Supported Platforms
Unsupported Platforms
23. Configuration Options
Parameters for Configuration (configure)
Parameters for Building (make)
Locale Support
Kerberos Authentication
24. Installation
Requirements to Run Postgres
Installation Procedure
Playing with Postgres
The Next Step
Porting Notes
25. Runtime Environment
Using Postgres from Unix
26. System Layout
27. Using pg_options
28. Starting postmaster
29. Adding and Deleting Users
30. Disk Management
Alternate Locations
31. Troubleshooting
32. Managing a Database
Creating a Database
Accessing a Database
Destroying a Database
33. Database Recovery
34. Regression Test
Regression Environment
Directory Layout
Regression Test Procedure
Regression Analysis
35. Release Notes
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
IV. Programmer's Guide
36. Introduction
Resources
Terminology
Notation
Y2K Statement
Copyrights and Trademarks
37. Architecture
Postgres Architectural Concepts
38. Extending SQL: An Overview
How Extensibility Works
The Postgres Type System
About the Postgres System Catalogs
39. Extending SQL: Functions
Query Language (SQL) Functions
Programming Language Functions
40. Extending SQL: Types
User-Defined Types
41. Extending SQL: Operators
42. Extending SQL: Aggregates
43. 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
44. Interfacing Extensions To Indices
45. GiST Indices
46. Linking Dynamically-Loaded Functions
ULTRIX
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
PL/pgSQL
PL/Tcl
V. Interfaces
50. Functions
51. Large Objects
Historical Note
Inversion Large Objects
Large Object 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 egpc
Limitations
Porting From Other RDBMS Packages
Installation
For the Developer
53. 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
54. pgtcl
Commands
Examples
pgtcl Command Reference Information
55. ODBC Interface
Background
Windows Applications
Unix Installation
Configuration Files
ApplixWare
56. 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
VI. Developer's Guide
57. Architecture
Postgres Architectural Concepts
58. 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
59. Frontend/Backend Protocol
Overview
Protocol
Message Data Types
Message Formats
60. Postgres Signals
61. gcc Default Optimizations
62. Backend Interface
BKI File Format
General Commands
Macro Commands
Debugging Commands
Example
63. Page Files
Page Structure
Files
Bugs
VII. Appendices
A. Documentation
Documentation Roadmap
Documentation Sources
The Documentation Project
Building Documentation
Hardcopy Generation for v6.4
Toolsets
Alternate Toolsets
B. Contacts
SQL References