Beginning Databases with PostgreSQL

PostgreSQL is rapidly on its way to becoming the most successful open source relational database system. It has had an explosive growth from its roots in academia, to the Internet, through the efforts of a talented hard-working worldwide development team. The vast array of enterprise businesses migrating to PostgreSQL from proprietary databases are stark evidence for its success to come.

This book is a complete tutorial on PostgreSQL features and functions beginning with the basics and building up to the design and construction of databases and integrating them with programming languages for the Web. Here we'll show you how to make the most of this fully programmable databases' powerful features like aggregate, join, transaction, inheritance, embedding your own C routines and more.

Who is this book for?

This book is for beginners and will lead you through your very first database query, to the complex database commands needed to solve 'real world' problems. However, it's an advatage if you know the essentials of SQL programming and for specific chapters, familiarity with PHP, Perl, and Java will be useful. You can set up your favorite PostgreSQL database on Windows and UNIX systems.

What does this book cover?

• Detailed tutoria in PostgreSQL
• Installing from binaries and source code on UNIX and Windows
• Working with graphical tools
• Various forms of queries, subqueries, aggregate functions and joins
• Transactions, locking, stored procedures and triggers
• Performance monitoring, tuning and server control
• Connecting and executing SQL statements using C (libpq) and Embedded SQL
• Developing applications in PHP, Perl and Java

• Introduction

