PostgreSQL 8 for Windows / Edition 1
by Richard Blum
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
… See more details belowOverview
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 Professional Publishing
- 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)
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
Customer Reviews
Average Review: