PostgreSQL 7.3 Documentation

The PostgreSQL Global Development Group

Copyright © 1996-2002 by The PostgreSQL Global Development Group

Table of Contents

PostgreSQL 7.3 Tutorial

Preface

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

Choosing the right office furniture for your office

PostgreSQL 7.3 User's Guide

Preface

1. What is PostgreSQL?

2. A Short History of PostgreSQL

3. What's In This Book

4. Overview of Documentation Resources

5. Terminology and Notation

6. Bug Reporting Guidelines

1. SQL Syntax

1.1. Lexical Structure

1.2. Value Expressions

2. Data Definition

2.1. Table Basics

2.2. System Columns

2.3. Default Values

2.4. Constraints

2.5. Inheritance

2.6. Modifying Tables

2.7. Privileges

2.8. Schemas

2.9. Other Database Objects

2.10. Dependency Tracking

3. Data Manipulation

3.1. Inserting Data

3.2. Updating Data

3.3. Deleting Data

4. Queries

4.1. Overview

4.2. Table Expressions

4.3. Select Lists

4.4. Combining Queries

4.5. Sorting Rows

4.6. LIMIT and OFFSET

5. Data Types

5.1. Numeric Types

5.2. Monetary Type

5.3. Character Types

5.4. Binary Strings

5.5. Date/Time Types

5.6. Boolean Type

5.7. Geometric Types

5.8. Network Address Data Types

5.9. Bit String Types

5.10. Object Identifier Types

5.11. Pseudo-Types

5.12. Arrays

6. Functions and Operators

6.1. Logical Operators

6.2. Comparison Operators

6.3. Mathematical Functions and Operators

6.4. String Functions and Operators

6.5. Binary String Functions and Operators

6.6. Pattern Matching

6.7. Data Type Formatting Functions

6.8. Date/Time Functions and Operators

6.9. Geometric Functions and Operators

6.10. Network Address Type Functions

6.11. Sequence-Manipulation Functions

6.12. Conditional Expressions

6.13. Miscellaneous Functions

6.14. Aggregate Functions

6.15. Subquery Expressions

7. Type Conversion

7.1. Overview

7.2. Operators

7.3. Functions

7.4. Query Targets

7.5. UNION and CASE Constructs

8. Indexes

8.1. Introduction

8.2. Index Types

8.3. Multicolumn Indexes

8.4. Unique Indexes

8.5. Functional Indexes

8.6. Operator Classes

8.7. Partial Indexes

8.8. Examining Index Usage

9. Concurrency Control

9.1. Introduction

9.2. Transaction Isolation

9.3. Explicit Locking

9.4. Data Consistency Checks at the Application Level

9.5. Locking and Indexes

10. Performance Tips

10.1. Using EXPLAIN

10.2. Statistics Used by the Planner

10.3. Controlling the Planner with Explicit JOIN Clauses

10.4. Populating a Database

A. Date/Time Support

A.1. Date/Time Input Interpretation

A.2. Date/Time Key Words

A.3. History of Units

B. SQL Key Words

C. SQL Conformance

Bibliography

PostgreSQL 7.3 Administrator's Guide

Preface

1. Installation Instructions

1.1. Short Version

1.2. Requirements

1.3. Getting The Source

1.4. If You Are Upgrading

1.5. Installation Procedure

1.6. Post-Installation Setup

1.7. Supported Platforms

2. Installation on Windows

3. Server Run-time Environment

3.1. The PostgreSQL User Account

3.2. Creating a Database Cluster

3.3. Starting the Database Server

3.4. Run-time Configuration

3.5. Managing Kernel Resources

3.6. Shutting Down the Server

3.7. Secure TCP/IP Connections with SSL

3.8. Secure TCP/IP Connections with SSH Tunnels

4. Database Users and Privileges

4.1. Database Users

4.2. User Attributes

4.3. Groups

4.4. Privileges

4.5. Functions and Triggers

5. Managing Databases

5.1. Overview

5.2. Creating a Database

5.3. Template Databases

5.4. Database Configuration

5.5. Alternative Locations

5.6. Destroying a Database

6. Client Authentication

6.1. The pg_hba.conf file

