PostgreSQL Developer's Handbook

PostgreSQL Developer's Handbook

by Ewald Geschwinde, Hans-Jürgen Schönig
Released December 2001
Publisher(s): Sams
ISBN: 9780672322600

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 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 datatypes, 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.

Table of contents

  1. Copyright
    1. Dedication
  2. About the Authors
    1. Ewald Geschwinde
    2. Hans-Jürgen Schönig
  3. Acknowledgments
  4. Tell Us What You Think!
  5. Introduction
  6. I. PostgreSQL
    1. 1. About PostgreSQL
      1. License
      2. The Basic Concepts of PostgreSQL
        1. Software Architecture
          1. The Postmaster
          2. The Backend
          3. The Frontend
        2. Limitations of PostgreSQL
    2. 2. Preparing
      1. Hardware Requirements
      2. Software Requirements
      3. Installing PostgreSQL
        1. Installing on UNIX Systems
          1. Installing on RPM-Based Systems
          2. Installing on Debian-Based Systems
          3. Installing the Source Code
        2. Installing on Windows
      4. Starting the Server and Creating Databases
        1. Using a Startup Script
        2. Using pg_ctl Directly
        3. Creating Databases
        4. Login
      5. The User Interface
    3. 3. An Introduction to SQL
      1. Relational Databases and Their Components
        1. Tables and Keys
          1. Primary Keys
          2. Foreign Keys
        2. Datatypes
        3. Indices
          1. B-Trees
          2. R-Trees
          3. Hash Access Methods
        4. Sequences
        5. Triggers
        6. Objects
          1. Classes
          2. Inheritance
          3. Function Overloading
        7. Views
        8. Procedures
        9. Aggregate Functions and Aggregate Expressions
          1. Aggregate Functions
          2. Aggregate Expressions
      2. Building a Database
        1. Building Simple Data Structures
          1. Creating Tables
          2. Using Temporary Tables
          3. The DROP TABLE Command
          4. The ALTER TABLE Command
          5. Creating and Dropping Indices
      3. Inserting Data
        1. Importing Large Datafiles and Using Pipes on UNIX Systems
      4. Retrieving Data
        1. Simple Queries
        2. Performing Simple Calculations
        3. Joining Tables
        4. Self-Joins and Aliases
        5. Aggregation Functions
        6. Subqueries
        7. Using SELECT and INSERT
        8. Pattern Matching and Regular Expressions in SQL
        9. Using SELECT INTO Statements
        10. Formatting the Output and Casting
          1. Generating HTML
      5. Updating and Deleting Data
        1. Writing UPDATE Queries
        2. Using DELETE
      6. Writing Advanced SQL Code
        1. Masquerading
        2. Working with Arrays
          1. Arrays and the COPY Command
        3. Working with BLOBs
        4. Using Network-Specific Datatypes
        5. Working with Geo-Data
          1. point
          2. line
          3. box
          4. path
          5. polygon
          6. circle
          7. Retrieving Geo-Data and Operators
        6. Combining Queries
      7. Building Complex Data Structures and Modeling Techniques
        1. Creating and Dropping Views
        2. Data Integrity with Constraints
        3. The CHECK Constraint
        4. Using Inheritance
        5. Modeling Techniques
          1. The Entity Relationship Model
          2. Normalization
          3. The Waterfall Model
    4. 4. Understanding Transactions
      1. How the Transaction Code Affects Daily Work
        1. Rollbacks
        2. Concurrent Transactions
        3. Transaction Isolation
        4. Locking
    5. 5. PL/PGSQL
      1. Why Use PL/pgSQL?
      2. Enabling a Database with PL/pgSQL
      3. The Structure of PL/pgSQL
      4. Writing Functions
        1. Inserting PL/pgSQL Functions into a Database
        2. A Simple Example
        3. Using Typecast Operations
        4. Simple Flow of Control Structures
          1. Using IF/ELSE
          2. Using LOOP/EXIT, WHILE, and FOR Loops
            1. PL/pgSQL also supports FOR loops:
      5. Using Functions in PL/pgSQL
        1. Function Overloading
        2. SELECT Statements and PL/pgSQL Functions
          1. Working with SELECT and Loops
        3. Exception Handling
        4. Making Functions More Independent from Datatypes
      6. Writing Triggers
        1. Automatically Created Variables
      7. Oracle's PL/SQL and PL/pgSQL—Comparing Two Worlds
        1. The Main Differences
          1. Quoting
          2. Function Overloading
          3. PostgreSQL and Database Cursors
          4. Creating and Replacing Functions
          5. Making Life Easier with instr
          6. Locking Tables
          7. Functions and Transactions
    6. 6. Database Administration
      1. How to Configure PostgreSQL
        1. Configuring the Postmaster
        2. PostgreSQL 7.1—The pg_options File
        3. PostgreSQL 7.1—postgreSQL.conf
      2. Adding Users and Managing User Rights
        1. A General Overview
        2. Adding and Deleting Users
        3. Changing User and Group Attributes
        4. Using GRANT and REVOKE
      3. Modifying Databases
        1. A General Overview
        2. Using ALTER
          1. A Short Overview
          2. Changing Names
          3. How Changes Affect Objects Related to other Objects
          4. Dropping Columns
      4. System Tables
        1. pg_attribute
        2. pg_description
        3. pg_group
        4. pg_index and pg_indexes
        5. pg_tables
        6. pg_trigger
      5. Security and Access Restrictions
        1. User Authentification
          1. Overview
      6. Summary
    7. 7. Backup and Recovery
      1. Common Errors
        1. Full Backup Versus Incremental Backup
        2. Starting Backups Using cron
        3. Backup Hardware
      2. Backup Strategies and Tools for PostgreSQL
        1. pg_dump and pg_dumplo
        2. Backup Server
        3. Jukeboxes and Basic Tape Operations
        4. Saving Large Amounts of Data on Multiple Tapes
        5. Journaling
      3. Summary
    8. 8. Performance Tuning
      1. Measuring Performance
      2. Indexes and Performance
      3. Using EXPLAIN and Understanding the Optimizer
        1. Understanding Execution Plans
        2. VACUUM
      4. Tuning SQL
        1. Rewriting Queries
        2. Influencing the Optimizer
        3. Caching the Result of Functions
      5. System Performance
        1. The Impact of I/O on System Performance and CPUs
          1. CLUSTER
          2. Tuning the File System
          3. fsync
          4. PostgreSQL on SMP Machines
        2. The Impact of Memory on System Performance and Restricted Access to System Resources
      6. Working with Large Amounts of Data
      7. Regular Expressions and Performance
      8. Summary
    9. 9. Programming Interfaces
      1. C/C++
        1. An Overview of C/C++
        2. Connecting
        3. Simple Selects—An Example
        4. Binary Cursors and COPY
        5. Environment Variables
        6. A Reference of the C Interface
          1. Database Connection Functions
          2. Query Execution Functions
          3. Asynchronous Query Processing
          4. Fastpath
          5. Asynchronous Notification
          6. Functions Related to the COPY Command
          7. Tracing Functions
          8. Control Functions
        7. Working with Large Objects
        8. A Reference of the C++ Interface
          1. The PgConnection Object
          2. The PgDatabase Object
          3. Asynchronous Notification
      2. Embedded SQL C Preprocessor (ECPG)
        1. The Basic Concepts of ECPG
        2. A First Example
        3. Error Handling
        4. Connecting
        5. Simple Queries and Retrieving Data
        6. A Final Example
      3. Perl
        1. Overview
        2. The Pg Module
          1. Overview
          2. Connecting
          3. Collecting Information about the Backend
          4. Running Queries
          5. Retrieving Information about the Result
          6. Working with BLOBs
          7. Final Word
        3. The DBI Interface and DBD for PostgreSQL
          1. Overview
          2. Installation
          3. Connecting
          4. Working with DBI
          5. Working with Arrays
          6. Transactions
          7. Errors and Exception Handling
          8. Debugging and Monitoring
          9. Notation and Conventions
        4. DBI Proxies
      4. PHP
        1. A Short History of PHP
        2. Connecting
        3. Working with PHP
        4. Errors and Exception Handling
        5. Working with BLOBs and File Uploads
        6. Debugging
        7. A Final Example
      5. Java
        1. A short history of Java
        2. JDBC
          1. What is JDBC?
          2. Components of a JDBC-Enabled System
          3. A Few Words about Java, SQL, and JDBC Driver
          4. Installing JDBC under Linux RedHat
          5. Connecting
          6. Simple Queries and Retrieving Data
          7. An Overview of the JDBC-API
          8. JDBC and Transactions
          9. Exception Handling with java.sql.SQLWarning
          10. Prepared Commands
          11. Stored Procedures
          12. PostgreSQL Extensions to the JDBC API
            1. A Few Words about JDBC Extensions
            2. fastpath
            3. Geometric Objects
            4. Large Objects
            5. Object Serialization
          13. A Final Example
      6. Python
        1. A Short Overview of Python
        2. Connecting
        3. The Database Wrapper Class DB
      7. TcL/TK
        1. The History of TcL
        2. Connecting
        3. Simple Examples
        4. Using PL/Tcl
          1. An Overview of PL/Tcl
          2. Simple Examples
          3. Function Overloading
          4. Accessing a Database with PL/Tcl
          5. Writing Triggers with PL/Tcl
        5. Building a Simple Tcl/Tk Frontend for PostgreSQL
      8. ODBC
        1. An Overview of ODBC
        2. Using ODBC with PostgreSQL
        3. ODBC and Security
    10. 10. Extended PostgreSQL—Software for PostgreSQL
      1. Contributed Software
        1. cube—A Self-Defined Datatype
          1. Installing cube
          2. Working with cubes
          3. cube and Indices
        2. Working with fulltext Indices
          1. The Problem with Indices and Substring Searching
          2. Working with the fulltextindex Package
        3. Working with ISBN and ISSN
        4. PostgreSQL Large Object Dumper
          1. Backup
          2. Recovery
        5. Benchmarking with pgbench
        6. The seg module
        7. The soundex Code
        8. Removing Lost Binary Objects from a Database
        9. pg_controldata
      2. Administration Tools
        1. phpPgAdmin
          1. Installing phpPgAdmin
          2. Working with phpPgAdmin
  7. II. Practical PostgreSQL Solutions
    1. 11. Importing Data and Working with Binary Data
      1. Importing ASCII and Binary Data Using COPY
      2. Working with ASCII and EBCDIC
        1. An Overview and a Short History of EBCDIC
        2. An Overview and a Short History of ASCII
        3. Understanding BCD Coding
        4. Taking Care of Byte Order
        5. The ASCII—EBCDIC Table
        6. Performing Simple EBCDIC—ASCII Conversions
      3. Summary
    2. 12. Generating Multidimensional Results
      1. Generating Multidimensional Results on the Database Level
      2. Creating Multidimensional Arrays on the Application Level
      3. Summary
    3. 13. Classification of Data—Real-World Examples
      1. Working with SQL
      2. EFEU—The Perfect Tool for Data Warehousing
        1. About EFEU
        2. Using PostgreSQL and Data Matrices
      3. Summary
    4. 14. Generating Flash with PostgreSQL and PHP
      1. PHP's Flash Interfaces
      2. Simple Flash Examples
      3. Adding Data to Flash
      4. Working with Geometric Datatypes
      5. Using Action Script for Interactive Applications
      6. A Final Example
      7. Summary
    5. 15. PostgreSQL Regression Tests
      1. The Idea Behind Regression Tests
      2. Running Tests
      3. Evaluating Regression Tests
        1. Error Messages
        2. Local Differences
        3. Date and Time Differences
        4. Floating-Point Differences
        5. Polygon Differences
        6. Ordering Differences
        7. ”Random” Testing
      4. Summary
    6. 16. Extending PostgreSQL's Core Features
      1. Adding Functions and an Overview of Programming Conventions
        1. Writing Simple SQL Functions
        2. Writing C Functions
        3. PostgreSQL's C Datatypes
        4. Calling Conventions for Functions Written in C
        5. Rules for Writing C Code
      2. Adding Datatypes
      3. Adding Operators
      4. Adding Aggregates
      5. Changing PostgreSQL's Rule System
        1. Using Rules for Changing PostgreSQL's Behavior
        2. Using Rules in Combination with Views
      6. Summary
    7. 17. Date Calculations
      1. SQL and Dates
        1. SQL, Dates, and Time Zones
        2. timestamp with time zone
        3. interval
        4. date
        5. time [ without time zone ]
        6. Special Values and Abbreviations
        7. Performing Simple Date and Time Operations with SQL
      2. Using Perl—The DateCalc Module
        1. An Overview
        2. The Current Calendar
        3. Basic Operations Using DateCalc
        4. Using DateCalc with PostgreSQL
      3. Analyzing a Time Series
      4. Summary
    8. 18. Persistent Database Connections with PHP
      1. Speed Tests
      2. Dangerous Side Effects
      3. Testing Persistent Connections
        1. Open Connections and Backend Processes
        2. Persistent Connections and Transactions
      4. Summary
    9. 19. Using PostgreSQL and Microsoft Software over the Network
      1. Connecting to MS Office
        1. Setting Up the ODBC Driver
      2. Using Visual Basic to Connect to PostgreSQL
        1. The First Contact
        2. A Simple Example
      3. Using PostgreSQL with Microsoft Office
        1. An Example Database
        2. Working with MS Access
        3. Working with MS Excel
        4. Working with MS Word
        5. Using PostgreSQL and MS Office with Huge Amounts of Data
      4. Summary
    10. 20. Drawing Graphs on Unix Systems Using gnuplot and PostgreSQL
      1. About gnuplot
      2. Using PostgreSQL and gnuplot
      3. A Simple LaTex Document
      4. Geometric Datatypes and gnuplot—A Simple Example
      5. Summary

Product information

  • Title: PostgreSQL Developer's Handbook
  • Author(s): Ewald Geschwinde, Hans-Jürgen Schönig
  • Release date: December 2001
  • Publisher(s): Sams
  • ISBN: 9780672322600