PostgreSQL 8 for Windows

by Richard Blum

Overview

Publisher's Note: Products purchased from Third Party sellers are not guaranteed by the publisher for quality, authenticity, or access to any online entitlements included with the product.

The easiest way to set up a PostgreSQL database server on Windows

Get up-and-running on PostgreSQL quickly using this hands-on guide. Filled with real-world examples, PostgreSQL 8 for Windows offers you practical, step-by-step details on installing, configuring, and using PostgreSQL 8—the full-featured, open-source database management system—on Windows platforms. You'll learn to administer, secure, and tune your database and use SQL. You'll also discover how to interface Microsoft Access, Microsoft .NET, Visual C++, and Java with the PostgreSQL database.

• Install and configure PostgreSQL 8 on Windows
  
• Customize your system using the configuration files
• Work with the utilities
• Administer your database from the pgAdmin III graphical interface
• Use the psql command line program to manually execute SQL commands
• Take advantage of built-in functions or create your own stored procedures and triggers
• Implement tested security measures
• Maintain optimal database performance
• Access a PostgreSQL database from a Microsoft Access application and migrate Access databases to PostgreSQL
• Create .NET, Visual C++, and Java applications that interface with your PostgreSQL server

Product Details

ISBN-13:	9780071485623
Publisher:	McGraw Hill LLC
Publication date:	03/22/2007
Series:	Database Professional's Library
Edition description:	1ST
Pages:	400
Product dimensions:	7.30(w) x 8.90(h) x 0.81(d)

About the Author

Richard Blum has worked for more than 18 years in the IT field as a network and systems administrator. During this time he has worked with database servers in UNIX, Linux, Novell, and Microsoft servers.

Table of Contents

