PostgreSQL

PostgreSQL

by Korry Douglas, Susan Douglas
Released February 2003
Publisher(s): Sams
ISBN: 9780735712577

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

PostgreSQL

is the world¿s most advanced open-source database. PostgreSQL is the most comprehensive, in-depth, and easy-to-read guide to this award-winning database. This book starts with a thorough overview of SQL, a description of all PostgreSQL data types, and a complete explanation of PostgreSQL commands.

If you are a developer or an administrator, you¿ll love the chapter that explores PostgreSQL performance. The authors explain how PostgreSQL stores data on disk (and in memory) and how to measure and influence the effectiveness of PostgreSQL¿s caching mechanisms. You¿ll also learn how PostgreSQL generates and evaluates execution plans. The authors explain all of the query operators that can appear in the results of an EXPLAIN command, describing the performance implications of each operator as well as the conditions which will cause PostgreSQL to use that operator.

PostgreSQL is also a complete guide for the developer. Whether you¿re developing with C, C++, ODBC, Embedded SQL, Java, Tcl/Tk, Perl, Python or PHP, you¿ll find a comprehensive description of the PostgreSQL API for your language of choice. Easy to follow exercises will walk you through the development of working applications that fully demonstrate the features offered by each API. You will also find a chapter that describes the PL/pgSQL server-side procedural language, learning how to build triggers, functions, and stored-procedures. The authors have even included a chapter that walks you through the process of extending the PostgreSQL server with custom-written C functions and new data types. The accompanying web site, www.conjectrix.com, contains downloadable versions of all of the sample code and a wealth of PostgreSQL-related resources.

The last section of PostgreSQL was written for the PostgreSQL administrator. You will learn how to install PostgreSQL on Windows, Linux, and Unix systems, from source code or from pre-compiled installers. The authors have described all of the PostgreSQL configuration options (compile-time, startup, and on-the-fly). The chapter on PostgreSQL security describes all of the authentication protocols that you can choose from and describes how to secure your database, both internally and externally. If you have to support a multi-national environment, you will find the chapter on internationalization and localization to be invaluable.

You won¿t find a more complete guide to PostgreSQL anywhere. The authors have made PostgreSQL approachable by leading the reader from basic concepts to advanced features. Whether you¿re a database beginner or an advanced user, you will find this book a valuable addition to your library.

