PostgreSQL

Introduction.

PostgreSQL Features

What Versions Does This Book Cover?

Who Is This Book For?

What Topics Does This Book Cover?

What's New in the Second Edition?

I. GENERAL POSTGRESQL USE.

1. Introduction to PostgreSQL and SQL.

A Sample Database

Basic Database Terminology

Prerequisites

Installing PostgreSQL Using an RPM

Connecting to a Database

A (Very) Simple Query

Creating Tables

Viewing Table Descriptions

Adding New Records to a Table

Using the INSERT Command

Using the COPY Command

Installing the Sample Database

Retrieving Data from the Sample Database

SELECT Expression

SELECT FROM Table

SELECT Single-Column FROM Table

SELECT Column-List FROM Table

SELECT Expression-List FROM Table

Selecting Specific Rows

The CASE Expression

Formatting Column Results

Matching Patterns

Aggregates

COUNT()

SUM()

AVG()

MIN() and MAX()

Other Aggregate Functions

Grouping Results

Multi-Table Joins

Join Types

UPDATE

DELETE

A (Very) Short Introduction to Transaction Processing

Creating New Tables Using CREATE TABLE...AS

Using VIEW

Summary

2. Working with Data in PostgreSQL.

NULL Values

Character Values

Syntax for Literal Values

Supported Operators

Numeric Values

Size, Precision, and Range-of-Values

Syntax for Literal Values

Supported Operators

Date/Time Values

Syntax for Literal Values

Supported Operators

Boolean (Logical) Values

Size and Valid Values

Syntax for Literal Values

Supported Operators

Geometric Data Types

Syntax for Literal Values

Sizes and Valid Values

Supported Operators

Object IDs (OID)

Syntax for Literal Values

Size and Valid Values

Supported Operators

BLOBs

Syntax for Literal Values

Supported Operators

Large-Objects

Network Address Data Types

MACADDR

CIDR

INET

Syntax for Literal Values

Supported Operators

Sequences

Arrays

Column Constraints

NULL/NOT NULL

UNIQUE

PRIMARY KEY

REFERENCES

CHECK()

Expression Evaluation and Type Conversion

Creating Your Own Data Types

Refining Data Types with CREATE DOMAIN

Creating and Using Composite Types

Summary

3. PostgreSQL SQL Syntax and Use.

PostgreSQL Naming Rules

The Importance of the COMMENT Command

Creating, Destroying, and Viewing Databases

Tablespaces

Creating New Databases

Dropping a Database

Viewing Databases

Creating New Tables

Temporary Tables

Table Constraints

Dropping Tables

Inheritance

ALTER TABLE

Adding Indexes to a Table

Tradeoffs

Creating an Index

Functional Indexes and Partial Indexes

Creating Indexes on Array Values

Indexes and Tablespaces

Getting Information About Databases and Tables

Transaction Processing

Persistence

Transaction Isolation

Multi-Versioning and Locking

Summary

4. Performance.

How PostgreSQL Organizes Data

Page Caching

Summary

Gathering Performance Information

Dead Tuples

Index Performance

Understanding How PostgreSQL Executes a Query

EXPLAIN

Seq Scan

Index Scan

Sort

Unique

LIMIT

Aggregate

Append

Result

Nested Loop

Merge Join

Hash and Hash Join

Group

Subquery Scan and Subplan

Tid Scan

Materialize

Setop (Intersect, Intersect All, Except, Except All)

Execution Plans Generated by the Planner

The ARC Buffer Manager

Table Statistics

Performance Tips

II. PROGRAMMING WITH POSTGRESQL.

5. Introduction to PostgreSQL Programming.

Server-Side Programming

PL/pgSQL

Other Procedural Languages Supported by PostgreSQL

Extending PostgreSQL Using External Languages

Client-Side APIs

General Structure of Client Applications

Connection Properties

LISTEN/NOTIFY

Choosing an Application Environment

Server-Side Code

Client-Side Code

Mixing Server-Side and Client-Side Code

Summary

6. Extending PostgreSQL.

Extending the PostgreSQL Server with Custom Functions