6.2. Authentication methods

6.3. Authentication problems

7. Localization

7.1. Locale Support

7.2. Multibyte Support

8. Routine Database Maintenance Tasks

8.1. General Discussion

8.2. Routine Vacuuming

8.3. Routine Reindexing

8.4. Log File Maintenance

9. Backup and Restore

9.1. SQL Dump

9.2. File system level backup

9.3. Migration between releases

10. Monitoring Database Activity

10.1. Standard Unix Tools

10.2. Statistics Collector

10.3. Viewing Locks

11. Monitoring Disk Usage

11.1. Determining Disk Usage

11.2. Disk Full Failure

12. Write-Ahead Logging (WAL)

12.1. General Description

12.2. Implementation

12.3. WAL Configuration

13. Regression Tests

13.1. Introduction

13.2. Running the Tests

13.3. Test Evaluation

13.4. Platform-specific comparison files

A. Release Notes

A.1. Release 7.3

A.2. Release 7.2.3

A.3. Release 7.2.2

A.4. Release 7.2.1

A.5. Release 7.2

A.6. Release 7.1.3

A.7. Release 7.1.2

A.8. Release 7.1.1

A.9. Release 7.1

A.10. Release 7.0.3

A.11. Release 7.0.2

A.12. Release 7.0.1

A.13. Release 7.0

A.14. Release 6.5.3

A.15. Release 6.5.2

A.16. Release 6.5.1

A.17. Release 6.5

A.18. Release 6.4.2

A.19. Release 6.4.1

A.20. Release 6.4

A.21. Release 6.3.2

A.22. Release 6.3.1

A.23. Release 6.3

A.24. Release 6.2.1

A.25. Release 6.2

A.26. Release 6.1.1

A.27. Release 6.1

A.28. Release 6.0

A.29. Release 1.09

A.30. Release 1.02

A.31. Release 1.01

A.32. Release 1.0

A.33. Postgres95 Release 0.03

A.34. Postgres95 Release 0.02

A.35. Postgres95 Release 0.01

PostgreSQL 7.3 Programmer's Guide

Preface

I. Client Interfaces

1. libpq - C Library

1.1. Introduction

1.2. Database Connection Functions

1.3. Command Execution Functions

1.4. Asynchronous Query Processing

1.5. The Fast-Path Interface

1.6. Asynchronous Notification

1.7. Functions Associated with the COPY Command

1.8. libpq Tracing Functions

1.9. libpq Control Functions

1.10. Environment Variables

1.11. Files

1.12. Threading Behavior

1.13. Building Libpq Programs

1.14. Example Programs

2. Large Objects

2.1. Introduction

2.2. Implementation Features

2.3. Interfaces

2.4. Server-side Built-in Functions

2.5. Accessing Large Objects from Libpq

3. pgtcl - Tcl Binding Library

3.1. Introduction

3.2. Loading pgtcl into your application

3.3. pgtcl Command Reference Information

4. ECPG - Embedded SQL in C

4.1. The Concept

4.2. Connecting to the Database Server

4.3. Closing a Connection

4.4. Running SQL Commands

4.5. Passing Data

4.6. Error Handling

4.7. Including Files

4.8. Processing Embedded SQL Programs

4.9. Library Functions

4.10. Porting From Other RDBMS Packages

4.11. For the Developer

5. JDBC Interface

5.1. Setting up the JDBC Driver

5.2. Using the Driver

5.3. Issuing a Query and Processing the Result

5.4. Performing Updates

5.5. Creating and Modifying Database Objects

5.6. Storing Binary Data

5.7. PostgreSQL Extensions to the JDBC API

5.8. Using the driver in a multithreaded or a servlet environment

5.9. Connection Pools And DataSources

5.10. Further Reading

6. PyGreSQL - Python Interface

6.1. The pg Module

6.2. pg Module Functions

6.3. Connection Object: pgobject

6.4. Database Wrapper Class: DB

6.5. Query Result Object: pgqueryobject

6.6. Large Object: pglarge

II. Server Programming

7. Architecture

7.1. PostgreSQL Architectural Concepts

8. Extending SQL: An Overview

8.1. How Extensibility Works