Table of contents

  1. Copyright
  2. About the Authors
  3. About the Technical Reviewers
  4. About the Development Editor
  5. Acknowledgments
  6. We Want to Hear from You
  7. Reader Services
  8. Preface
  9. Introduction
    1. PostgreSQL Features
    2. What Versions Does This Book Cover?
      1. Who Is This Book For?
    3. What Topics Does This Book Cover?
  10. I. General PostgreSQL Use
    1. 1. Introduction to PostgreSQL and SQL
      1. A Sample Database
      2. Basic Database Terminology
      3. Prerequisites
        1. Installing PostgreSQL Using an RPM
      4. Connecting to a Database
        1. A (Very) Simple Query
      5. Creating Tables
      6. Viewing Table Descriptions
      7. Adding New Records to a Table
        1. Using the INSERT Command
        2. Using the COPY Command
      8. Installing the Sample Database
      9. Retrieving Data from the Sample Database
        1. SELECT Expression
        2. SELECT * FROM Table
        3. SELECT Single-Column FROM Table
        4. SELECT Column-List FROM Table
        5. SELECT Expression-List FROM Table
        6. Selecting Specific Rows
          1. SELECT [ALL | DISTINCT | DISTINCT ON]
          2. The WHERE Clause
          3. NULL Values
          4. The ORDER BY Clause
          5. LIMIT and OFFSET
        7. Formatting Column Results
          1. Working with Date Values
        8. Matching Patterns
          1. LIKE and NOT LIKE
          2. Pattern Matching with Regular Expressions
      10. Aggregates
        1. COUNT()
        2. SUM()
        3. AVG()
        4. MIN() and MAX()
        5. Other Aggregate Functions
        6. Grouping Results
      11. Multi-Table Joins
        1. Join Types
      12. UPDATE
      13. DELETE
      14. A (Very) Short Introduction to Transaction Processing
      15. Creating New Tables Using CREATE TABLE...AS
      16. Using VIEW
      17. Summary
    2. 2. Working with Data in PostgreSQL
      1. NULL Values
      2. Character Values
        1. Syntax for Literal Values
        2. Supported Operators
      3. Numeric Values
        1. Size, Precision, and Range-of-Values
        2. Syntax for Literal Values
        3. Supported Operators
      4. Date/Time Values
        1. Syntax for Literal Values
        2. Supported Operators
      5. Boolean (Logical) Values
        1. Size and Valid Values
        2. Syntax for Literal Values
        3. Supported Operators
      6. Geometric Data Types
        1. Syntax for Literal Values
        2. Sizes and Valid Values
        3. Supported Operators
      7. Object IDs (OID)
        1. Syntax for Literal Values
        2. Size and Valid Values
        3. Supported Operators
      8. BLOBs
        1. Syntax for Literal Values
        2. Supported Operators
        3. Large-Objects
      9. Network Address Data Types
        1. MACADDR
        2. CIDR
        3. INET
        4. Syntax for Literal Values
        5. Supported Operators
      10. Sequences
      11. Arrays
      12. Column Constraints
        1. NULL/NOT NULL
        2. UNIQUE
        3. PRIMARY KEY
        4. REFERENCES
        5. CHECK()
      13. Expression Evaluation and Type Conversion
      14. Creating Your Own Data Types
      15. Summary
    3. 3. PostgreSQL SQL Syntax and Use
      1. PostgreSQL Naming Rules
      2. Creating, Destroying, and Viewing Databases
        1. Creating New Databases
          1. createdb
        2. Dropping a Database
        3. Viewing Databases
      3. Creating New Tables
        1. Temporary Tables
        2. Table Constraints
        3. Dropping Tables
        4. Inheritance
        5. ALTER TABLE
      4. Adding Indexes to a Table
        1. Tradeoffs
        2. Creating an Index
        3. Functional Indexes and Partial Indexes
      5. Getting Information About Databases and Tables
      6. Transaction Processing
        1. Persistence
        2. Transaction Isolation
        3. Multi-Versioning and Locking
      7. Summary
    4. 4. Performance
      1. How PostgreSQL Organizes Data
        1. Page Caching
        2. Summary
      2. Gathering Performance Information
        1. Dead Tuples
        2. Index Performance
      3. Understanding How PostgreSQL Executes a Query
        1. EXPLAIN
        2. Seq Scan
        3. Index Scan
        4. Sort
        5. Unique
        6. LIMIT
        7. Aggregate
        8. Append
        9. Result
        10. Nested Loop
        11. Merge Join
        12. Hash and Hash Join
        13. Group
        14. Subquery Scan and Subplan
        15. Tid Scan
        16. Materialize
        17. Setop (Intersect, Intersect All, Except, Except All)
      4. Table Statistics
      5. Performance Tips
  11. II. Programming with PostgreSQL
    1. 5. Introduction to PostgreSQL Programming
      1. Server-Side Programming
        1. PL/pgSQL
        2. Other Procedural Languages Supported by PostgreSQL
        3. Extending PostgreSQL Using External Languages
      2. Client-Side APIs
      3. General Structure of Client Applications
      4. Choosing an Application Environment
        1. Server-Side Code
        2. Client-Side Code
        3. Mixing Server-Side and Client-Side Code
      5. Summary
    2. 6. Extending PostgreSQL
      1. Extending the PostgreSQL Server with Custom Functions
      2. Returning Multiple Values from an Extension Function
      3. Extending the PostgreSQL Server with Custom Data Types
      4. Internal and External Forms
      5. Defining a Simple Data Type in PostgreSQL
      6. Defining the Data Type in C
      7. Defining the Input and Output Functions in C
      8. Defining the Input and Output Functions in PostgreSQL
      9. Defining the Data Type in PostgreSQL
      10. Summary
    3. 7. PL/pgSQL
      1. Installing PL/pgSQL
      2. Language Structure
        1. Quoting Embedded Strings
        2. CREATE FUNCTION
        3. DROP FUNCTION
      3. Function Body
        1. Comments
        2. Variables
          1. Function Parameters
          2. DECLARE
          3. Pseudo Data Types—%TYPE, %ROWTYPE, OPAQUE, and RECORD
          4. ALIAS and RENAME
          5. FOR Loop Iterator
        3. PL/pgSQL Statement Types
          1. Assignment
          2. SELECT INTO
          3. Conditional Execution
          4. Loop Constructs
          5. EXIT
          6. Labels—EXIT Targets and Name Qualifiers
          7. RETURN
          8. PERFORM
          9. EXECUTE
          10. GET DIAGNOSTICS
          11. Error Handling
          12. RAISE
      4. Cursors
        1. FETCH
        2. Parameterized Cursors
        3. Cursor References
      5. Triggers
      6. Summary
    4. 8. The PostgreSQL C API—libpq
      1. Prerequisites
      2. Client 1—Connecting to the Server
        1. Compiling the Client
        2. Identifying the Server
      3. Client 2—Adding Error Checking
        1. Viewing Connection Attributes
      4. Client 3—Simple Processing—PQexec() and PQprint()
        1. Results Returned by PQexec()
        2. Binary Cursors
      5. Client 4—An Interactive Query Processor
        1. Processing Multiple Result Sets
        2. Asynchronous Processing
      6. Summary
    5. 9. 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
        1. Working with Binary Cursors
        2. Byte Ordering and NULL Values
      5. Client 4—An Interactive Query Processor
      6. Summary
    6. 10. The PostgreSQL C++ API—libpq++
      1. Prerequisites
      2. Client 1—Connecting to the Server
      3. Client 2—Adding Error Checking
      4. Client 3—Processing Queries
        1. Working with Transactions
        2. Working with Cursors
        3. Working with Large-Objects
      5. Client 4—An Interactive Query Processor
      6. Summary
    7. 11. Embedding SQL Commands in C Programs—ecpg
      1. Prerequisites
      2. Client 1—Connecting to the Server
        1. The ecpg Preprocessor
        2. Connection Strings
      3. Client 2—Adding Error Checking
        1. The sqlca Structure
      4. Client 3—Processing SQL Commands
        1. ecpg Data Types
      5. Client 4—An Interactive Query Processor
      6. Summary
    8. 12. Using PostgreSQL from an ODBC Client Application
      1. ODBC Architecture Overview
        1. The ODBC Client Application
        2. The ODBC Driver Manager
        3. The ODBC Driver
        4. The ODBC-Compliant Database
        5. The Data Source
        6. Setting Up a Data Source on Unix Systems
          1. Installing unixODBC and the PostgreSQL ODBC Driver
        7. Setting Up a Data Source in Windows
      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. 13. Using PostgreSQL from a Java Client Application
      1. JDBC Architecture Overview
        1. The JDBC DriverManager
        2. The JDBC Driver
        3. The JDBC-Compliant Database
      2. Prerequisites
      3. Client 1—Connecting to the Server
        1. JDBC URLs
      4. Client 2—Adding Error Checking
      5. Client 3—Processing Queries
        1. Statement Classes
          1. PreparedStatement
          2. CallableStatement
        2. Metadata
      6. Client 4—An Interactive Query Processor
      7. Summary
    10. 14. Using PostgreSQL with Perl
      1. DBI Architecture Overview
        1. The DBI
        2. The DBD Driver
        3. The DBI-Compliant Database
      2. Prerequisites
      3. Client 1—Connecting to the Server
        1. DBI URLs
      4. Client 2—Adding Error Checking
      5. Client 3—Processing Queries
        1. The Prepare/Execute Model
        2. Metadata and Result Set Processing
        3. Other Statement and Database Handle Attributes
      6. Client 4—An Interactive Query Processor
      7. Summary
    11. 15. 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
        1. Other Ways to Retrieve Result Set Values
        2. Metadata Access
      6. Client 4—an Interactive Query Processor
      7. Other Features
      8. Summary
    12. 16. Using PostgreSQL with Tcl and Tcl/Tk
      1. Prerequisites
        1. PostgreSQL-Related Tcl/Tk Components
      2. Client 1—Connecting to the Server
        1. Making the Connection Dialog Reusable
      3. Client 2—Query Processing
        1. Result Set Processing
      4. Client 3—An Interactive Query Processor
      5. The libpgtcl Large-Object API
      6. Summary
    13. 17. 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
  12. III. PostgreSQL Administration
    1. 18. Introduction to PostgreSQL Administration
      1. Security
      2. User Accounts
      3. Backup and Restore
      4. Server Startup and Shutdown
      5. Tuning
      6. Installing Updates
      7. Localization
      8. Summary
    2. 19. PostgreSQL Administration
      1. Roadmap (Where's All My Stuff?)
      2. Installing PostgreSQL
        1. Unix/Linux
          1. From Binaries
          2. From Source
            1. Downloading and Unpacking the Source Code
            2. Configuring the Source Code
            3. Compiling the Source Code
            4. Installing the Compiled Code
          3. Completing the Installation Process
        2. Windows
          1. From Binaries
          2. From Source
          3. Completing the Installation Process
      3. Managing Databases
        1. Creating a New Cluster
        2. Creating a New Database
        3. Routine Maintenance
          1. Managing Tables (cluster and vacuum)
          2. Managing Indexes
      4. Managing User Accounts
        1. CREATE USER
          1. SYSID
          2. Privileges (CREATEDB and CREATEUSER)
          3. Group Membership (IN GROUP)
          4. PASSWORD and Password Expiration
          5. createuser
          6. ALTER USER
          7. DROP USER
          8. GRANT and REVOKE
        2. Managing Groups
          1. CREATE GROUP
          2. ALTER GROUP
          3. DROP GROUP
      5. Configuring Your PostgreSQL Runtime Environment
        1. Connection-Related Parameters
        2. Operational Parameters
        3. Optimizer Parameters
        4. Debugging/Logging Parameters
        5. Performance Statistics
        6. Miscellaneous Parameters
      6. Arranging for PostgreSQL Startup and Shutdown
        1. Using pg_ctl
        2. Shutdown Modes
        3. Configuring PostgreSQL Startup on Unix/Linux Hosts
        4. Configuring PostgreSQL as a Windows Service
      7. Backing Up and Copying Databases
        1. Using pg_dump
        2. Using pg_dumpall
        3. Using pg_restore
      8. Summary
    3. 20. Internationalization and Localization
      1. Locale Support
        1. Enabling Locale Support
        2. Effects of Locale Support
      2. Multibyte Character Sets
        1. Encodings Supported by PostgreSQL
        2. Enabling Multibyte Support
        3. Selecting an Encoding
        4. Client/Server Translation
      3. Summary
    4. 21. Security
      1. Securing the PostgreSQL Data Files
      2. Securing Network Access
        1. local Connections
        2. host and hostssl Connections
        3. The trust Authentication Method
        4. The ident Authentication Method
        5. The password Authentication Method
          1. Defining pg_shadow Passwords
          2. Defining Passwords for Flat Password Files
        6. The crypt Authentication Method
        7. The md5 Authentication Method
        8. The pam Authentication Method
        9. The krb4 and krb5 Authentication Methods
        10. The reject Authentication Method
      3. Securing Tables
      4. Summary

Product information

  • Title: PostgreSQL
  • Author(s): Korry Douglas, Susan Douglas
  • Release date: February 2003
  • Publisher(s): Sams
  • ISBN: 9780735712577