| |
| |
| Acknowledgments | |
| |
| |
| Introduction | |
| |
| |
| |
| Installation and Administration | |
| |
| |
| |
| What Is PostgreSQL? | |
| |
| |
| The Open Source Movement | |
| |
| |
| The History of PostgreSQL | |
| |
| |
| Comparing PostgreSQL | |
| |
| |
| PostgreSQL Versus Microsoft Access | |
| |
| |
| PostgreSQL Versus Commercial DBMS Products | |
| |
| |
| PostgreSQL Features | |
| |
| |
| Transaction Support | |
| |
| |
| ACID Compliant | |
| |
| |
| Nested Transactions | |
| |
| |
| Sub-selects | |
| |
| |
| Views | |
| |
| |
| Rules | |
| |
| |
| Triggers | |
| |
| |
| Support for Binary Large Objects (BLOBs) | |
| |
| |
| User-Defined Types | |
| |
| |
| Roles | |
| |
| |
| Table Partitioning | |
| |
| |
| Generalized Search Tree (GiST) | |
| |
| |
| Summary | |
| |
| |
| |
| Installing PostgreSQL on Windows | |
| |
| |
| System Requirements | |
| |
| |
| Windows Workstations | |
| |
| |
| Windows Servers | |
| |
| |
| Downloading PostgreSQL | |
| |
| |
| Installing PostgreSQL | |
| |
| |
| Installation Options Window | |
| |
| |
| Service Configuration Window | |
| |
| |
| Initialise Database Cluster Window | |
| |
| |
| Enable Procedural Languages Window | |
| |
| |
| Enable Contrib Modules Window | |
| |
| |
| Finish the Install | |
| |
| |
| Running PostgreSQL | |
| |
| |
| Service Method | |
| |
| |
| Manual Method | |
| |
| |
| Summary | |
| |
| |
| |
| The PostgreSQL Files and Programs | |
| |
| |
| The PostgreSQL Directory | |
| |
| |
| Database Cluster Directory | |
| |
| |
| Configuration Files | |
| |
| |
| The postgresql.conf File | |
| |
| |
| The pg_hba.conf File | |
| |
| |
| The pg_ident.conf File | |
| |
| |
| Programs | |
| |
| |
| PostgreSQL Server Commands | |
| |
| |
| SQL Wrapper Commands | |
| |
| |
| PostgreSQL Applications | |
| |
| |
| Summary | |
| |
| |
| |
| Managing PostgreSQL on Windows | |
| |
| |
| The pgAdmin III Program | |
| |
| |
| Parts of the PostgreSQL System | |
| |
| |
| Tablespaces | |
| |
| |
| Databases | |
| |
| |
| Group Roles | |
| |
| |
| Login Roles | |
| |
| |
| Creating a New Application | |
| |
| |
| Creating a New Database | |
| |
| |
| Creating a New Schema | |
| |
| |
| Creating the Tables | |
| |
| |
| Entering and Viewing Data | |
| |
| |
| The pgAdmin III Query Tool | |
| |
| |
| Working with User Accounts | |
| |
| |
| Creating Group Roles | |
| |
| |
| Creating Login Roles | |
| |
| |
| Testing Privileges | |
| |
| |
| Database Maintenance | |
| |
| |
| Backups and Restores | |
| |
| |
| Performing a Backup | |
| |
| |
| Restoring a Database | |
| |
| |
| Summary | |
| |
| |
| |
| Using PostgreSQL in Windows | |
| |
| |
| |
| The psql Program | |
| |
| |
| The psql Command-Line Format | |
| |
| |
| Connection Options | |
| |
| |
| Feature Options | |
| |
| |
| Using the Command-Line Options | |
| |
| |
| The psql Meta-commands | |
| |
| |
| psql General Meta-commands | |
| |
| |
| Query Buffer Meta-commands | |
| |
| |
| Input/Output Meta-commands | |
| |
| |
| Informational Meta-commands | |
| |
| |
| Formatting Meta-commands | |
| |
| |
| Copy and Large Object Meta-commands | |
| |
| |
| The psqlrc.conf File | |
| |
| |
| Importing Data with psql | |
| |
| |
| Summary | |
| |
| |
| |
| Using Basic SQL | |
| |
| |
| The SQL Query Language | |
| |
| |
| SQL History | |
| |
| |
| SQL Format | |
| |
| |
| Creating Objects | |
| |
| |
| Creating a Database | |
| |
| |
| Creating a Schema | |
| |
| |
| Creating a Table | |
| |
| |
| Creating Group and Login Roles | |
| |
| |
| Assigning Privileges | |
| |
| |
| Handling Data | |
| |
| |
| Inserting Data | |
| |
| |
| Modifying Data | |
| |
| |
| Deleting Data | |
| |
| |
| Querying Data | |
| |
| |
| The Basic Query Format | |
| |
| |
| Writing Advanced Queries | |
| |
| |
| Summary | |
| |
| |
| |
| Using Advanced SQL | |
| |
| |
| Revisiting the Select Command | |
| |
| |
| The Distinct Clause | |
| |
| |
| The Select List | |
| |
| |
| The From Clause | |
| |
| |
| The Where Clause | |
| |
| |
| The Group By Clause | |
| |
| |
| The Having Clause | |
| |
| |
| The Set Operation Clauses | |
| |
| |
| The Order By Clause | |
| |
| |
| The Limit Clause | |
| |
| |
| The For Clause | |
| |
| |
| Table Views | |
| |
| |
| Table Indexes | |
| |
| |
| Why Use Indexes? | |
| |
| |
| Creating an Index | |
| |
| |
| Determining the Index Method | |
| |
| |
| Transactions | |
| |
| |
| Basic Transactions | |
| |
| |
| Advanced Transactions | |
| |
| |
| Cursors | |
| |
| |
| Creating a Cursor | |
| |
| |
| Using a Cursor | |
| |
| |
| Summary | |
| |
| |
| |
| PostgreSQL Functions | |
| |
| |
| What Is a Function? | |
| |
| |
| Operators | |
| |
| |
| Built-in Functions | |
| |
| |
| String Functions | |
| |
| |
| Date and Time Functions | |
| |
| |
| Math Functions | |
| |
| |
| Aggregate Functions | |
| |
| |
| Summary | |
| |
| |
| |
| Stored Procedures and Triggers | |
| |
| |
| PostgreSQL Procedural Languages | |
| |
| |
| Types of Functions | |
| |
| |
| The PL/pgSQL Language | |
| |
| |
| Creating a PL/pgSQL Function | |
| |
| |
| Creating a Stored Procedure Using pgAdmin III | |
| |
| |
| PL/pgSQL Function Code | |
| |
| |
| Triggers | |
| |
| |
| Trigger Function Format | |
| |
| |
| Creating a Trigger Function | |
| |
| |
| Testing the Trigger Function | |
| |
| |
| Summary | |
| |
| |
| |
| Security | |
| |
| |
| Controlling Network Users | |
| |
| |
| Controlling Access via Firewalls | |
| |
| |
| Controlling Access via Configuration Files | |
| |
| |
| Testing Remote Connectivity | |
| |
| |
| Encrypting Network Sessions | |
| |
| |
| Enabling SSL in PostgreSQL | |
| |
| |
| Encryption Keys and Certificates | |
| |
| |
| Creating an SSL Encryption Key | |
| |
| |
| Testing SSL Encryption | |
| |
| |
| Monitoring Users | |
| |
| |
| Summary | |
| |
| |
| |
| Performance | |
| |
| |
| Enhancing Query Performance | |
| |
| |
| The Explain Command | |
| |
| |
| Using pgAdmin III to Evaluate Queries | |
| |
| |
| The postgresql.conf Performance Parameters | |
| |
| |
| Query Tuning | |
| |
| |
| Resource Usage | |
| |
| |
| Runtime Statistics | |
| |
| |
| Summary | |
| |
| |
| |
| Windows Programming with PostgreSQL | |
| |
| |
| |
| Microsoft Access and PostgreSQL | |
| |
| |
| Interfacing PostgreSQL with Access | |
| |
| |
| Installing the ODBC Driver | |
| |
| |
| Configuring a PostgreSQL ODBC Connection | |
| |
| |
| Creating an Access Application Using PostgreSQL | |
| |
| |
| Data Type Considerations | |
| |
| |
| Designing an Application Database | |
| |
| |
| Setting Up the ODBC Session | |
| |
| |
| Creating the Access Application | |
| |
| |
| Using PostgreSQL Views in Access | |
| |
| |
| Sharing the Application | |
| |
| |
| Exporting an Access Application to PostgreSQL | |
| |
| |
| Summary | |
| |
| |
| |
| Microsoft .NET Framework | |
| |
| |
| The Microsoft .NET Framework | |
| |
| |
| Creating a .NET Development Environment | |
| |
| |
| Downloading the .NET Packages | |
| |
| |
| Installing the .NET Packages | |
| |
| |
| Integrating the Npgsql Library | |
| |
| |
| Downloading Npgsql | |
| |
| |
| Installing the Npgsql Library | |
| |
| |
| Creating .NET Applications with Npgsql | |
| |
| |
| The Npgsql Library | |
| |
| |
| The NpgsqlConnection Class | |
| |
| |
| The NpgsqlCommand Class | |
| |
| |
| The NpgsqlParameterCollection Class | |
| |
| |
| Summary | |
| |
| |
| |
| Visual C++ | |
| |
| |
| The Visual C++ Programming Environment | |
| |
| |
| Visual C++ Express Edition | |
| |
| |
| Downloading and Installing Visual C++ Express Edition | |
| |
| |
| Installing the Microsoft Platform SDK | |
| |
| |
| The libpq Library | |
| |
| |
| The libpq Functions | |
| |
| |
| Opening and Closing Sessions | |
| |
| |
| Executing SQL Commands | |
| |
| |
| Using Parameters | |
| |
| |
| Summary | |
| |
| |
| |
| Java | |
| |
| |
| The Java Development Environment | |
| |
| |
| Downloading the Java SDK | |
| |
| |
| Installing the Java SDK | |
| |
| |
| Building a Java Program Using NetBeans | |
| |
| |
| PostgreSQL JDBC Driver | |
| |
| |
| Using JDBC in a NetBeans Application | |
| |
| |
| Using JDBC in a Java Command-Line Application | |
| |
| |
| Java Database Connectivity | |
| |
| |
| Starting a Connection | |
| |
| |
| Sending SQL Commands | |
| |
| |
| Using Parameters and Prepared Statements | |
| |
| |
| Summary | |
| |
| |
| Index | |