8.2. The PostgreSQL Type System

8.3. About the PostgreSQL System Catalogs

9. Extending SQL: Functions

9.1. Introduction

9.2. Query Language (SQL) Functions

9.3. Procedural Language Functions

9.4. Internal Functions

9.5. C Language Functions

9.6. Function Overloading

9.7. Table Functions

9.8. Procedural Language Handlers

10. Extending SQL: Types

11. Extending SQL: Operators

11.1. Introduction

11.2. Example

11.3. Operator Optimization Information

12. Extending SQL: Aggregates

13. The Rule System

13.1. Introduction

13.2. What is a Query Tree?

13.3. Views and the Rule System

13.4. Rules on INSERT, UPDATE and DELETE

13.5. Rules and Permissions

13.6. Rules and Command Status

13.7. Rules versus Triggers

14. Interfacing Extensions To Indexes

14.1. Introduction

14.2. Access Methods and Operator Classes

14.3. Access Method Strategies

14.4. Access Method Support Routines

14.5. Creating the Operators and Support Routines

14.6. Creating the Operator Class

14.7. Special Features of Operator Classes

15. Index Cost Estimation Functions

16. Triggers

16.1. Trigger Definition

16.2. Interaction with the Trigger Manager

16.3. Visibility of Data Changes

16.4. Examples

17. Server Programming Interface

17.1. Interface Functions

17.2. Interface Support Functions

17.3. Memory Management

17.4. Visibility of Data Changes

17.5. Examples

III. Procedural Languages

18. Procedural Languages

18.1. Introduction

18.2. Installing Procedural Languages

19. PL/pgSQL - SQL Procedural Language

19.1. Overview

19.2. Structure of PL/pgSQL

19.3. Declarations

19.4. Expressions

19.5. Basic Statements

19.6. Control Structures

19.7. Cursors

19.8. Errors and Messages

19.9. Trigger Procedures

19.10. Examples

19.11. Porting from Oracle PL/SQL

20. PL/Tcl - Tcl Procedural Language

20.1. Overview

20.2. Description

21. PL/Perl - Perl Procedural Language

21.1. PL/Perl Functions and Arguments

21.2. Data Values in PL/Perl

21.3. Database Access from PL/Perl

21.4. Trusted and Untrusted PL/Perl

21.5. Missing Features

22. PL/Python - Python Procedural Language

22.1. PL/Python Functions

22.2. Trigger Functions

22.3. Database Access

22.4. Restricted Environment

PostgreSQL 7.3 Reference Manual

Preface

I. SQL Commands

ABORT --  abort the current transaction

ALTER DATABASE -- change a database

ALTER GROUP --  add users to a group or remove users from a group

ALTER TABLE --  change the definition of a table

ALTER TRIGGER --  change the definition of a trigger

ALTER USER -- change a database user account

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

COPY --  copy data between files and tables

CREATE AGGREGATE --  define a new aggregate function

CREATE CAST -- define a user-defined cast

CREATE CONSTRAINT TRIGGER --  define a new constraint trigger

CREATE CONVERSION -- define a user-defined conversion

CREATE DATABASE --  create a new database

CREATE DOMAIN --  define a new domain

CREATE FUNCTION -- define a new function

CREATE GROUP --  define a new user group

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 for indexes

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 -- create a new table from the results of a query

CREATE TRIGGER --  define a new trigger

CREATE TYPE --  define a new data type

CREATE USER -- define a new database user account

CREATE VIEW --  define a new view

DEALLOCATE --  remove a prepared query

DECLARE --  define a cursor

DELETE --  delete rows of a table

DROP AGGREGATE --  remove a user-defined aggregate function

DROP CAST -- remove a user-defined cast

DROP CONVERSION -- remove a user-defined conversion

DROP DATABASE --  remove a database

DROP DOMAIN --  remove a user-defined domain

DROP FUNCTION --  remove a user-defined function

DROP GROUP --  remove a user group

DROP INDEX --  remove an index

DROP LANGUAGE --  remove a user-defined procedural language

DROP OPERATOR --  remove a user-defined operator

DROP OPERATOR CLASS --  remove a user-defined operator class

DROP RULE --  remove a rewrite rule

