The second edition of the best-selling PostgreSQL has been updated to completely cover new features and capabilities of the 8.0 version of PostgreSQL. You will be lead through the internals of the powerful PostgreSQL open source database chapter, offering an easy-to-read, code-based approach that makes it easy to understand how each feature is implemented, how to best use each feature, and how to get more performance from database applications. This definitive guide to building, programming and administering the ...

See more details below
Paperback (REV)
$42.50 price
(Save 22%)$54.99 List Price
Other sellers (Paperback)
  • All (13) from $2.81   
  • New (6) from $31.21   
  • Used (7) from $2.77   
Sending request ...


The second edition of the best-selling PostgreSQL has been updated to completely cover new features and capabilities of the 8.0 version of PostgreSQL. You will be lead through the internals of the powerful PostgreSQL open source database chapter, offering an easy-to-read, code-based approach that makes it easy to understand how each feature is implemented, how to best use each feature, and how to get more performance from database applications. This definitive guide to building, programming and administering the powerful PostgreSQL open-source database system will help you harness one of the most widely used open source, enterprise-level database systems.

Read More Show Less

Product Details

  • ISBN-13: 9780672327568
  • Publisher: Sams
  • Publication date: 7/19/2005
  • Series: Developer's Library
  • Edition description: REV
  • Edition number: 2
  • Pages: 1032
  • Product dimensions: 7.02 (w) x 8.94 (h) x 2.14 (d)

Meet the Author

Korry Douglas is the Director of Research and Development for Appx Software. Over the last two decades he has worked on the design and implementation of an umber of high-level languages and development environments, and his work with a variety of database products (Oracle, Sybase, SQL Server, PostgreSQL, MySQL and mSQL) given him a unique understanding of the commonalities of, and differences between, databases.
Read More Show Less

Table of Contents


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?


1. Introduction to PostgreSQL and SQL.

A Sample Database

Basic Database Terminology


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





MIN() and MAX()

Other Aggregate Functions

Grouping Results

Multi-Table Joins

Join Types



A (Very) Short Introduction to Transaction Processing

Creating New Tables Using CREATE TABLE...AS

Using VIEW


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


Syntax for Literal Values

Supported Operators


Network Address Data Types




Syntax for Literal Values

Supported Operators



Column Constraints






Expression Evaluation and Type Conversion

Creating Your Own Data Types

Refining Data Types with CREATE DOMAIN

Creating and Using Composite Types


3. PostgreSQL SQL Syntax and Use.

PostgreSQL Naming Rules

The Importance of the COMMENT Command

Creating, Destroying, and Viewing Databases


Creating New Databases

Dropping a Database

Viewing Databases

Creating New Tables

Temporary Tables

Table Constraints

Dropping Tables



Adding Indexes to a Table


Creating an Index

Functional Indexes and Partial Indexes

Creating Indexes on Array Values

Indexes and Tablespaces

Getting Information About Databases and Tables

Transaction Processing


Transaction Isolation

Multi-Versioning and Locking


4. Performance.

How PostgreSQL Organizes Data

Page Caching


Gathering Performance Information

Dead Tuples

Index Performance

Understanding How PostgreSQL Executes a Query


Seq Scan

Index Scan







Nested Loop

Merge Join

Hash and Hash Join


Subquery Scan and Subplan

Tid Scan


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

Execution Plans Generated by the Planner

The ARC Buffer Manager

Table Statistics

Performance Tips


5. Introduction to PostgreSQL Programming.

Server-Side Programming


Other Procedural Languages Supported by PostgreSQL

Extending PostgreSQL Using External Languages

Client-Side APIs

General Structure of Client Applications

Connection Properties


Choosing an Application Environment

Server-Side Code

Client-Side Code

Mixing Server-Side and Client-Side Code


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


7. PL/pgSQL.

Installing PL/pgSQL

Language Structure

Quoting Embedded Strings



Function Body



PL/pgSQL Statement Types



Parameterized Cursors

Cursor References


TRIGGER Return Values

TRIGGER Function Arguments

Polymorphic Functions

PL/pgSQL and Security


8. The PostgreSQL C API-libpq.


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


9. A Simpler C API-libpgeasy.


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


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


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


set_variable() and get_variable()

Client 4-Working with transactors


11. Embedding SQL Commands in C Programs-ecpg.


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


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


Client 1-Connecting to the Server

Client 2-Adding Error Checking

Client 3-Processing Queries

Client 4-An Interactive Query Processor



13. Using PostgreSQL from a Java Client Application.

JDBC Architecture Overview

The JDBC DriverManager

The JDBC Driver

The JDBC-Compliant Database


Client 1-Connecting to the Server


Client 2-Adding Error Checking

JNDI and the DataSource Class

Client 3-Processing Queries

