| |
| |
| Introduction | |
| |
| |
| |
| PostgreSQL | |
| |
| |
| |
| About PostgreSQL | |
| |
| |
| License | |
| |
| |
| The Basic Concepts of PostgreSQL | |
| |
| |
| Software Architecture | |
| |
| |
| Limitations of PostgreSQL | |
| |
| |
| |
| 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 | |
| |
| |
| The User Interface | |
| |
| |
| |
| 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 Aliases | |
| |
| |
| Aggregation 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 | |
| |
| |
| |
| Understanding Transactions | |
| |
| |
| How the Transaction Code Affects Daily Work | |
| |
| |
| Rollbacks | |
| |
| |
| Concurrent Transactions | |
| |
| |
| Transaction Isolation | |
| |
| |
| Locking | |
| |
| |
| |
| 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 | |
| |
| |
| |
| 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 Authentification | |
| |
| |
| |
| 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 | |
| |
| |
| |
| 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 | |
| |
| |
| |
| Programming Interfaces | |
| |
| |
| C/C++ | |
| |
| |
| An Overview of C/C++ | |
| |
| |
| Connecting | |
| |
| |
| Simple Selects--An Example | |
| |
| |
| Binary Cursors and COPY | |
| |
| |
| Environment Variables | |
| |
| |
| A Reference of the C Interface | |
| |
| |
| Working with Large Objects | |
| |
| |
| A Reference of 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 | |
| |
| |
| |
| Extended PostgreSQL--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 | |
| |
| |
| |
| Practical PostgreSQL Solutions | |
| |
| |
| |
| 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 | |
| |
| |
| |
| Generating Multidimensional Results | |
| |
| |
| Generating Multidimensional Results on the Database Level | |
| |
| |
| Creating Multidimensional Arrays on the Application Level | |
| |
| |
| |
| Classification of Data--Real-World Examples | |
| |
| |
| Working with SQL | |
| |
| |
| EFEU--The Perfect Tool for Data Warehousing | |
| |
| |
| About EFEU | |
| |
| |
| Using PostgreSQL and Data Matrices | |
| |
| |
| |
| 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 | |
| |
| |
| |
| PostgreSQL Regression 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 | |
| |
| |
| |
| 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 | |
| |
| |
| |
| 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 Series | |
| |
| |
| |
| Persistent Database Connections with PHP | |
| |
| |
| Speed Tests | |
| |
| |
| Dangerous Side Effects | |
| |
| |
| Testing Persistent Connections | |
| |
| |
| Open Connections and Backend Processes | |
| |
| |
| Persistent Connections and Transactions | |
| |
| |
| |
| 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 | |
| |
| |
| |
| Drawing Graphs on Unix Systems Using gnuplot and PostgreSQL | |
| |
| |
| About gnuplot | |
| |
| |
| Using PostgreSQL and gnuplot | |
| |
| |
| A Simple LaTex Document | |
| |
| |
| Geometric Datatypes and gnuplot--A Simple Example | |
| |
| |
| Index | |