Beginning Databases with PostgreSQL (eBook)

Richard Stones graduated from university with an electrical engineering degree, but decided software was more fun. He has programmed in a variety of languages, but only admits to knowing Visual Basic under duress. He has worked for a number of companies, from the very small to the very large, in a variety of areas, from real-time embedded systems upward. He is employed by Celesio AG as a systems architect, working principally on systems for the retail side of the business. He has co-authored several computing books with Neil Matthew, including Beginning Linux Programming, Professional Linux Programming, and Beginning Databases with MySQL.

Contents 6
About the Authors 18
About the Technical Reviewer 20
Acknowledgments 21
Introduction 22
Chapter 1 Introduction to PostgreSQL 24
Programming with Data 24
Constant Data 25
Flat Files for Data Storage 25
Repeating Groups and Other Problems 26
What Is a Database Management System? 27
Database Models 27
Query Languages 31
Database Management System Responsibilities 33
What Is PostgreSQL? 34
Short History of PostgreSQL 35
The PostgreSQL Architecture 36
Data Access with PostgreSQL 38
What Is Open Source? 38
Resources 39
Chapter 2 Relational Database Principles 40
Limitations of Spreadsheets 40
Storing Data in a Database 44
Choosing Columns 44
Choosing a Data Type for Each Column 44
Identifying Rows Uniquely 45
Accessing Data in a Database 46
Accessing Data Across a Network 47
Handling Multiuser Access 48
Slicing and Dicing Data 49
Adding Information 51
Using Multiple Tables 51
Relating a Table with a Join Operation 52
Designing Tables 55
Understanding Some Basic Rules of Thumb 56
Creating a Simple Database Design 57
Extending Beyond Two Tables 58
Completing the Initial Design 60
Basic Data Types 63
Dealing with the Unknown: NULLs 64
Reviewing the Sample Database 65
Summary 65
Chapter 3 Getting Started with PostgreSQL 66
Installing PostgreSQL on Linux and UNIX Systems 66
Installing PostgreSQL from Linux Binaries 67
Anatomy of a PostgreSQL Installation 70
Installing PostgreSQL from the Source Code 72
Setting Up PostgreSQL on Linux and UNIX 76
Installing PostgreSQL on Windows 82
Using the Windows Installer 82
Configuring Client Access 87
Creating the Sample Database 87
Creating User Records 88
Creating the Database 88
Creating the Tables 90
Removing the Tables 91
Populating the Tables 92
Summary 95
Chapter 4 Accessing Your Data 96
Using psql 97
Starting Up on Linux Systems 97
Starting Up on Windows Systems 97
Resolving Startup Problems 98
Using Some Basic psql Commands 101
Using Simple SELECT Statements 101
Overriding Column Names 104
Controlling the Order of Rows 104
Suppressing Duplicates 106
Performing Calculations 109
Choosing the Rows 110
Using More Complex Conditions 112
Pattern Matching 114
Limiting the Results 115
Checking for NULL 116
Checking Dates and Times 117
Setting the Time and Date Style 117
Using Date and Time Functions 121
Working with Multiple Tables 123
Relating Two Tables 123
Aliasing Table Names 128
Relating Three or More Tables 129
The SQL92 SELECT Syntax 133
Summary 135
Chapter 5 PostgreSQL Command-Line and Graphical Tools 136
psql 136
Starting psql 137
Issuing Commands in psql 137
Working with the Command History 138
Scripting psql 138
Examining the Database 140
psql Command-Line Quick Reference 141
psql Internal Commands Quick Reference 142
ODBC Setup 144
Installing the ODBC Driver 144
Creating a Data Source 146
pgAdmin III 148
Installing pgAdmin III 148
Using pgAdmin III 149
phpPgAdmin 152
Installing phpPgAdmin 153
Using phpPgAdmin 153
Rekall 156
Connecting to a Database 157
Creating Forms 158
Building Queries 159
Microsoft Access 160
Using Linked Tables 160
Entering Data and Creating Reports 164
Microsoft Excel 165
Resources for PostgreSQL Tools 169
Summary 170
Chapter 6 Data Interfacing 171
Adding Data to the Database 171
Using Basic INSERT Statements 171
Using Safer INSERT Statements 174
Inserting Data into Serial Columns 176
Inserting NULL Values 180
Using the /copy Command 181
Loading Data Directly from Another Application 184
Updating Data in the Database 187
Using the UPDATE Statement 187
Updating from Another Table 190
Deleting Data from the Database 191
Using the DELETE Statement 191
Using the TRUNCATE Statement 192
Summary 193
Chapter 7 Advanced Data Selection 194
Aggregate Functions 194
The Count Function 195
The Min Function 203
The Max Function 204
The Sum Function 205
The Avg Function 205
The Subquery 206
Subqueries That Return Multiple Rows 208
Correlated Subqueries 209
Existence Subqueries 212
The UNION Join 213
Self Joins 215
Outer Joins 217
Summary 221
Chapter 8 Data Definition and Manipulation 222
Data Types 222
The Boolean Data Type 223
Character Data Types 225
Number Data Types 227
Temporal Data Types 230
Special Data Types 230
Arrays 231
Data Manipulation 233
Converting Between Data Types 233
Functions for Data Manipulation 235
Magic Variables 236
The OID Column 237
Table Management 238
Creating Tables 238
Using Column Constraints 239
Using Table Constraints 243
Altering Table Structures 244
Deleting Tables 248
Using Temporary Tables 248
Views 249
Creating Views 249
Deleting and Replacing Views 252
Foreign Key Constraints 253
Foreign Key As a Column Constraint 254
Foreign Key As a Table Constraint 255
Foreign Key Constraint Options 261
Summary 263
Chapter 9 Transactions and Locking 264
What Are Transactions? 264
Grouping Data Changes into Logical Units 265
Concurrent Multiuser Access to Data 265
ACID Rules 267
Transaction Logs 268
Transactions with a Single User 268
Transactions Involving Multiple Tables 271
Transactions and Savepoints 272
Transaction Limitations 275
Transactions with Multiple Users 276
Implementing Isolation 276
Changing the Isolation level 282
Using Explicit and Implicit Transactions 282
Locking 283
Avoiding Deadlocks 283
Explicit Locking 285
Summary 287
Chapter 10 Functions, Stored Procedures, and Triggers 288
Operators 289
Operator Precedence and Associativity 290
Arithmetic Operators 291
Comparison and String Operators 293
Other Operators 294
Built-in Functions 294
Procedural Languages 297
Getting Started with PL/pgSQL 298
Function Overloading 300
Listing Functions 302
Deleting Functions 302
Quoting 302
Anatomy of a Stored Procedure 303
Function Arguments 304
Comments 305
Declarations 305
Assignments 309
Execution Control Structures 310
Dynamic Queries 318
SQL Functions 319
Triggers 320
Defining a Trigger Procedure 321
Creating Triggers 321
Why Use Stored Procedures and Triggers? 327
Summary 328
Chapter 11 PostgreSQL Administration 329
System Configuration 329
The bin Directory 330
The data Directory 331
Other PostgreSQL Subdirectories 336
Database Initialization 337
Server Control 338
Running Processes on Linux and UNIX 338
Starting and Stopping the Server on Linux and UNIX 339
PostgreSQL Internal Configuration 340
Configuration Methods 340
User Configuration 341
Group Configuration 345
Tablespace Management 346
Database Management 348
Schema Management 351
Privilege Management 357
Database Backup and Recovery 358
Creating a Backup 359
Restoring from a Backup 361
Backing Up and Restoring from pgAdmin III 363
Database Performance 367
Monitoring Behavior 367
Using VACUUM 368
Creating Indexes 372
Summary 376
Chapter 12 Database Design 377
What Is a Good Database Design? 377
Understanding the Problem 377
Taking Design Aspects into Account 378
Stages in Database Design 380
Gathering Information 381
Developing a Logical Design 381
Determining Relationships and Cardinality 386
Converting to a Physical Model 391
Establishing Foreign Keys 393
Establishing Data Types 395
Completing the Table Definitions 397
Implementing Business Rules 397
Checking the Design 398
Normal Forms 398
First Normal Form 398
Second Normal Form 399
Third Normal Form 399
Common Patterns 400
Many-to-Many 400
Hierarchy 401
Recursive Relationships 402
Resources for Database Design 404
Summary 404
Chapter 13 Accessing PostgreSQL from C Using libpq 405
Using the libpq Library 406
Making Database Connections 407
Creating a New Database Connection 407
Using a Makefile 410
Retrieving Information About Connection Errors 411
Learning About Connection Parameters 411
Executing SQL with libpq 412
Determining Query Status 412
Executing Queries with PQexec 414
Creating a Variable Query 416
Updating and Deleting Rows 416
Extracting Data from Query Results 417
Handling NULL Results 420
Printing Query Results 421
Managing Transactions 424
Using Cursors 424
Fetching All the Results at Once 426
Fetching Results in Batches 428
Dealing with Binary Values 431
Working Asynchronously 431
Executing a Query in Asynchronous Mode 432
Canceling an Asynchronous Query 435
Making an Asynchronous Database Connection 435
Summary 437
Chapter 14 Accessing PostgreSQL from C Using Embedded SQL 438
Using ecpg 438
Writing an esqlc Program 439
Using a Makefile 442
Using ecpg Arguments 443
Logging SQL Execution 444
Making Database Connections 444
Error Handling 446
Reporting Errors 447
Trapping Errors 450
Using Host Variables 451
Declaring Fixed-Length Variable Types 451
Working with Variable-Length Data 453
Retrieving Data with ecpg 455
Dealing with Null-Terminated Strings 456
Dealing with NULL Database Values 457
Handling Empty Results 458
Implementing Cursors in Embedded SQL 460
Debugging ecpg Code 462
Summary 463
Chapter 15 Accessing PostgreSQL from PHP 464
Adding PostgreSQL Support to PHP 464
Using the PHP API for PostgreSQL 465
Making Database Connections 466
Creating a New Database Connection 466
Creating a Persistent Connection 467
Closing Connections 468
Learning More About Connections 468
Building Queries 469
Creating Complex Queries 470
Executing Queries 471
Working with Result Sets 471
Extracting Values from Result Sets 472
Getting Field Information 475
Freeing Result Sets 476
Type Conversion of Result Values 477
Error Handling 477
Getting and Setting Character Encoding 478
Using PEAR 478
Using PEAR’s Database Abstraction Interface 479
Error Handling with PEAR 480
Preparing and Executing Queries with PEAR 481
Summary 482
Chapter 16 Accessing PostgreSQL from Perl 483
Installing Perl Modules 484
Using CPAN 484
Using PPM 485
Installing the Perl DBI 486
Installing DBI and the PostgreSQL DBD on Windows 487
Installing DBI and the PostgreSQL DBD from Source 489
Using DBI 490
Making Database Connections 491
Executing SQL 495
Working with Result Sets 496
Binding Parameters 499
Using Other DBI Features 501
Using DBIx::Easy 502
Creating XML from DBI Queries 503
SQL to XML 505
XML to SQL 506
Summary 507
Chapter 17 Accessing PostgreSQL from Java 508
Using a PostgreSQL JDBC Driver 508
Installing a PostgreSQL JDBC Driver 510
Using the Driver Interface and DriverManager Class 510
Making Database Connections 515
Creating Database Statements 515
Handling Transactions 516
Retrieving Database Meta Data 517
Working with JDBC Result Sets 519
Getting the Result Set Type and Concurrency 519
Traversing Result Sets 520
Accessing Result Set Data 521
Working with Updatable Result Sets 522
Using Other Relevant Methods 524
Creating JDBC Statements 524
Using Statements 525
Using Prepared Statements 529
Summary 533
Chapter 18 Accessing PostgreSQL from C# 534
Using the ODBC .NET Data Provider on Windows 534
Setting Up the ODBC .NET Data Provider 534
Connecting to the Database 535
Retrieving Data into a Dataset 536
Using Npgsql in Mono 537
Connecting to the Database 538
Retrieving Data from the Database 542
Using Parameters and Prepared Statements with Npgsql 549
Changing Data in the Database 553
Using Npgsql in Visual Studio 556
Summary 557
APPENDIX A PostgreSQL Database Limits 559
APPENDIX B PostgreSQL Data Types 561
Logical Types 561
Exact Number Types 562
Approximate Number Types 562
Temporal Types 563
Character Types 563
Geometric Types 564
Miscellaneous PostgreSQL Types 564
APPENDIX C PostgreSQL SQL Syntax Reference 566
PostgreSQL SQL Commands 566
PostgreSQL SQL Syntax 567
APPENDIX D psql Reference 588
Command-Line Options 588
Internal Commands 589
APPENDIX E Database Schema and Tables 592
APPENDIX F Large Objects Support in PostgreSQL 595
Using Links 595
Using Encoded Text Strings 596
Using BLOBs 597
INDEX 602