1. Introduction to PostgreSQL
• Programming with Data
• Flat File Databases
• What is a Database?
• Database Types
• Network Database Model
• Hierarchical Database Model
• Relational Database Model
• Query Languages
• SQL
• Database Management Systems
• What is PostgreSQL?
• A Short History of PostgreSQL
• The PostgreSQL Architecture
• Open Source Licensing
• Resources
2. Relational Database Principles
• Spreadsheets
• Some Terminology
• Limitations with Spreadsheets
• What's Different About a Database?
• Choosing Columns
• Choosing a Data Type for Each Column
• Identifying Rows Uniquely
• Order of Rows
• Putting Data Into a Database
• Access Across a Network
• Slicing and Dicing Data
• Adding Additional Information
• Multiple Tables
• Relations Between Tables
• Designing Tables
• Some Basic Rules of Thumb
• Rule of Thumb One — Break Down the Data Into Columns
• Rule of Thumb Two — Have a Unique Way of Identifying Each Row
• Rule of Thumb Three — Remove Repeating Information
• Rule of Thumb Four — Get the Naming Right
• Demonstration of Customer/Order Database
• Extending Beyond Two Tables
• Completing the Initial Design
• Basic Data Types
• NULLs
• Testing for NULLs
• The Sample Database
• Summary
3. Getting Started with PostgreSQL
• Install or Upgrade?
• Installing PostgreSQL from the Linux Binaries
• Anatomy of a PostgreSQL Installation
• Installing PostgreSQL From the Source Code
• Starting PostgreSQL
• Creating the Database
• Creating the Tables
• Populating the Tables
• Stopping PostgreSQL
• Installing PostgreSQL on Windows
• Cygwin — A UNIX Environment for Windows
• IPC Services for Windows
• PostgreSQL for Cygwin
• Compiling PostgreSQL on Windows
• Configuring PostgreSQL for Windows
• Starting PostgreSQL Automatically
• Summary
4. Accessing Your Data
• Using psql
• Simple SELECT Statements
• Overriding Column Names
• Contrrolling the Order of Rows
• Suppressing Duplicates
• Performing Calculations
• Choosing Rows
• More Complex Conditions
• Pattern Matching
• Limiting the Result
• Comparisons Using Other Types
• Checking NULL Values
• Chaecking Dates and Time
• Setting the Time and Date Style
• Date and Time Functions
• Multiple Tables
• Relating Two Tables
• Aliasing Table Names
• Relating Three Tables
• Summary
5. PostgreSQL Graphical Tools
• psql
• Starting psql
• Commands in psql
• Command History
• Scripting psql
• Examining the Database
• Command Line Quick Reference
• Internal Commands Quick Reference
• ODBC
• pgAdmin
• Kpsql
• PgAccess
• Forms and Query Designer
• Microsoft Access
• Linked Tables
• Data Entry
• Reports
• Microsoft Excel
• Resources
• Summary
6. Data Interfacing
• Adding Data to the Database
• Basic INSERTs
• Safer INSERT Statements
• Inserting Data Into SERIAL Columns
• Accessing Sequence Numbers
• Inserting NULL Values
• The \copy Command
• Loading Data Directly from Another Application
• Updating Data in the Database
• A Word of Warning
• Deleting Data from the Database
• Summary
7. Advanced Data Selection
• Aggregate Functions
• COUNT
• GROUP BY and COUNT(*)
• HAVING and COUNT(*)
• COUNT(column name)
• The MIN() Function
• The MAX() Function
• The SUM() Function
• The AVG() Function
• The UNION Join
• Subqueries
• Types of Subquery
• Correlated Fields
• Self Joins
• Outer Joins
• Summary
8. Data Definition and Manipulation
• Data Types
• Boolean
• Character
• Number
• Temporal
• PostgreSQL Special Types
• Creating Your Own Types
• Array Types
• Converting Between Types
• Other Data Manipulations
• Magic Variables
• The OID Column
• Manipulating Tables
• Creating Tables
• Column Constraints
• Table Constraints
• Altering Table Structures
• Deleting Tables
• Temporary Tables
• Views
• Foreign Key Constraints
• Foreign Key as a Column Constraint
• Foreign Key as a Table Constraint
• Foreign Key Constraint Options
• Deferrable
• ON UPDATE and ON DELETE
• Summary
9. Transactions and Locking
• What are Transactions?
• ACID Rules
• Transactions with Single Users
• Transactions Limitations
• Transactions with Multiple Users
• ANSI Isolation Levels
• Undesirable Phenomena
• ANSI/ISO Isolation Levels
• Chained (Auto Commit) and Unchained Mode
• Locking
• Deadlocks
• Explicit Locking
• Locking Rows
• Locking Tables
• Summary
10. Stored Procedures and Triggers
• Operators
• Operator Precedence and Associativity
• Arithmetic Operators
• Comparison and String Operators
• Other Operators
• Functions
• Procedural Languages
• Getting Started with PL/pgSQL
• Function Overloading
• Listing Functions
• Deleting Functions
• Quoting
• Anatomy of a Stored Procedure
• Function Arguments
• Comments
• Declarations
• ALIAS
• RENAME
• A Simple Variable Declaration
• A Composite Variable Declaration
• ROWTYPE
• RECORD
• Assignments
• SELECT INTO Statement
• PERFORM
• Execution Control Structures
• Returning from Functions
• Exceptions and Messages
• Conditionals
• Loops
• Dynamic Queries
• SQL Functions
• Triggers
• Creating Triggers
• Trigger Procedures
• Why Stored Procedures and Triggers?
• Summary
11. PostgreSQL Administration
• Default Installation
• bin
• include and lib
• doc
• man
• share
• data
• The Initial Database
• Server Control
• Starting and Stopping the Server
• Users
• CREATE USER
• DROP USER
• ALTER USER
• GROUPS
• PRIVILEGES
• Views
• Data Maintenance
• Creating and Deleting Databases
• Backing Up and Restoring Data
• Database Upgrades
• Database Security
• Configuration Options
• Build Time Server Configuration
• Run-time Server Configuration
• Performance
• VACUUM
• Indexes
• Summary
12. Database Design
• Understanding the Problem
• What is a Good Database Design?
• Stages in Database Design
• Gather Information
• Logical Design
• Determining Entities
• Convert Entities to Tables
• Determine Relationships and Cardinality
• Drawing Relationship Diagrams
• The Example Database
• Convert to a Physical Model
• Establish Primary Keys
• Establish Foreign Keys
• Establish Data Types
• Implement Business Rules
• Check the Design
• Normal Forms
• First Normal Form
• Second Normal Form
• Third Normal Form
• Common Patterns
• Many-to-Many
• Hierarchy
• Recursive Relationships
• Resources
• Summary
13. Accessing PostgreSQL from C Using libpq
• Using the libpq Library
• Database Connections
• Makefile
• More Information
• Executing SQL with libpq
• Transactions
• Extracting data from Queries
• Printing Query Results
• Cursors
• Binary Values
• Asynchronous Functionality
• Summary
14. Accessing PostgreSQL from C Using Embedded SQL
• A First Embedded SQL Program
• Arguments to ecpg
• Logging SQL Execution
• Database Connections
• Error Handling
• Trapping Errors
• Host Variables
• Retrieving Data with ecpg
• Transactions
• Handling Data
• Cursors
• Debugging ecpg Code
• Summary
15. Accessing PostgreSQL from PHP
• Adding PostgreSQL Support to PHP
• Using the PHP API for PostgreSQL
• Database Connections
• Persistent Connections
• Closing Connections
• Connection Information
• Building Queries
• Complex Queries
• Executing Queries
• Working with Resultsets
• Extracting Values from Resultsets
• Field Information
• Freeing Resultsets
• Type Conversation of Result Value
• Error Handling
• Character Encoding
• PEAR
• PEAR's Database Abstraction Interface
• PEAR's Error Handling
• Query Preparation and Execution
• Summary
16. Accessing PostgreSQL from Perl
• The pgsql_perl5 or Pg Module
• Installing pgsql_perl5
• Using pgsql_perl5
• The Perl DBI
• Installing DBIO and the PostgreSQL DBD
• Using DBI
• What Else Can We Do With DBI?
• Using DBIx::Easy
• DBI and XML
• Summary
17. Accessing PostgreSQL from Java
• JDBC Overview
• JDBC Drivers
• Type 7
• Type 8
• Type 9
• Type 10
• Building the PostgreSQL JDBC Driver
• DriverManager and Driver
• java.sql.DriverManager
• Managing Drivers
• Managing Connections
• Managing JDBC Logging
• Managing Login Timeouts
• java.sql.Driver
• Connections
• Creating Statements
• Handling Transactions
• Database Meta Data
• Retrieving PostgreSQL Meta Data
• JDBC Resultsets
• Resultset Concurrency and Type
• Type
• Concurrency
• Traversing Resultset
• Scrolling resultsets
• Querying the Cursor Position
• Fetch Direction and Size
• Accessing Resultset Data
• Mapping PostgreSQL Data Types
• Updateable Resultsets
• Deleting Data
• Updating Data
• Inserting Data
• Other Relevant Methods
• JDBC Statements
• Statement
• Executing SQL Statements
• Querying Results and Resultsets
• Handling SQL Batches
• Miscellaneous Methods
• An Example JDBC Client
• Prepared Statements
• Executing SQL Statements
• Updating Data
• An Example Using Prepared Statements
• SQL Exceptions and Warnings
• A JDBC GUI Application
• Class Diagram
• Customer
• Customer TableModel
• CustomerApp
• CustomerPanel
• System Interaction
• View Customer Details
• Adding New Customer
• Deleting a Customer
• Source Files
• The Customer Class
• The CustomerTableModel Class
• The CustomerPanel Class
• The CustomerApp Class
• Compile and Run the Application
• Summary
18. Further Information and Resources
• Non-Relational Storage
• OLTP, OLAP, and Other Database Terminology
• Resources
• Web Resources
• PostgreSQL
• PHP
• Perl
• Java and JDBC
• General Tools
• Books
• SQL
• PHP
• Perl
• Java
• Summary

• Appendix A: PostgreSQL Database Limits
• Database Size: No Limit
• Table Size: 16Tb-64Tb
• Rows in a Table: No Limit
• Table Indexes: No Limit
• Column Size: 1Gb
• Columns in a Table: 250+
• Row Size: No Limit
• Appendix B: PostgreSQL data types
• Logical Types
• Exact Number Types
• Approximate Number Types
• Temporal Types
• Character Types
• Geometric types
• Miscellaneous Types
• Appendix C: PostgreSQL SQL Syntax
• PostgreSQL SQL Commands
• PostgreSQL SQL Syntax
• Appendix D: psql Reference
• psql Command Line Options
• psql Internal Commands
• psql Internal Commands
• Appendix E: Database Schema and Tables
• Appendix F: Large Objects Support in PostgreSQL
• Adding Images to the Database
• BLOBs
• Import and Export
• Remote Import and Export
• Programming BLOBs

Excellent introduction to PostgreSQL and RDBMS in general. You can start programming while reading this and later use it as a reference.