Statement Classes


Client 4-An Interactive Query Processor


14. Using PostgreSQL with Perl.

DBI Architecture Overview


The DBD Driver

The DBI-Compliant Database


Client 1-Connecting to the Server


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


15. Using PostgreSQL with PHP.

PHP Architecture Overview


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


16. Using PostgreSQL with Tcl and Tcl/Tk.


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


17. Using PostgreSQL with Python.

Python/PostgreSQL Interface Architecture


Client 1-Connecting to the Server

Client 2-Adding Error Checking

Client 3-Query Processing

Client 4-An Interactive Command Processor


18. Npgsql: The .NET Data Provider.


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


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


20. Introduction to PostgreSQL Administration.


User Accounts

Backup and Restore

Server Startup and Shutdown

Running PostgreSQL on a Windows Host


Installing Updates



21. PostgreSQL Administration.

Roadmap (Where's All My Stuff?)

Installing PostgreSQL



Managing Databases

Creating a New Cluster

Creating a New Database

Routine Maintenance

Logfile Rotation

The PostgreSQL BGWRITER Process

Managing User Accounts


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


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


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


24. Replicating PostgreSQLData with Slony.



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)


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


Read More Show Less



PostgreSQL is a relational database with a long history. In the late 1970s, the University of California at Berkeley began development of PostgreSQL's ancestor—a relational database known as Ingres. Relational Technologies turned Ingres into a commercial product. Relational Technologies became Ingres Corporation and was later acquired by Computer Associates. Around 1986, Michael Stonebraker from UC Berkeley led a team that added object-oriented features to the core of Ingres; the new version became known as Postgres. Postgres was again commercialized; this time by a company named Illustra, which became part of the Informix Corporation. Andrew Yu and Jolly Chen added SQL support to Postgres in the mid-'90s. Prior versions had used a different, Postgres-specific query language known as Postquel. In 1996, many new features were added, including the MVCC transaction model, more adherence to the SQL92 standard, and many performance improvements. Postgres once again took on a new name: PostgreSQL.

Today, PostgreSQL is developed by an international group of open-source software proponents known as the PostgreSQL Global Development group. PostgreSQL is an open-source product—it is not proprietary in any way. Red Hat has recently commercialized PostgreSQL, creating the Red Hat Database, but PostgreSQL itself will remain free and open source.

PostgreSQL Features

PostgreSQL has benefited well from its long history. Today, PostgreSQL is one of the most advanced database servers available. Here are a few of the features found in a standard PostgreSQL distribution:

  • Object-relational—In PostgreSQL, every table defines a class. PostgreSQL implements inheritance between tables (or, if you like, between classes). Functions and operators are polymorphic.
  • Standards compliant—PostgreSQL syntax implements most of the SQL92 standard and many features of SQL99. Where differences in syntax occur, they are most often related to features unique to PostgreSQL.
  • Open source—An international team of developers maintains PostgreSQL. Team members come and go, but the core members have been enhancing PostgreSQL's performance and feature set since at least 1996. One advantage to PostgreSQL's open-source nature is that talent and knowledge can be recruited as needed. The fact that this team is international ensures that PostgreSQL is a product that can be used productively in any natural language, not just English.
  • Transaction processing—PostgreSQL protects data and coordinates multiple concurrent users through full transaction processing. The transaction model used by PostgreSQL is based on multi-version concurrency control (MVCC). MVCC provides much better performance than you would find with other products that coordinate multiple users through table-, page-, or row-level locking.
  • Referential integrity—PostgreSQL implements complete referential integrity by supporting foreign and primary key relationships as well as triggers. Business rules can be expressed within the database rather than relying on an external tool.
  • Multiple procedural languages—Triggers and other procedures can be written in any of several procedural languages. Server-side code is most commonly written in PL/pgSQL, a procedural language similar to Oracle's PL/SQL. You can also develop server-side code in Tcl, Perl, even bash (the open-source Linux/Unix shell).
  • Multiple-client APIs—PostgreSQL supports the development of client applications in many languages. This book describes how to interface to PostgreSQL from C, C++, ODBC, Perl, PHP, Tcl/Tk, and Python.
  • Unique data types—PostgreSQL provides a variety of data types. Besides the usual numeric, string, and data types, you will also find geometric types, a Boolean data type, and data types designed specifically to deal with network addresses.
  • Extensibility—One of the most important features of PostgreSQL is that it can be extended. If you don't find something that you need, you can usually add it yourself. For example, you can add new data types, new functions and operators, and even new procedural and client languages. There are many contributed packages available on the Internet. For example, Refractions Research, Inc. has developed a set of geographic data types that can be used to efficiently model spatial (GIS) data.

What Versions Does This Book Cover?

The first edition of this book covered versions 7.1 through 7.3. In this edition, we've updated the basics and added coverage for the new features introduced in versions 7.4 and 8.0. Throughout the book, I'll be sure to let you know which features work only in new releases, and, in a few cases, I'll explain features that have been deprecated (that is, features that are obsolete). You can use this book to install, configure, tune, program, and manage PostgreSQL versions 7.1 through 8.0.

Fortunately, the PostgreSQL developers try very hard to maintain forward compatibility—new features tend not to break existing applications. This means that all the features discussed in this book should still be available and substantially similar in later versions of PostgreSQL. I have tried to avoid talking about features that have not been released at the time of writing—where I have mentioned future developments, I will point them out.

Who Is This Book For?

If you are already using PostgreSQL, you should find this book a useful guide to some of the features that you might be less familiar with. The first part of the book provides an introduction to SQL and PostgreSQL for the new user. You'll also find information that shows how to obtain and install PostgreSQL on a Unix/Linux host, as well as on Microsoft Windows.

If you are developing an application that will store data in PostgreSQL, the second part of this book will provide you with a great deal of information relating to PostgreSQL programming. You'll find information on both server-side and client-side programming in a variety of languages.

Every database needs occasional administrative work. The final part of the book should be of help if you are a PostgreSQL administrator, or a developer or user that needs to do occasional administration. You will also find information on how to secure your data against inappropriate use.

Finally, if you are trying to decide which database to use for your current project (or for future projects), this book should provide all the information you need to evaluate whether PostgreSQL will fit your needs.

What Topics Does This Book Cover?

PostgreSQL is a huge product. It's not easy to find the right mix of topics when you are trying to fit everything into a single book. This book is divided into three parts.

The first part, "General PostgreSQL Use," is an introduction and user's guide for PostgreSQL. Chapter 1, "Introduction to PostgreSQL and SQL," covers the basics—how to obtain and install PostgreSQL (if you are running Linux, chances are you already have PostgreSQL and it may be installed). The first chapter also provides a gentle introduction to SQL and discusses the sample database we'll be using throughout the book. Chapter 2, "Working with Data in PostgreSQL," describes the many data types supported by a standard PostgreSQL distribution; you'll learn how to enter values (literals) for each data type, what kind of data you can store with each type, and how those data types are combined into expressions. Chapter 3, "PostgreSQL SQL Syntax and Use," fills in some of the details we glossed over in the first two chapters. You'll learn how to create new databases, new tables and indexes, and how PostgreSQL keeps your data safe through the use of transactions. Chapter 4, "Performance," describes the PostgreSQL optimizer. I'll show you how to get information about the decisions made by the optimizer, how to decipher that information, and how to influence those decisions.

Part II, "Programming with PostgreSQL," is all about PostgreSQL programming. In Chapter 5, "Introduction to PostgreSQL Programming," we start off by describing the options you have when developing a database application that works with PostgreSQL (and there are a lot of options). Chapter 6, "Extending PostgreSQL," briefly describes how to extend PostgreSQL by adding new functions, data types, and operators. Chapter 7, "PL/pgSQL," describes the PL/pgSQL language. PL/pgSQL is a server-based procedural language. Code that you write in PL/pgSQL executes within the PostgreSQL server and has very fast access to data. Each chapter in the remainder of the programming section deals with a client-based API. You can connect to a PostgreSQL server using a number of languages. I show you how to interface to PostgreSQL using C, C++, ecpg, ODBC, JDBC, Perl, PHP, Tcl/Tk, Python, and Microsoft's .NET. Chapters 8 through 18 all follow the same pattern: you develop a series of client applications in a given language. The first client application shows you how to establish a connection to the database (and how that connection is represented by the language in question). The next client adds error checking so that you can intercept and react to unusual conditions. The third client in each chapter demonstrates how to process SQL commands from within the client. The final client wraps everything together and shows you how to build an interactive query processor using the language being discussed. Even if you program in only one or two languages, I would encourage you to study the other chapters in this section. I think you'll find that looking at the same application written in a variety of languages will help you understand the philosophy followed by the PostgreSQL development team, and it's a great way to start learning a new language. Chapter 19, "Other Useful Programming Tools," introduces you to a few programming tools (and interfaces) that you might find useful: PL/Java and PL/Perl. I'll also show you how to use PostgreSQL inside of bash shell scripts.

