PostgreSQL

The PostgreSQL Development Team

Edited by

Thomas Lockhart

PostgreSQL is Copyright © 1996-2000 by PostgreSQL Inc.

Table of Contents

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

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

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

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

		Next
		Summary