Acknowledgments     xi
Introduction     xiii
Installation and Administration
What Is PostgreSQL?     3
The Open Source Movement     4
The History of PostgreSQL     5
Comparing PostgreSQL     8
PostgreSQL Versus Microsoft Access     8
PostgreSQL Versus Commercial DBMS Products     12
PostgreSQL Features     13
Transaction Support     13
ACID Compliant     14
Nested Transactions     18
Sub-selects     18
Views     18
Rules     20
Triggers     20
Support for Binary Large Objects (BLOBs)     20
User-Defined Types     21
Roles     21
Table Partitioning     22
Generalized Search Tree (GiST)     24
Summary     24
Installing PostgreSQL on Windows     25
System Requirements     26
Windows Workstations     26
Windows Servers     29
Downloading PostgreSQL     33
Installing PostgreSQL     35
Installation Options Window     36
Service Configuration Window     39
InitialiseDatabase Cluster Window     40
Enable Procedural Languages Window     42
Enable Contrib Modules Window     43
Finish the Install     44
Running PostgreSQL     46
Service Method     46
Manual Method     47
Summary     48
The PostgreSQL Files and Programs     49
The PostgreSQL Directory     50
Database Cluster Directory     50
Configuration Files     53
The postgresql.conf File     54
The pg_hba.conf File     70
The pg_ident.conf File     74
Programs     75
PostgreSQL Server Commands     75
SQL Wrapper Commands     78
PostgreSQL Applications     79
Summary     80
Managing PostgreSQL on Windows     81
The pgAdmin III Program     82
Parts of the PostgreSQL System     83
Tablespaces     85
Databases     86
Group Roles     88
Login Roles     89
Creating a New Application     89
Creating a New Database     89
Creating a New Schema     91
Creating the Tables      94
Entering and Viewing Data     101
The pgAdmin III Query Tool     104
Working with User Accounts     105
Creating Group Roles     106
Creating Login Roles     109
Testing Privileges     111
Database Maintenance     112
Backups and Restores     114
Performing a Backup     115
Restoring a Database     116
Summary     117
Using PostgreSQL in Windows
The psql Program     121
The psql Command-Line Format     122
Connection Options     122
Feature Options     123
Using the Command-Line Options     126
The psql Meta-commands     127
psql General Meta-commands     128
Query Buffer Meta-commands     131
Input/Output Meta-commands     132
Informational Meta-commands     133
Formatting Meta-commands     135
Copy and Large Object Meta-commands     136
The psqlrc.conf File     138
Importing Data with psql     139
Summary     140
Using Basic SQL     141
The SQL Query Language     142
SQL History      142
SQL Format     142
Creating Objects     146
Creating a Database     146
Creating a Schema     148
Creating a Table     149
Creating Group and Login Roles     154
Assigning Privileges     155
Handling Data     158
Inserting Data     158
Modifying Data     159
Deleting Data     161
Querying Data     162
The Basic Query Format     162
Writing Advanced Queries     164
Summary     167
Using Advanced SQL     169
Revisiting the Select Command     170
The Distinct Clause     171
The Select List     171
The From Clause     172
The Where Clause     174
The Group By Clause     174
The Having Clause     175
The Set Operation Clauses     175
The Order By Clause     176
The Limit Clause     176
The For Clause     177
Table Views     177
Table Indexes     179
Why Use Indexes?     180
Creating an Index     180
Determining the Index Method      183
Transactions     185
Basic Transactions     186
Advanced Transactions     187
Cursors     189
Creating a Cursor     189
Using a Cursor     190
Summary     194
PostgreSQL Functions     195
What Is a Function?     196
Operators     196
Built-in Functions     205
String Functions     206
Date and Time Functions     209
Math Functions     211
Aggregate Functions     211
Summary     214
Stored Procedures and Triggers     215
PostgreSQL Procedural Languages     216
Types of Functions     217
The PL/pgSQL Language     217
Creating a PL/pgSQL Function     218
Creating a Stored Procedure Using pgAdmin III     222
PL/pgSQL Function Code     225
Triggers     227
Trigger Function Format     228
Creating a Trigger Function     229
Testing the Trigger Function     232
Summary     232
Security     235
Controlling Network Users     236
Controlling Access via Firewalls     236
Controlling Access via Configuration Files     239
Testing Remote Connectivity     241
Encrypting Network Sessions     242
Enabling SSL in PostgreSQL     244
Encryption Keys and Certificates     244
Creating an SSL Encryption Key     245
Testing SSL Encryption     248
Monitoring Users     249
Summary     250
Performance     251
Enhancing Query Performance     252
The Explain Command     252
Using pgAdmin III to Evaluate Queries     255
The postgresql.conf Performance Parameters     257
Query Tuning     257
Resource Usage     259
Runtime Statistics     260
Summary     261
Windows Programming with PostgreSQL
Microsoft Access and PostgreSQL     265
Interfacing PostgreSQL with Access     266
Installing the ODBC Driver     266
Configuring a PostgreSQL ODBC Connection     269
Creating an Access Application Using PostgreSQL     275
Data Type Considerations     275
Designing an Application Database     276
Setting Up the ODBC Session      277
Creating the Access Application     278
Using PostgreSQL Views in Access     282
Sharing the Application     284
Exporting an Access Application to PostgreSQL     284
Summary     286
Microsoft .NET Framework     287
The Microsoft .NET Framework     288
Creating a .NET Development Environment     289
Downloading the .NET Packages     290
Installing the .NET Packages     290
Integrating the Npgsql Library     292
Downloading Npgsql     293
Installing the Npgsql Library     294
Creating .NET Applications with Npgsql     294
The Npgsql Library     295
The NpgsqlConnection Class     296
The NpgsqlCommand Class     300
The NpgsqlParameterCollection Class     309
Summary     311
Visual C++     313
The Visual C++ Programming Environment     314
Visual C++ Express Edition     314
Downloading and Installing Visual C++ Express Edition     315
Installing the Microsoft Platform SDK     319
The libpq Library     322
The libpq Functions     325
Opening and Closing Sessions      325
Executing SQL Commands     330
Using Parameters     338
Summary     342
Java     343
The Java Development Environment     344
Downloading the Java SDK     345
Installing the Java SDK     346
Building a Java Program Using NetBeans     348
PostgreSQL JDBC Driver     350
Using JDBC in a NetBeans Application     352
Using JDBC in a Java Command-Line Application     353
Java Database Connectivity     354
Starting a Connection     354
Sending SQL Commands     356
Using Parameters and Prepared Statements     362
Summary     365
Index     367