Poised Solutions Tech Library

PostgreSQL

PostgreSQL Author: Kerry Douglas Susan Douglas Publisher: Developer's Library Published: 2003 Pages: 790

PostgreSQL is quite simply the best book on PostgreSQL, after reading PostgreSQL you will have PostgreSQL explained fully to you. PostgreSQL the book explains all the ins and outs of the PostgreSQL database. And if you are a PostgreSQL Developer you will want PostgreSQL in easy reach as a reference for when you explore some of the most dusty corners of the PostgreSQL Database.

PostgreSQL also teaches SQL (Structured Query Language) well, so if you are a SQL Developer perhaps chained to another database, you will still find very useful that does translate to other SQL database systems.

PostgreSQL is an enjoyable read, the writing style is clear and a lot of the content is thought provoking as well as informative. The pace of PostgreSQL is just right, easing the reader into the PostgreSQL database, and then adding the extras as the book progresses. PostgreSQL acts both as reference and tutorial book, which is always a good thing to say about a book, and it is a book that does answer some of the more esoteric questions about the PostgreSQL database.

PostgreSQL Chapters

PostgreSQL Chapters

1. Introduction to PostgreSQL and SQL
   • A Sample Database
   • Basic Database Terminology
   • Prerequisites
     • Installing PostgreSQL using an RPM
   • Connecting to 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
     • 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
   • Create New Tables Using CREATE TABLE ... AS
   • Using VIEW
   • Summary
2. Working with Data in PostgreSQL
   • NULL Values
   • Character Values
     • Syntax for Literal Values
     • Support 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
     • Size 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
   • Summary
3. PostgreSQL SQL Syntax and Use
   • PostgreSQL Naming Rules
   • Creating, Destroying and Viewing Databases
     • 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
   • 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)
   • Table Statistics
   • Performance Tips
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
   • 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
   • 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
   • 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
   • 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()
     • Binary Cursors
   • 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 PostgreSQL C++ API - libpq++
    • Prerequisites
    • Client 1 - Connecting to the Server
    • Client 2 - Adding Error Checking
    • Client 3 - Processing Queries
      • Working with Transactions
      • Working with Cursors
      • Working with Large-Objects
    • Client 4 - An Interactive Query Processor
    • 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
    • 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
    • Client 3 - Processing Queries
      • Statement
      • 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 libpgctl 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. Introduction to PostgreSQL Administration
    • Security
    • User Accounts
    • Backup and Restore
    • Server Startup and Shutdown
    • tuning
    • Installing Updates
    • Localization
    • Summary
19. 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
    • Managing User Accounts
      • CREATE USER
      • Managing Groups
    • Configuring Your PostgreSQL Runtime
    • Environment
      • Connection-Related Parameters
      • Operational Parameters
      • Optimizer Parameters
      • Debugging / Logging Parameters
      • Performance Statistics
      • Miscellaneous Parameters
    • Arranging for PostgreSQL Startup and Shutdown
      • Using pg_ctl
      • Shutdown Modes
      • Configuring PostgreSQL Startup on Unix / Linux
      • Hosts
      • Configuring PostgreSQL as a Windows
      • Service
    • Backing Up and Copying Databases
      • Using pg_dump
      • Using pg_dumpall
      • using pg_restore
    • Summary
20. Internationalization and Localization
    • Locale Support
      • Enabling Locale Support
      • Effects of Locale Support
    • Multibyte Character Sets
      • Encodings Supported by PostgreSQL
      • Enabling Multibyte Support
      • Selecting an Encoding
      • Client / Server Translation
    • Summary
21. Security
    • Securing the PostgreSQL Data Files
    • Securing Network Access
      • local Connections
      • host and hostssl 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
    • Summary

PostgreSQL Appendices

1. Index