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.

Table of Contents:
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.