Returning Multiple Values from an Extension Function

The PostgreSQL SRF Interface

Returning Complete Rows from an Extension Function

Extending the PostgreSQL Server with Custom Data Types

Internal and External Forms

Defining a Simple Data Type in PostgreSQL

Defining the Data Type in C

Defining the Input and Output Functions in C

Defining the Input and Output Functions in PostgreSQL

Defining the Data Type in PostgreSQL

Indexing Custom Data Types

Summary

7. PL/pgSQL.

Installing PL/pgSQL

Language Structure

Quoting Embedded Strings

CREATE FUNCTION

DROP FUNCTION

Function Body

Comments

Variables

PL/pgSQL Statement Types

Cursors

FETCH

Parameterized Cursors

Cursor References

Triggers

TRIGGER Return Values

TRIGGER Function Arguments

Polymorphic Functions

PL/pgSQL and Security

Summary

8. The PostgreSQL C API-libpq.

Prerequisites

Client 1-Connecting to the Server

Compiling the Client

Identifying the Server

Client 2-Adding Error Checking

Viewing Connection Attributes

Client 3-Simple Processing-PQexec() and PQprint()

Results Returned by PQexec()

The Prepare/Execute Model

Client 4-An Interactive Query Processor

Processing Multiple Result Sets

Asynchronous Processing

Summary

9. A Simpler C API-libpgeasy.

Prerequisites

Client 1-Connecting to the Server

Client 2-Adding Error Checking

Client 3-Processing Queries

Working with Binary Cursors

Byte Ordering and NULL Values

Client 4-An Interactive Query Processor

Summary

10. The New PostgreSQL C++ API-libpqxx.

Prerequisites

Client 1-Connecting to the Server

Using pqxx-config to Create a Simple Makefile

connection Member Functions

Client 2-Adding Error Checking

Other Exceptions Thrown by libpqxx

Handling Informational/Warning Messages with Notice Processor Objects

Client 3-Processing Queries

Working with Transactions

Working with Result Sets

Working with Large-Objects

LISTEN/NOTIFY

set_variable() and get_variable()

Client 4-Working with transactors

Summary

11. Embedding SQL Commands in C Programs-ecpg.

Prerequisites

Client 1-Connecting to the Server

The ecpg Preprocessor

Connection Strings

Client 2-Adding Error Checking

The sqlca Structure

Client 3-Processing SQL Commands

ecpg Data Types

Client 4-An Interactive Query Processor

Summary

12. Using PostgreSQL from an ODBC Client Application.

ODBC Architecture Overview

The ODBC Client Application

The ODBC Driver Manager

The ODBC Driver

The ODBC-Compliant Database

The Data Source

Setting Up a Data Source on Unix Systems

Setting Up a Data Source in Windows

Datasource Connection Properties

Prerequisites

Client 1-Connecting to the Server

Client 2-Adding Error Checking

Client 3-Processing Queries

Client 4-An Interactive Query Processor

Summary

Resources

13. Using PostgreSQL from a Java Client Application.

JDBC Architecture Overview

The JDBC DriverManager

The JDBC Driver

The JDBC-Compliant Database

Prerequisites

Client 1-Connecting to the Server

JDBC URLs

Client 2-Adding Error Checking

JNDI and the DataSource Class

Client 3-Processing Queries

Statement Classes

Metadata

Client 4-An Interactive Query Processor

Summary

14. Using PostgreSQL with Perl.

DBI Architecture Overview

The DBI

The DBD Driver

The DBI-Compliant Database

Prerequisites

Client 1-Connecting to the Server

DBI URLs

Client 2-Adding Error Checking

Client 3-Processing Queries

The Prepare/Execute Model

Metadata and Result Set Processing

Other Statement and Database Handle Attributes

Client 4-An Interactive Query Processor

Summary

15. Using PostgreSQL with PHP.

PHP Architecture Overview

Prerequisites

Client 1-Connecting to the Server

Client 2-Adding Error Checking

Client 3-Query Processing

Other Ways to Retrieve Result Set Values

Metadata Access

Client 4-An Interactive Query Processor

