PostgreSQL

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.

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.