Beginning Databases with PostgreSQL: From Novice to Professional

by Matthew, Neil

• ISBN13: 9781590594780
• ISBN10: 1590594789
• Edition: 2nd
• Format: Paperback
• Copyright: 2005-04-30
• Publisher: Apress

Summary

This book introduces readers to many of the most prominent features of PostgreSQL, one of the world's most popular Open Source relational database systems, simultaneously introducing key relational database design and management principles that will help the novice reader effectively manage their data-driven application. Over 150 pages of coverage is devoted to the most popular PostgreSQL APIs, including PHP, Perl, Java and C. Users of all levels will appreciate the 50 pages of condensed reference material covering PostgreSQL data types, syntax, the psql client, and large object support.

Table of Contents

About the Authors	xvii
About the Technical Reviewer	xix
Acknowledgments	xxi
Introduction	xxiii
Introduction to PostgreSQL	1	(16)
Programming with Data	1	(3)
Constant Data	2	(1)
Flat Files for Data Storage	2	(1)
Repeating Groups and Other Problems	3	(1)
What Is a Database Management System?	4	(7)
Database Models	4	(4)
Query Languages	8	(2)
Database Management System Responsibilities	10	(1)
What Is PostgreSQL?	11	(4)
A Short History of PostgreSQL	12	(1)
The PostgreSQL Architecture	13	(2)
Data Access with PostgreSQL	15	(1)
What Is Open Source?	15	(1)
Resources	16	(1)
Relational Database Principles	17	(26)
Limitations of Spreadsheets	17	(4)
Storing Data in a Database	21	(2)
Choosing Columns	21	(1)
Choosing a Data Type for Each Column	21	(1)
Identifying Rows Uniquely	22	(1)
Accessing Data in a Database	23	(5)
Accessing Data Across a Network	24	(1)
Handling Multiuser Access	25	(1)
Slicing and Dicing Data	26	(2)
Adding Information	28	(4)
Using Multiple Tables	28	(1)
Relating a Table with a Join Operation	29	(3)
Designing Tables	32	(8)
Understanding Some Basic Rules of Thumb	33	(1)
Creating a Simple Database Design	34	(1)
Extending Beyond Two Tables	35	(2)
Completing the Initial Design	37	(3)
Basic Data Types	40	(1)
Dealing with the Unknown: Nulls	41	(1)
Reviewing the Sample Database	42	(1)
Summary	42	(1)
Getting Started with PostgreSQL	43	(30)
Installing PostgreSQL on Linux and Unix Systems	43	(16)
Installing PostgreSQL from Linux Binaries	44	(3)
Anatomy of a PostgreSQL Installation	47	(2)
Installing PostgreSQL from the Source Code	49	(4)
Setting Up PostgreSQL on Linux and Unix	53	(6)
Installing PostgreSQL on Windows	59	(5)
Using the Windows Installer	59	(5)
Configuring Client Access	64	(1)
Creating the Sample Database	64	(8)
Creating User Records	65	(1)
Creating the Database	65	(2)
Creating the Tables	67	(1)
Removing the Tables	68	(1)
Populating the Tables	69	(3)
Summary	72	(1)
Accessing Your Data	73	(40)
Using psql	74	(4)
Starting Up on Linux Systems	74	(1)
Starting Up on Windows Systems	74	(1)
Resolving Startup Problems	75	(3)
Using Some Basic psql Commands	78	(1)
Using Simple Select Statements	78	(8)
Overriding Column Names	81	(1)
Controlling the Order of Rows	81	(2)
Suppressing Duplicates	83	(3)
Performing Calculations	86	(1)
Choosing the Rows	87	(6)
Using More Complex Conditions	89	(2)
Pattern Matching	91	(1)
Limiting the Results	92	(1)
Checking for Null	93	(1)
Checking Dates and Times	94	(6)
Setting the Time and Date Style	94	(4)
Using Date and Time Functions	98	(2)
Working with Multiple Tables	100	(10)
Relating Two Tables	100	(5)
Aliasing Table Names	105	(1)
Relating Three or More Tables	106	(4)
The SQL92 Select Syntax	110	(2)
Summary	112	(1)
PostgreSQL Command-Line and Graphical Tools	113	(36)
psql	113	(8)
Starting psql	114	(1)
Issuing Commands in psql	114	(1)
Working with the Command History	115	(1)
Scripting psql	115	(2)
Examining the Database	117	(1)
psql Command-Line Quick Reference	118	(1)
psql Internal Commands Quick Reference	119	(2)
ODBC Setup	121	(4)
Installing the ODBC Driver	121	(2)
Creating a Data Source	123	(2)
pgAdmin III	125	(4)
Installing pgAdmin III	125	(1)
Using pgAdmin III	126	(3)
phpPgAdmin	129	(4)
Installing phpPgAdmin	130	(1)
Using phpPgAdmin	130	(3)
Rekall	133	(4)
Connecting to a Database	134	(1)
Creating Forms	135	(1)
Building Queries	136	(1)
Microsoft Access	137	(5)
Using Linked Tables	137	(4)
Entering Data and Creating Reports	141	(1)
Microsoft Excel	142	(4)
Resources for PostgreSQL Tools	146	(1)
Summary	147	(2)
Data Interfacing	149	(24)
Adding Data to the Database	149	(16)
Using Basic Insert Statements	149	(3)
Using Safer Insert Statements	152	(2)
Inserting Data into Serial Columns	154	(4)
Inserting Null Values	158	(1)
Using the \copy Command	159	(3)
Loading Data Directly from Another Application	162	(3)
Updating Data in the Database	165	(4)
Using the Update Statement	165	(3)
Updating from Another Table	168	(1)
Deleting Data from the Database	169	(2)
Using the Delete Statement	169	(1)
Using the Truncate Statement	170	(1)
Summary	171	(2)
Advanced Data Selection	173	(28)
Aggregate Functions	173	(12)
The Count Function	174	(8)
The Min Function	182	(1)
The Max Function	183	(1)
The Sum Function	184	(1)
The Avg Function	184	(1)
The Subquery	185	(7)
Subqueries That Return Multiple Rows	187	(1)
Correlated Subqueries	188	(3)
Existence Subqueries	191	(1)
The Union Join	192	(2)
Self Joins	194	(2)
Outer Joins	196	(4)
Summary	200	(1)
Data Definition and Manipulation	201	(42)
Data Types	201	(11)
The Boolean Data Type	202	(2)
Character Data Types	204	(2)
Number Data Types	206	(3)
Temporal Data Types	209	(1)
Special Data Types	209	(1)
Arrays	210	(2)
Data Manipulation	212	(5)
Converting Between Data Types	212	(2)
Functions for Data Manipulation	214	(1)
Magic Variables	215	(1)
The OID Column	216	(1)
Table Management	217	(11)
Creating Tables	217	(1)
Using Column Constraints	218	(4)
Using Table Constraints	222	(1)
Altering Table Structures	223	(4)
Deleting Tables	227	(1)
Using Temporary Tables	227	(1)
Views	228	(4)
Creating Views	228	(3)
Deleting and Replacing Views	231	(1)
Foreign Key Constraints	232	(10)
Foreign Key As a Column Constraint	233	(1)
Foreign Key As a Table Constraint	234	(6)
Foreign Key Constraint Options	240	(2)
Summary	242	(1)
Transactions and Locking	243	(24)
What Are Transactions?	243	(4)
Grouping Data Changes into Logical Units	244	(1)
Concurrent Multiuser Access to Data	244	(2)
ACID Rules	246	(1)
Transaction Logs	247	(1)
Transactions with a Single User	247	(8)
Transactions Involving Multiple Tables	250	(1)
Transactions and Savepoints	251	(3)
Transaction Limitations	254	(1)
Transactions with Multiple Users	255	(7)
Implementing Isolation	255	(6)
Changing the Isolation level	261	(1)
Using Explicit and Implicit Transactions	261	(1)
Locking	262	(4)
Avoiding Deadlocks	262	(2)
Explicit Locking	264	(2)
Summary	266	(1)
Functions, Stored Procedures, and Triggers	267	(42)
Operators	268	(5)
Operator Precedence and Associativity	269	(1)
Arithmetic Operators	270	(2)
Comparison and String Operators	272	(1)
Other Operators	273	(1)
Built-in Functions	273	(3)
Procedural Languages	276	(6)
Getting Started with PL/pgSQL	277	(2)
Function Overloading	279	(2)
Listing Functions	281	(1)
Deleting Functions	281	(1)
Quoting	281	(1)
Anatomy of a Stored Procedure	282	(16)
Function Arguments	283	(1)
Comments	284	(1)
Declarations	284	(4)
Assignments	288	(1)
Execution Control Structures	289	(8)
Dynamic Queries	297	(1)
SQL Functions	298	(1)
Triggers	299	(7)
Defining a Trigger Procedure	300	(1)
Creating Triggers	300	(6)
Why Use Stored Procedures and Triggers?	306	(1)
Summary	307	(2)
PostgreSQL Administration	309	(48)
System Configuration	309	(8)
The bin Directory	310	(1)
The data Directory	311	(5)
Other PostgreSQL Subdirectories	316	(1)
Database Initialization	317	(1)
Server Control	318	(2)
Running Processes on Linux and Unix	318	(1)
Starting and Stopping the Server on Linux and Unix	319	(1)
PostgreSQL Internal Configuration	320	(18)
Configuration Methods	320	(1)
User Configuration	321	(4)
Group Configuration	325	(1)
Tablespace Management	326	(2)
Database Management	328	(3)
Schema Management	331	(6)
Privilege Management	337	(1)
Database Backup and Recovery	338	(9)
Creating a Backup	339	(2)
Restoring from a Backup	341	(2)
Backing Up and Restoring from pgAdmin III	343	(4)
Database Performance	347	(9)
Monitoring Behavior	347	(1)
Using Vacuum	348	(4)
Creating Indexes	352	(4)
Summary	356	(1)
Database Design	357	(28)
What Is a Good Database Design?	357	(3)
Understanding the Problem	357	(1)
Taking Design Aspects into Account	358	(2)
Stages in Database Design	360	(11)
Gathering Information	361	(1)
Developing a Logical Design	361	(5)
Determining Relationships and Cardinality	366	(5)
Converting to a Physical Model	371	(7)
Establishing Primary Keys	372	(1)
Establishing Foreign Keys	373	(2)
Establishing Data Types	375	(2)
Completing the Table Definitions	377	(1)
Implementing Business Rules	377	(1)
Checking the Design	378	(1)
Normal Forms	378	(2)
First Normal Form	378	(1)
Second Normal Form	379	(1)
Third Normal Form	379	(1)
Common Patterns	380	(4)
Many-to-Many	380	(1)
Hierarchy	381	(1)
Recursive Relationships	382	(2)
Resources for Database Design	384	(1)
Summary	384	(1)
Accessing PostgreSQL from C Using libpq	385	(34)
Using the libpq Library	386	(1)
Making Database Connections	387	(5)
Creating a New Database Connection	387	(3)
Using a Makefile	390	(1)
Retrieving Information About Connection Errors	391	(1)
Learning About Connection Parameters	391	(1)
Executing SQL with libpq	392	(9)
Determining Query Status	392	(2)
Executing Queries with PQexec	394	(2)
Creating a Variable Query	396	(1)
Updating and Deleting Rows	396	(1)
Extracting Data from Query Results	397	(3)
Handling Null Results	400	(1)
Printing Query Results	401	(3)
Managing Transactions	404	(1)
Using Cursors	404	(7)
Fetching All the Results at Once	406	(2)
Fetching Results in Batches	408	(3)
Dealing with Binary Values	411	(1)
Working Asynchronously	411	(6)
Executing a Query in Asynchronous Mode	412	(3)
Canceling an Asynchronous Query	415	(1)
Making an Asynchronous Database Connection	415	(2)
Summary	417	(2)
Accessing PostgreSQL from C Using Embedded SQL	419	(26)
Using ecpg	419	(6)
Writing an esqlc Program	420	(3)
Using a Makefile	423	(1)
Using ecpg Arguments	424	(1)
Logging SQL Execution	425	(1)
Making Database Connections	425	(2)
Error Handling	427	(5)
Reporting Errors	428	(3)
Trapping Errors	431	(1)
Using Host Variables	432	(4)
Declaring Fixed-Length Variable Types	432	(2)
Working with Variable-Length Data	434	(2)
Retrieving Data with ecpg	436	(5)
Dealing with Null-Terminated Strings	437	(1)
Dealing with Null Database Values	438	(1)
Handling Empty Results	439	(2)
Implementing Cursors in Embedded SQL	441	(2)
Debugging ecpg Code	443	(1)
Summary	444	(1)
Accessing PostgreSQL from PHP	445	(20)
Adding PostgreSQL Support to PHP	445	(1)
Using the PHP API for PostgreSQL	446	(1)
Making Database Connections	447	(3)
Creating a New Database Connection	447	(1)
Creating a Persistent Connection	448	(1)
Closing Connections	449	(1)
Learning More About Connections	449	(1)
Building Queries	450	(2)
Creating Complex Queries	451	(1)
Executing Queries	452	(1)
Working with Result Sets	452	(6)
Extracting Values from Result Sets	453	(3)
Getting Field Information	456	(1)
Freeing Result Sets	457	(1)
Type Conversion of Result Values	458	(1)
Error Handling	458	(1)
Getting and Setting Character Encoding	459	(1)
Using Pear	459	(4)
Using Pear's Database Abstraction Interface	460	(1)
Error Handling with Pear	461	(1)
Preparing and Executing Queries with Pear	462	(1)
Summary	463	(2)
Accessing PostgreSQL from Perl	465	(26)
Installing Perl Modules	466	(2)
Using CPAN	466	(1)
Using PPM	467	(1)
Installing the Perl DBI	468	(4)
Installing DBI and the PostgreSQL DBD on Windows	469	(2)
Installing DBI and the PostgreSQL DBD from Source	471	(1)
Using DBI	472	(12)
Making Database Connections	473	(4)
Executing SQL	477	(1)
Working with Result Sets	478	(3)
Binding Parameters	481	(2)
Using Other DBI Features	483	(1)
Using DBlx::Easy	484	(1)
Creating XML from DBI Queries	485	(4)
SQL to XML	487	(1)
XML to SQL	488	(1)
Summary	489	(2)
Accessing PostgreSQL from Java	491	(26)
Using a PostgreSQL JDBC Driver	491	(7)
Installing a PostgreSQL JDBC Driver	493	(1)
Using the Driver Interface and DriverManager Class	493	(5)
Making Database Connections	498	(4)
Creating Database Statements	498	(1)
Handling Transactions	499	(1)
Retrieving Database Meta Data	500	(2)
Working with JDBC Result Sets	502	(5)
Getting the Result Set Type and Concurrency	502	(1)
Traversing Result Sets	503	(1)
Accessing Result Set Data	504	(1)
Working with Updatable Result Sets	505	(2)
Using Other Relevant Methods	507	(1)
Creating JDBC Statements	507	(9)
Using Statements	508	(4)
Using Prepared Statements	512	(4)
Summary	516	(1)
Accessing PostgreSQL from C#	517	(26)
Using the ODBC .NET Data Provider on Windows	517	(3)
Setting Up the ODBC .NET Data Provider	517	(1)
Connecting to the Database	518	(1)
Retrieving Data into a Dataset	519	(1)
Using Npgsql in Mono	520	(19)
Connecting to the Database	521	(4)
Retrieving Data from the Database	525	(7)
Using Parameters and Prepared Statements with Npgsql	532	(4)
Changing Data in the Database	536	(3)
Using Npgsql in Visual Studio	539	(1)
Summary	540	(3)
APPENDIX A PostgreSQL Database Limits	543	(2)
APPENDIX B PostgreSQL Data Types	545	(6)
Logical Types	545	(1)
Exact Number Types	546	(1)
Approximate Number Types	546	(1)
Temporal Types	547	(1)
Character Types	547	(1)
Geometric Types	548	(1)
Miscellaneous PostgreSQL Types	548	(3)
APPENDIX C PostgreSQL SQL Syntax Reference	551	(22)
PostgreSQL SQL Commands	551	(1)
PostgreSQL SQL Syntax	552	(21)
APPENDIX D psql Reference	573	(4)
Command-Line Options	573	(1)
Internal Commands	574	(3)
APPENDIX E Database Schema and Tables	577	(4)
APPENDIX F Large Objects Support in PostgreSQL	581	(8)
Using Links	581	(1)
Using Encoded Text Strings	582	(1)
Using BLOBs	583	(6)
Importing and Exporting Images	583	(2)
Remote Importing and Exporting	585	(1)
Programming BLOBs	586	(3)
Index	589