Accessing PostgreSQL from Perl (S. 465-466)

As earlier chapters have shown, communicating with PostgreSQL generally involves a lot of string manipulation. One language that excels at string manipulation is Perl. In Chapter 13, we demonstrated that the libpq interface is a powerful way to access a PostgreSQL database, but there are disadvantages. We need to use string manipulation to pass values to queries and to retrieve results, and for short programs, the C code dealing with strings can overshadow the database interactions.

As Chapter 13 pointed out, although binary access is possible, its benefits are minimal. With Perl, strings are much more sophisticated, supporting functionality such as joining, splitting, pattern matching, and automatic conversion to and from other data types. Perl has also historically been associated with web server processing (although more modern mechanisms such as PHP, described in the previous chapter, are taking over that role).

Having interfaces to databases definitely adds benefits. If you know even a little about Perl, you will be aware that one of the language’s axioms is that there is always more than one way to tackle any given job. In fact, Perl enthusiasts would be disappointed if they had to limit their options to single figures. We do not propose to bombard you with numerous techniques for accessing PostgreSQL databases from Perl, however. Instead, we will present a single methodology. There are essentially three ways to access PostgreSQL from Perl:

• Low-level access, which is essentially a Perl mapping of the libpq C interface (Module Pg)
• High-level access, using a database independent layer (DBI)
• Access by embedding the Perl interpreter (similar to the description in Chapter 14)

We will describe only the high-level DBI access mechanism, because it is the simplest to install and use. This method is database-independent, yet is still very flexible and powerful. If you are interested in a libpq-style of working, we suggest taking a look at Module Pg, which is part of the DBD:: Pg database driver. PL/Perl requires a version of Perl to have been initially built as a shared library - libperl.so, as opposed to the more usual libperl.a (see the instructions for building Perl found in Perl source distributions).

Installing Perl Modules

The Perl programming language supports the concept of modules - additional functions that can be integrated into a Perl installation to provide extra features. Many developers have developed modules to extend Perl, providing diverse functionality, including network protocols for file transfer or sending e-mail, parsing and manipulating XML documents, generating graphical images, and more. Much of Perl’s standard functionality is provided by modules included with a basic Perl installation.

In this chapter, we will be using several modules that are not included in the base Perl installation. Because installing modules is a fairly common task, the Perl community has developed a standard process for finding and installing modules. This process has itself been encapsulated in a Perl module, the CPAN module, which we will meet in a moment. Perl modules can be written in several languages, not just Perl. Modules written in a language other than Perl need to be compiled before they can be installed, but binaries are often available to download.