PostgreSQL, Second Edition

PostgreSQL, Second Edition

by Korry Douglas, Susan Douglas
Released July 2005
Publisher(s): Sams
ISBN: 0672327562

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

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

  1. Copyright
  2. The Real Value in Free Software
  3. About the Authors
  4. Acknowledgments
  5. We Want to Hear from You!
  6. Reader Services
  7. Introduction
  8. General PostgreSQL Use
    1. Introduction to PostgreSQL and SQL
      1. A Sample Database
      2. Basic Database Terminology
      3. Prerequisites
      4. Connecting to a Database
      5. Creating Tables
      6. Viewing Table Descriptions
      7. Adding New Records to a Table
      8. Installing the Sample Database
      9. Retrieving Data from the Sample Database
      10. The CASE Expression
      11. Aggregates
      12. Multi-Table Joins
      13. UPDATE
      14. DELETE
      15. A (Very) Short Introduction to Transaction Processing
      16. Creating New Tables Using CREATE TABLE...AS
      17. Using VIEW
      18. Summary
    2. Working with Data in PostgreSQL
      1. NULL Values
      2. Character Values
      3. Numeric Values
      4. Date/Time Values
      5. Boolean (Logical) Values
      6. Geometric Data Types
      7. Object IDs (OID)
      8. BLOBs
      9. Network Address Data Types
      10. Sequences
      11. Arrays
      12. Column Constraints
      13. Expression Evaluation and Type Conversion
      14. Creating Your Own Data Types
      15. Summary
    3. PostgreSQL SQL Syntax and Use
      1. PostgreSQL Naming Rules
      2. Creating, Destroying, and Viewing Databases
      3. Creating New Tables
      4. Adding Indexes to a Table
      5. Getting Information About Databases and Tables
      6. Transaction Processing
      7. Summary
    4. Performance
      1. How PostgreSQL Organizes Data
      2. Gathering Performance Information
      3. Understanding How PostgreSQL Executes a Query
      4. Execution Plans Generated by the Planner
      5. The ARC Buffer Manager
      6. Table Statistics
      7. Performance Tips
  9. Programming with PostgreSQL
    1. Introduction to PostgreSQL Programming
      1. Server-Side Programming
      2. Client-Side APIs
      3. General Structure of Client Applications
      4. Choosing an Application Environment
      5. Summary
    2. Extending PostgreSQL
      1. Extending the PostgreSQL Server with Custom Functions
      2. Returning Multiple Values from an Extension Function
      3. The PostgreSQL SRF Interface
      4. Returning Complete Rows from an Extension Function
      5. Extending the PostgreSQL Server with Custom Data Types
      6. Internal and External Forms
      7. Defining a Simple Data Type in PostgreSQL
      8. Defining the Data Type in C
      9. Defining the Input and Output Functions in C
      10. Defining the Input and Output Functions in PostgreSQL
      11. Defining the Data Type in PostgreSQL
      12. Indexing Custom Data Types
      13. Summary
    3. PL/pgSQL
      1. Installing PL/pgSQL
      2. Language Structure
      3. Function Body
      4. Cursors
      5. Triggers
      6. Polymorphic Functions
      7. PL/pgSQL and Security
      8. Summary
    4. The PostgreSQL C API—libpq
      1. Prerequisites
      2. Client 1—Connecting to the Server
      3. Client 2—Adding Error Checking
      4. Client 3—Simple Processing—PQexec() and PQprint()
      5. Client 4—An Interactive Query Processor
      6. Summary
    5. A Simpler C API—libpgeasy
      1. Prerequisites
      2. Client 1—Connecting to the Server
      3. Client 2—Adding Error Checking
      4. Client 3—Processing Queries
      5. Client 4—An Interactive Query Processor
      6. Summary
    6. The New PostgreSQL C++ API—libpqxx
      1. Prerequisites
      2. Client 1—Connecting to the Server
      3. Client 2—Adding Error Checking
      4. Client 3—Processing Queries
      5. Client 4—Working with transactors
      6. Summary
    7. Embedding SQL Commands in C Programs—ecpg
      1. Prerequisites
      2. Client 1—Connecting to the Server
      3. Client 2—Adding Error Checking
      4. Client 3—Processing SQL Commands
      5. Client 4—An Interactive Query Processor
      6. Summary
    8. Using PostgreSQL from an ODBC Client Application
      1. ODBC Architecture Overview
      2. Prerequisites
      3. Client 1—Connecting to the Server
      4. Client 2—Adding Error Checking
      5. Client 3—Processing Queries
      6. Client 4—An Interactive Query Processor
      7. Summary
      8. Resources
    9. Using PostgreSQL from a Java Client Application
      1. JDBC Architecture Overview
      2. Prerequisites
      3. Client 1—Connecting to the Server
      4. Client 2—Adding Error Checking
      5. Client 3—Processing Queries
      6. Client 4—An Interactive Query Processor
      7. Summary
    10. Using PostgreSQL with Perl
      1. DBI Architecture Overview
      2. Prerequisites
      3. Client 1—Connecting to the Server
      4. Client 2—Adding Error Checking
      5. Client 3—Processing Queries
      6. Client 4—An Interactive Query Processor
      7. Summary
    11. Using PostgreSQL with PHP
      1. PHP Architecture Overview
      2. Prerequisites
      3. Client 1—Connecting to the Server
      4. Client 2—Adding Error Checking
      5. Client 3—Query Processing
      6. Client 4—An Interactive Query Processor
      7. Other Features
      8. Summary
    12. Using PostgreSQL with Tcl and Tcl/Tk
      1. Prerequisites
      2. Client 1—Connecting to the Server
      3. Client 2—Query Processing
      4. Client 3—An Interactive Query Processor
      5. The libpgtcl Large-Object API
      6. Summary
    13. Using PostgreSQL with Python
      1. Python/PostgreSQL Interface Architecture
      2. Prerequisites
      3. Client 1—Connecting to the Server
      4. Client 2—Adding Error Checking
      5. Client 3—Query Processing
      6. Client 4—An Interactive Command Processor
      7. Summary
    14. Npgsql: The .NET Data Provider
      1. Prerequisites
      2. Preparing Visual Studio
      3. Understanding the ADO.NET Class Hierarchy
      4. Creating an Npgsql-enabled VB Project
      5. Client 1—Connecting to the Server
      6. Client 2—An Interactive Query Processor
      7. Client 3—Updating the Database with a DataSet
      8. Client 4—A More Robust Query Processor
      9. Client 5—Using a Typed DataSet
      10. Summary
    15. Other Useful Programming Tools
      1. PL/Java—Writing Stored Procedures in Java
      2. pgcurl—Web-enabling Your PostgreSQL Server
      3. pgbash—Writing PostgreSQL-enabled Shell Scripts
  10. PostgreSQL Administration
    1. Introduction to PostgreSQL Administration
      1. Security
      2. User Accounts
      3. Backup and Restore
      4. Server Startup and Shutdown
      5. Running PostgreSQL on a Windows Host
      6. Tuning
      7. Installing Updates
      8. Localization
      9. Summary
    2. PostgreSQL Administration
      1. Roadmap (Where's All My Stuff?)
      2. Installing PostgreSQL
      3. Managing Databases
      4. The PostgreSQL BGWRITER Process
      5. Managing User Accounts
      6. Configuring Your PostgreSQL Runtime Environment
      7. Arranging for PostgreSQL Startup and Shutdown
      8. Backing Up and Copying Databases
      9. Point-in-time Recovery
      10. Summary
    3. Internationalization and Localization
      1. Locale Support
      2. Multi-Byte Character Sets
      3. Summary
    4. Security
      1. Securing the PostgreSQL Data Files
      2. Securing Network Access
      3. Securing Tables
      4. Securing Functions
      5. Summary
    5. Replicating PostgreSQL Data with Slony
      1. Overview
      2. Requirements
      3. Creating a Replication Cluster
      4. Starting the Replication Daemons
      5. Creating a Replication Set
      6. Subscribing to a Replication Set
      7. Changing the Cluster Topology (Re-mastering and Failover)
      8. Summary
    6. Contributed Modules
      1. Exchanging PostgreSQL Data with XML
      2. Using Full-text Search

Product information

  • Title: PostgreSQL, Second Edition
  • Author(s): Korry Douglas, Susan Douglas
  • Release date: July 2005
  • Publisher(s): Sams
  • ISBN: 0672327562