PostgreSQL Developer's Handbook

PostgreSQL is an object-relational database server that is widely considered to be the world's most advanced open-source database system. It is ANSI-SQL-compatible, and it offers powerful features to enable more complex software design than would be possible with relational databases that are not object-oriented. PostgreSQL is extremely modular, it supports a large number of fatatypes, and programming interfaces for PostgreSQL are available for all important programming languages, including C, Perl, Python, Tcl, Java, and PHP.

PostgreSQL Developer's Handbook provides a complete overview of the PostgreSQL database server and extensive coverage of its core features, including object orientation, PL/(pgSQL, and the most important programming interfaces. The book introduces the reader to the language and syntax of PostgreSQL and then moves quickly into advanced programming topics.

• Part I: PostgreSQL
1. About PostgreSQL
• License
• The Basic Concepts of PostgreSQL
• Software Architecture
• Limitations of PostgreSQL
2. Preparing
• Hardware Requirements
• Software Requirements
• Installing PostgreSQL
• Installing on UNIX systems
• Installing on Windows
• Starting the Server and Creating Databases
• Using a Startup Script
• Using pg_ctl Directly
• Creating Databases
• Login
• User Interface
3. An Introduction to SQL
• Relational Databases and Their Components
• Tables and Keys
• Datatypes
• Indices
• Sequences
• Triggers
• Objects
• Views
• Procedures
• Aggregate Functions and Aggregate Expressions
• Building a Database
• Building Simple Data Structures
• Inserting Data
• Importing Large Datafiles and Using Pipes on UNIX Systems
• Retrieving Data
• Simple Queries
• Performing Simple Calculations
• Joining Tables
• Self-Joins and Alises
• Aggregate Functions
• Subqueries
• Using SELECT and INSERT
• Pattern Matching and Regular Expressions in SQL
• Using SELECT INTO statements
• Formatting the Output and Casting
• Updating and Deleting Data
• Writing UPDATE Queries
• Using DELETE
• Writing Advanced SQL Code
• Masquerading
• Working with Arrays
• Working with BLOBs
• Using Network-Specific Datatypes
• Working with Geo-Data
• Combining Queries
• Building Complex Data Structures and Modeling Techniques
• Creating and Dropping Views
• Data Integrity with Constraints
• The CHECK Constraint
• Using Inheritance
• Modeling Techniques
4. Understanding Transactions
• How the Transaction Code Affects Daily Work
• Rollbacks
• Concurrent Transactions
• Transaction Isolation
• Locking
5. PL/PGSQL
• Why Use PL/PGSQL?
• Enabling a Database with PL/pgSQL
• The Structure of PL/pgSQL
• Writing Functions
• Inserting PL/pgSQL Functions into a Database
• A Simple Example
• Using Typecast Operations
• Simple Flow of Control Structures
• Using Functions in PL/pgSQL
• Function Overloading
• SELECT Statements and PL/pgSQL Functions
• Exception Handling
• Making Functions More Independent from Datatypes
• Writing Triggers
• Automatically Created Variables
• Oracle's PL/SQL and PL/pgSQL — Comparing Two Worlds
• The Main Differences
6. Database Administration
• How to Configure PostgreSQL
• Configuring the Postmaster
• PostgreSQL 7.1 — The pg_options File
• PostgreSQL 7.1 — PostgreSQL.conf
• Adding Users and Managing User Rights
• A General Overview
• Adding and Deleting Users
• Changing User and Group Attributes
• Using GRANT and REVOKE
• Modifying Databases
• A General Overview
• Using ALTER
• System Tables
• pg_attribute
• pg_description
• pg_group
• pg_index and pg_indexes
• pg_tables
• pg_trigger
• Security and Access Restrictions
• User Authentication
7. Backup and Recovery
• Common Errors
• Full Backup Versus Incremental Backup
• Starting Backups Using cron
• Backup Hardware
• Backup Strategies and Tools for PostgreSQL
• Backup Server
• Jukeboxes and Basic tape Operations
• Saving Large Amounts of Data on Multiple Tapes
• Journaling
8. Performance Tuning
• Measuring Performance
• Indexes and Performance
• Using EXPLAIN and Understanding the Optimizer
• Understanding Execution Plans
• VACUUM
• Tuning SQL
• Rewriting Queries
• Influencing the Optimizer
• Caching the Result of Functions
• System Performance
• The Impact of I/O on System Performance and CPUs
• The Impact of Memory on System Performance and Restricted Access to System Resources
• Working with Large Amounts of Data
• Regular Expressions and Performance
9. Programming Interfaces
• C/C++
• An Overview of C/C++
• Connecting
• Simple Selects — An Example
• Binary Cursors and COPY
• Environmental Variables
• A Reference of the C Interface
• Working with Large Objects
• A Reference to the C++ Interface
• Embedded SQL C Preprocessor (ECPG)
• The Basic Concepts of ECPG
• A First Example
• Error Handling
• Connecting
• Simple Queries and Retrieving Data
• A Final Example
• Perl
• Overview
• The Pg Module
• The DBI Interface and DBD for PostgreSQL
• DBI Proxies
• PHP
• A Short History of PHP
• Connecting
• Working with PHP
• Errors and Exception Handling
• Working with BLOBs and File Uploads
• Debugging
• A Final Example
• Java
• A Short History of Java
• JDBC
• Python
• A Short Overview of Python
• Connecting
• The Database Wrapper Class DB
• Tcl/Tk
• The History of Tcl
• Connecting
• Simple Examples
• Using PL/Tcl
• Building a Simple Tcl/Tk Frontend for PostgreSQL
• ODBC
• An Overview of ODBC
• Using ODBC with PostgreSQL
• ODBC and Security
10. Extended PostgeSQL — Software for PostgreSQL
• Contributed Software
• cube — A Self-Defined Datatype
• Working with fulltext Indices
• Working with ISBN and ISSN
• PostgreSQL Large Object Dumper
• Benchmarking with pgbench
• The seg module
• The soundex Code
• Removing Lost Binary Objects from a Database
• pg_controldata
• Administration Tools
• phpPgAdmin
• Part II: Practical PostgreSQL Solutions
11. Importing Data and Working with Binary Data
• Importing ASCII and Binary Data Using COPY
• Working with ASCII and EBCDIC
• An Overview and a Short History of EBCDIC
• An Overview and a Short History of ASCII
• Understanding BCD Coding
• Taking Care of Byte Order
• The ASCII—EBCDIC Table
• Performing Simple EBCDIC—ASCII Conversions
12. Generating Multi-dimensional Results
• Generating Multi-dimensional Results on the Database Level
• Creating Multi-dimensional Arrays on the Application Level
13. Classification of Data — Real World Examples
• Working with SQL
• EFEU — The Perfect Tool for Data Warehousing
• About EFEU
• Using PostgreSQL and Data Matrices
14. Generating Flash with PostgreSQL and PHP
• PHP's Flash Interfaces
• Simple Flash Examples
• Adding Data to Flash
• Working with Geometric Datatypes
• Using Action Script for Interactive Applications
• A Final Example
15. PostgreSQL Regressions Tests
• The Idea Behind Regression Tests
• Running Tests
• Evaluating Regression Tests
• Error Messages
• Local Differences
• Date and Time Differences
• Floating-Point Differences
• Polygon Differences
• Ordering Differences
• "Random" Testing
16. Extending PostgreSQL's Core Features
• Adding Functions and an Overview of Programming Conventions
• Writing Simple SQL Functions
• Writing C Functions
• PostgreSQL's C Datatypes
• Calling Conventions for Functions Written in C
• Rules for Writing C Code
• Adding Datatypes
• Adding Operators
• Adding Aggregates
• Changing PostgreSQL's Rule System
• Using Rules for Changing PostgreSQL's Behavior
• Using Rules in Combination with Views
17. Date Calculations
• SQL and Dates
• SQL, Dates, and Time Zones
• timestamp with time zone
• interval
• date
• time [without time zone]
• Special Values and Abbreviations
• Performing Simple Date and Time Operations with SQL
• Using Perl — The DateCalc Module
• An Overview
• The Current Calendar
• Basic Operations Using DateCalc
• Using DateCalc with PostgreSQL
• Analyzing a Time Sewries
18. Persistent Database Connection with PHP
• Speed Tests
• Dangerous Side Effects
• Testing Persistent Connections
• Open Connections and Backend Process
• Persistent Connections and Transactions
19. Using PostgreSQL and Microsoft Software over the Network
• Connecting to MS Office
• Setting Up the ODBC Driver
• Using Visual Basic to Connect to PostgreSQL
• The First Contact
• A Simple Example
• Using PostgreSQL with Microsoft Office
• An Example Database
• Working with MS Access
• Working with MS Excel
• Working with MS Word
• Using PostgreSQL and MS Office with Huge Amounts of Data
20. Drawing Graphs on UNIX System Using gnuplot and PostgreSQL
• About gnuplot
• Using PostgreSQL and gnuplot
• A Simple LaTex Document
• Geometric Datatypes and gnuplot — A Simple Example

It's not a reference, nor an exhaustive guide. I would skip this one without regretting it.