DROP SCHEMA --  remove a schema

DROP SEQUENCE --  remove a sequence

DROP TABLE --  remove a table

DROP TRIGGER --  remove a trigger

DROP TYPE --  remove a user-defined data type

DROP USER -- remove a database user account

DROP VIEW --  remove a view

END --  commit the current transaction

EXECUTE --  execute a prepared query

EXPLAIN --  show the execution plan of a statement

FETCH --  retrieve rows from a table 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 --  explicitly lock a table

MOVE --  position a cursor on a specified row of a table

NOTIFY --  generate a notification

PREPARE --  create a prepared query

REINDEX --  rebuild corrupted indexes

RESET -- restore the value of a run-time parameter to a default value

REVOKE -- remove access privileges

ROLLBACK --  abort the current transaction

SELECT --  retrieve rows from a table or view

SELECT INTO --  create a new table from the results of a query

SET -- change a run-time parameter

SET CONSTRAINTS -- set the constraint mode of the current transaction

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

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

pgtclsh --  PostgreSQL Tcl shell client

pgtksh --  PostgreSQL Tcl/Tk shell client

vacuumdb -- garbage-collect and analyze a PostgreSQL database

III. PostgreSQL Server Applications

initdb -- create a new PostgreSQL database cluster

initlocation -- create a secondary PostgreSQL database storage area

ipcclean -- remove shared memory and semaphores from an aborted PostgreSQL server

pg_ctl -- start, stop, or restart a PostgreSQL server

pg_controldata -- display server-wide control information

pg_resetxlog -- reset write-ahead log and pg_control contents

postgres -- run a PostgreSQL server in single-user mode

postmaster -- PostgreSQL multiuser database server

PostgreSQL 7.3 Developer's Guide

1. PostgreSQL Source Code

2. Overview of PostgreSQL Internals

2.1. The Path of a Query

2.2. How Connections are Established

2.3. The Parser Stage

2.4. The PostgreSQL Rule System

2.5. Planner/Optimizer

2.6. Executor

3. System Catalogs

3.1. Overview

3.2. pg_aggregate

3.3. pg_am

3.4. pg_amop

3.5. pg_amproc

3.6. pg_attrdef

3.7. pg_attribute

3.8. pg_cast

3.9. pg_class

3.10. pg_constraint

3.11. pg_conversion

3.12. pg_database

3.13. pg_depend

3.14. pg_description

3.15. pg_group

3.16. pg_index

3.17. pg_inherits

3.18. pg_language

3.19. pg_largeobject

3.20. pg_listener

3.21. pg_namespace

3.22. pg_opclass

3.23. pg_operator

3.24. pg_proc

3.25. pg_rewrite

3.26. pg_shadow

3.27. pg_statistic

3.28. pg_trigger

3.29. pg_type

4. Frontend/Backend Protocol

4.1. Overview

4.2. Protocol

4.3. Message Data Types

4.4. Message Formats

5. gcc Default Optimizations

6. BKI Backend Interface

6.1. BKI File Format

6.2. BKI Commands

6.3. Example

7. Page Files

8. Genetic Query Optimization

8.1. Query Handling as a Complex Optimization Problem

8.2. Genetic Algorithms

8.3. Genetic Query Optimization (GEQO) in PostgreSQL

8.4. Further Readings

9. GiST Indexes

10. Native Language Support

A. The CVS Repository

A.1. Getting The Source Via Anonymous CVS

A.2. CVS Tree Organization

A.3. Getting The Source Via CVSup

B. Documentation

B.1. DocBook

B.2. Tool Sets

B.3. Building The Documentation

B.4. Documentation Authoring

B.5. Style Guide

Index

© 2002-2003 Active-Venture.com Small business hosting

Disclaimer: This documentation is provided only for the benefits of our hosting customers.
For authoritative source of the documentation, please refer to http://www.postgresql.org/docs/

Other Tutorial Sites: Red Hat Linux: ver 7.1, ver 7.2, ver 7.3, ver 8 & ver 9, Internet RFCs, RUBY, Smarty, Unix Help, About CPU, Site Marketing, Schizophrenia, Surfers Guide, Website Shop