PostgreSQL 8 for Windows

by Blum, Richard

• ISBN13: 9780071485623
• ISBN10: 0071485627
• Edition: 1st
• Format: Paperback
• Copyright: 2007-04-12
• Publisher: McGraw-Hill Education

Summary

There have been more than 1 million downloads of PostgreSQL 8.1--two-thirds of these for the Windows platform This is the first distribution of the software geared toward Windows There is no other existing documentation for this product

Author Biography

Richard Blum has been a network and systems administrator for more than 17 years for a large government organization.

Table of Contents

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