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 a number of high-level, high-productivity languages and development environments. His products interface with many relational (and non-relational) databases. Working with so many different database products (Oracle, Sybase, SQL Server, DB2, PostgreSQL, MySQL, MSQL) has given him a broad understanding of the commonalities of, and differences between, databases. Susan Douglas is the President and CEO of Conjectrix, Inc., a software company specializing in database technologies and security tools. Consulting to the end-user community has given her widespread database experience and a real appreciation for high-quality programs and flexible tools powerful enough to handle data well and intuitive enough to actually use. Korry and his wife (and best friend) Susan raise horses in rural Virginia. Both are natives of the Pacific Northwest, but prefer the sunshine and open spaces offered by Virginia. They both telecommute, preferring to spend as much time as possible with their 200 or so animal friends (who never complain about buggy code, inelegant design, or poor performance). Susan is an avid equestrienne; Korry gets to clean the barn.

Introduction. PostgreSQL Features. What Versions Does This Book Cover? What Topics Does This Book Cover? I. GENERAL POSTGRESQL USE. 1. Introduction to PostgreSQL and SQL. A Sample Database. Basic Database Terminology. Prerequisites. Connecting to a Database. Creating Tables. Viewing Table Descriptions. Adding New Records to a Table. Installing the Sample Database. Retrieving Data from the Sample Database. Aggregates. Multi-Table Joins. UPDATE. DELETE. A (Very) Short Introduction to Transaction Processing. Creating New Tables Using CREATE TABLE...AS. Using VIEW. 2. Working with Data in PostgreSQL. NULL Values. Character Values. Numeric Values. Date/Time Values. Boolean (Logical) Values. Geometric Data Types. Object IDs (OID). BLOBs. Network Address Data Types. Sequences. Arrays. Column Constraints. Expression Evaluation and Type Conversion. Creating Your Own Data Types. 3. PostgreSQL SQL Syntax and Use. PostgreSQL Naming Rules. Creating, Destroying, and Viewing Databases. Creating New Tables. Adding Indexes to a Table. Getting Information About Databases and Tables. Transaction Processing. 4. Performance. How PostgreSQL Organizes Data. Gathering Performance Information. Understanding How PostgreSQL Executes a Query. Table Statistics. Performance Tips. II. PROGRAMMING WITH POSTGRESQL. 5. Introduction to PostgreSQL Programming. Server-Side Programming. Client-Side APIs. General Structure of Client Applications. Choosing an Application Environment. 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. 7. PL/pgSQL. Installing PL/pgSQL. Language Structure. Function Body. Cursors. Triggers. 8. The PostgreSQL C API-libpq. Prerequisites. Client 1-Connecting to the Server. Client 2-Adding Error Checking. Client 3-Simple Processing-PQexec() and PQprint(). Client 4-An Interactive Query Processor. 9. A Simpler C API-libpgeasy. Prerequisites. Client 1-Connecting to the Server. Client 2-Adding Error Checking. Client 3-Processing Queries. Client 4-An Interactive Query Processor. 10. The PostgreSQL C++ API-libpq++. Prerequisites. Client 1-Connecting to the Server. Client 2-Adding Error Checking. Client 3-Processing Queries. Client 4-An Interactive Query Processor. 11. Embedding SQL Commands in C Programs-ecpg. Prerequisites. Client 1-Connecting to the Server. Client 2-Adding Error Checking. Client 3-Processing SQL Commands. Client 4-An Interactive Query Processor. 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. Resources. 13. Using PostgreSQL from a Java Client Application. JDBC Architecture Overview. Prerequisites. Client 1-Connecting to the Server. Client 2-Adding Error Checking. Client 3-Processing Queries. Client 4-An Interactive Query Processor. 14. Using PostgreSQL with Perl. DBI Architecture Overview. Prerequisites. Client 1-Connecting to the Server. Client 2-Adding Error Checking. Client 3-Processing Queries. Client 4-An Interactive Query Processor. 15. Using PostgreSQL with PHP. PHP Architecture Overview. Prerequisites. Client 1-Connecting to the Server. Client 2-Adding Error Checking. Client 3-Query Processing. Client 4-an Interactive Query Processor. Other Features. 16. Using PostgreSQL with Tcl and Tcl/Tk. Prerequisites. Client 1-Connecting to the Server. Client 2-Query Processing. Client 3-An Interactive Query Processor. The libpgtcl Large-Object API. 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. III. POSTGRESQL ADMINISTRATION. 18. Introduction to PostgreSQL Administration. Security. User Accounts. Backup and Restore. Server Startup and Shutdown. Tuning. Installing Updates. Localization. 19. PostgreSQL Administration. Roadmap (WhereA 's All My Stuff?). Installing PostgreSQL. Managing Databases. Managing User Accounts. Configuring Your PostgreSQL Runtime Environment. Arranging for PostgreSQL Startup and Shutdown. Backing Up and Copying Databases. 20. Internationalization and Localization. Locale Support. Multibyte Character Sets. 21. Security. Securing the PostgreSQL Data Files. Securing Network Access. Securing Tables. Index.