Other Features

Summary

16. Using PostgreSQL with Tcl and Tcl/Tk.

Prerequisites

Client 1-Connecting to the Server

Making the Connection Dialog Reusable

Client 2-Query Processing

Result Set Processing

Client 3-An Interactive Query Processor

The libpgtcl Large-Object API

Summary

17. Using PostgreSQL with Python.

Python/PostgreSQL Interface Architecture

Prerequisites

Client 1-Connecting to the Server

Client 2-Adding Error Checking

Client 3-Query Processing

Client 4-An Interactive Command Processor

Summary

18. Npgsql: The .NET Data Provider.

Prerequisites

Preparing Visual Studio

Understanding the ADO.NET Class Hierarchy

Creating an Npgsql-enabled VB Project

Client 1-Connecting to the Server

Client 2-An Interactive Query Processor

Client 3-Updating the Database with a DataSet

Client 4-A More Robust Query Processor

Client 5-Using a Typed DataSet

Creating a Typed DataSet

Summary

19. Other Useful Programming Tools.

PL/Java-Writing Stored Procedures in Java

Installing PL/Java

Writing a Simple PL/Java Function

Accessing the Database from a PL/Java Function

Returning Multiple Results from a PL/Java Function

Writing PL/Java Trigger Functions

Adding Install/Uninstall Commands to a Jar File

pgcurl-Web-enabling Your PostgreSQL Server

pgbash-Writing PostgreSQL-enabled Shell Scripts

III. POSTGRESQL ADMINISTRATION.

20. Introduction to PostgreSQL Administration.

Security

User Accounts

Backup and Restore

Server Startup and Shutdown

Running PostgreSQL on a Windows Host

Tuning

Installing Updates

Localization

Summary

21. PostgreSQL Administration.

Roadmap (Where's All My Stuff?)

Installing PostgreSQL

Unix/Linux

Windows

Managing Databases

Creating a New Cluster

Creating a New Database

Routine Maintenance

Logfile Rotation

The PostgreSQL BGWRITER Process

Managing User Accounts

CREATE USER

Managing Groups

Configuring Your PostgreSQL Runtime Environment

File Locations

Security-Related Parameters

Connection-Related Parameters

Operational Parameters

Write-Ahead Log Parameters

Optimizer Parameters

Debugging/Logging Parameters

Performance Statistics

Per-session Parameters

Miscellaneous Parameters

Read-only Parameters

Arranging for PostgreSQL Startup and Shutdown

Using pg_ctl

Shutdown Modes

Configuring PostgreSQL Startup on Unix/Linux Hosts

Backing Up and Copying Databases

Using pg_dump

Using pg_dumpall

Using pg_restore

Point-in-time Recovery

Summary

22. Internationalization and Localization.

Locale Support

Enabling Locale Support

Effects of Locale Support

PostgreSQL Locale Summary

Multi-Byte Character Sets

Encodings Supported by PostgreSQL

Enabling Multi-Byte Support

Selecting an Encoding

Client/Server Translation

Summary

23. Security.

Securing the PostgreSQL Data Files

Securing PostgreSQL Data Files in Windows

Securing Network Access

local Connections

host, hostssl, and hostnossl Connections

The trust Authentication Method

The ident Authentication Method

The password Authentication Method

The crypt Authentication Method

The md5 Authentication Method

The pam Authentication Method

The krb4 and krb5 Authentication Methods

The reject Authentication Method

Securing Tables

Securing Functions

Summary

24. Replicating PostgreSQLData with Slony.

Overview

Requirements

Creating a Replication Cluster

Starting the Replication Daemons

Creating a Replication Set

Subscribing to a Replication Set

Copying Table and Sequence Definitions

Creating a Subscriber

Changing the Cluster Topology (Re-mastering and Failover)

Summary

25. Contributed Modules.

Exchanging PostgreSQL Data with XML

XPath Queries

Converting XML Data with XSLT

Using Full-text Search

Searching Multiple Columns

Simplifying tsearch2 with Customized Functions

Searching for Phrases

Configuring tsearch2

Index.