The final part of this book (Part III, "PostgreSQL Administration") deals with administrative issues. The final six chapters of this book show you how to perform the occasional duties required of a PostgreSQL administrator. In the first two chapters, Chapter 20, "Introduction to PostgreSQL Administration," and Chapter 21, "PostgreSQL Administration," you'll learn how to start up, shut down, back up, and restore a server. In Chapter 22, "Internationalization and Localization," you will learn how PostgreSQL supports internationalization and localization. PostgreSQL understands how to store and process a variety of single-byte and multi-byte character sets including Unicode, ASCII, and Japanese, Chinese, Korean, and Taiwan EUC. In Chapter 23, "Security," I'll show you how to secure your data against unauthorized uses (and unauthorized users). In Chapter 24, "Replicating PostgreSQL with Slony," you'll learn how to replicate data with PostgreSQL's Slony replication system. Chapter 25, "Contributed Modules," introduces a few open-source projects that work well with PostgreSQL. I'll show you how to query a PostgreSQL database using XML, how to configure and use TSEARCH2 (a full-text indexing and search system), and how to install and use PgAdmin III, a graphical user interface specifically designed for PostgreSQL.

What's New in the Second Edition?

The first edition of this book hit the shelves in February 2003—at that time, the PostgreSQL developers had just released version 7.3.2. Release 7.4 was unleashed in November 2003. In January 2005, the PostgreSQL developers released version 8.0—a major release full of new features. We timed the second edition of this book to coincide with the release of version 8.0 (the book will appear in bookstores a few months after 8.0 hits the streets). In this edition, we've added coverage for all of the (major) new features in 7.3, 7.4, and 8.0, including

  • Installing, securing, and managing PostgreSQL on Windows hosts
  • Tablespaces
  • Schemas
  • New quoting mechanisms for string values
  • New data types (ANYARRAY, ANYELEMENT, VOID)
  • The standards-conforming INFORMATION_SCHEMA
  • Nested transactions (SAVEPOINT's)
  • The new PostgreSQL buffer manager
  • Auto-vacuum
  • Prepared-statement execution (the PREPARE/EXECUTE model)
  • Set-returning functions
  • Exception handling in PL/pgSQL
  • libpqxx, the new PostgreSQL interface for C++ clients
  • New features in ecpg (the embedded SQL processor for C)
  • New features in the ODBC, JDBC (Java), Perl, Python, PHP, and Tcl/Tk client interfaces
  • npgsql—the PostgreSQL .NET data provider
  • Other useful programming tools (PL/Java, pgpash, pgcurl, etc.)
  • Point-in-time recovery
  • Replication
  • Using PostgreSQL with XML
  • Full-text search

We hope you enjoy this book and find it useful. The PostgreSQL developers have done an incredible job of enhancing what was already a world-class database product. Now dig in.

© Copyright Pearson Education. All rights reserved.

Read More Show Less

Customer Reviews

Be the first to write a review
( 0 )
Rating Distribution

5 Star


4 Star


3 Star


2 Star


1 Star


Your Rating:

Your Name: Create a Pen Name or

Barnes & Review Rules

Our reader reviews allow you to share your comments on titles you liked, or didn't, with others. By submitting an online review, you are representing to Barnes & that all information contained in your review is original and accurate in all respects, and that the submission of such content by you and the posting of such content by Barnes & does not and will not violate the rights of any third party. Please follow the rules below to help ensure that your review can be posted.

Reviews by Our Customers Under the Age of 13

We highly value and respect everyone's opinion concerning the titles we offer. However, we cannot allow persons under the age of 13 to have accounts at or to post customer reviews. Please see our Terms of Use for more details.

What to exclude from your review:

Please do not write about reviews, commentary, or information posted on the product page. If you see any errors in the information on the product page, please send us an email.

Reviews should not contain any of the following:

  • - HTML tags, profanity, obscenities, vulgarities, or comments that defame anyone
  • - Time-sensitive information such as tour dates, signings, lectures, etc.
  • - Single-word reviews. Other people will read your review to discover why you liked or didn't like the title. Be descriptive.
  • - Comments focusing on the author or that may ruin the ending for others
  • - Phone numbers, addresses, URLs
  • - Pricing and availability information or alternative ordering information
  • - Advertisements or commercial solicitation


  • - By submitting a review, you grant to Barnes & and its sublicensees the royalty-free, perpetual, irrevocable right and license to use the review in accordance with the Barnes & Terms of Use.
  • - Barnes & reserves the right not to post any review -- particularly those that do not follow the terms and conditions of these Rules. Barnes & also reserves the right to remove any review at any time without notice.
  • - See Terms of Use for other conditions and disclaimers.
Search for Products You'd Like to Recommend

Recommend other products that relate to your review. Just search for them below and share!

Create a Pen Name

Your Pen Name is your unique identity on It will appear on the reviews you write and other website activities. Your Pen Name cannot be edited, changed or deleted once submitted.

Your Pen Name can be any combination of alphanumeric characters (plus - and _), and must be at least two characters long.

Continue Anonymously

    If you find inappropriate content, please report it to Barnes & Noble
    Why is this product inappropriate?
    Comments (optional)