Practical PostgreSQL

John Worsley

Command Prompt, Inc.

Joshua Drake

Command Prompt, Inc.

Andrew Brookins

Command Prompt, Inc.

Copyright (c) 2001 by Command Prompt, Inc. This material may be distributed only subject to the terms and conditions set forth in the Open Publication License, v1.0 or later (the latest version is presently available at

'Distribution of substantively modified versions of this document is prohibited without the explicit permission of the copyright holder.' to the license reference or copy.

'Distribution of the work or derivative of the work in any standard (paper) book form is prohibited unless prior permission is obtained from the copyright holder.' to the license reference or copy.

Although every reasonable effort has been made to incorporate accurate and useful information into this book, the copyright holders make no representation about the suitability of this book or the information therein for any purpose. It is provided "as is" without expressed or implied warranty.

Table of Contents
Who is the Intended Audience?
What is Included in the CD?
I. Introduction and Installation
1. What is PostgreSQL?
Open Source Free Version
Commercial PostgreSQL Products
Open Source versus Commercial Products
The Bottom Line
Commercial Support
Community Support
PostgreSQL Feature Set
Where to Proceed from Here
2. Installing PostgreSQL
Preparing for Installation
Installation Requirements
Optional Packages
10 Steps to PostgreSQL Installation
Step 1: Installing the PostgreSQL Source Package.
Step 2: Configuring the Source Tree
Step 3: Compiling the Source
Step 4: Regression Testing
Step 5: Installing Compiled Programs and Libraries
Step 6: Creating the "postgres" user
Step 7: Setting Environment Variables.
Step 8: Initializing and Starting PostgreSQL
Step 9: Configuring the PostgreSQL SysV Script
Step 10: Creating a Database
II. Using PostgreSQL
3. Understanding SQL
Introduction to SQL
A Brief History of SQL
SQL and its Predecessors
SQL Standards
Introduction to Relational Databases
Understanding Databases
Understanding Tables
SQL Statements
The Anatomy of a SQL Statement
Formatting Considerations
Keywords and Identifiers
Special Character Symbols
Putting it All Together
Data Types
Type Coercion
NULL Values
Boolean Values
Character Types
Numeric Types
Date and Time Types
Geometric Types
Tables in PostgreSQL
System Columns
Object Identifiers
Planning Ahead
4. Applying SQL with PostgreSQL
Introduction to psql
Starting psql
Introduction to psql Syntax
Executing Queries
Using Tables
Creating Tables with CREATE TABLE
Altering Tables with ALTER TABLE
Re-structuring Existing Tables
Destroying Tables with DROP TABLE
Managing Data with SQL
Adding Data with INSERT and COPY
Retrieving Rows with SELECT
Modifying Rows with UPDATE
Removing Rows with DELETE
Using Views
Creating Views
Destroying Views
Using Operators
Using Operators
Character and Text Operators
Numeric Operators
Logical Operators
Operator Precedence
Using Functions
Mathematical Functions
Built-in Aggregate Functions
Aggregate Expressions
Positional Parameters
Debugging SQL Queries
Included Clients
psql: Advanced Topics
PgAccess: A Graphical Client
5. Advanced PostgreSQL Features
Extending PostgreSQL
Creating New Data Types
Creating New Operators
Creating New Functions
Using an Index
Index Types
Multi Column Indices
Unique Indices
Functional Indices
Primary Keys
Creation and Insertion
Modifications and Updates
Column Constraints
Table Constraints
Destroying a Constraint
Creating a Trigger
Advanced Trigger Functions
Destroying a Trigger
Creating a Sequence
Destroying a Sequence
What is Replication?
Obtaining the eR Server
Configuring the eR Server
Replication Testing
III. Administrating PostgreSQL
6. Authentication and Encryption
Client Authentication
Password Authentication
The pg_hba.conf File
Authentication Failures
Encrypting sessions
Encryption options
Configuring and Using Stunnel
7. Database Management
Creating a Database
Accessing the Database
Removing a Database
Maintaining a Database
Using Vacuum
Using Vacuumdb
Backing up and Restoring Data
PostgreSQL Dump
Large Objects
Restoring the Dump
Backing up the File System
Remote backups and restores
8. User Management
Adding users
From the command line
From the psql Application
User Attributes
IV. Programming with PostgreSQL
9. PL/pgSQL
Adding PL/pgSQL to your Database
Step1: Log into the database
Step 2: Using createlang to add PL/pgSQL
Step 3: Start programming in PL/pgSQL
Creating Functions
Code Blocks
Returning from a Function
Exception Handling
Control Structures
IF statements
Handling RECORDS
Iterating Through Records
PL/pgSQL and Triggers
10. JDBC
Getting Set Up
Building The Driver
Using the built driver
Basic JDBC Syntax
Basic JDBC
Advanced JDBC
Issues specific to PostgreSQL and JDBC
List of Tables
2-1. Configuration Options
3-1. An Example SQL Table
3-2. Fundamental PostgreSQL Commands
3-3. PostgreSQL Supported C-Style Escape Sequences
3-4. Punctuation Symbols
3-5. Fundamental PostgreSQL Operators
3-6. A Simple SQL Query
3-7. UPDATE Example: The SET Clause
3-8. UPDATE Example: The WHERE Clause
3-9. PostgreSQL Supported Data Types
3-10. Supported True or False Constants
3-11. Character Types
3-12. Numeric Types Overview
3-13. Default Numeric Type
3-14. Date and Time Types
3-15. Valid Date Formats
3-16. Month Abbreviations
3-17. Day of the Week Abbreviations
3-18. Date Output Formats
3-19. Extended Date Output Formats
3-20. Valid Time Formats
3-21. Valid Time Zone Formats
3-22. Some Valid Timestamp Formats
3-23. Interval Formats
3-24. Date and Time Constants
3-25. Geometric Types
3-26. System Columns
3-27. The Authors Table
3-28. The Subjects Table
4-1. CREATE TABLE Syntax
4-2. The "books" Table
4-3. ALTER TABLE Syntax
4-4. INSERT INTO ... VALUES Syntax
4-5. Author Table Data Values
4-6. Publisher's Table Specifications
4-7. CREATE VIEW Syntax
4-8. Mathematical operators
4-9. Comparison Operators
4-10. Bit String Operators
4-11. The AND and OR Operators
4-12. The NOT Operator
4-13. Operator Precedence
4-14. Mathematical Functions in PostgreSQL
4-15. Trigonometric Functions
4-16. Aggregate Functions
4-17. Defined % Substitution Strings
5-1. Invoice Table
5-2. Operators which require B-tree
5-3. Operators which require R-tree
5-4. Shipped Orders
5-5. Primary/Unique Difference
5-6. Author Table
5-7. Inheritance Options
5-8. Fixed Week Array
5-9. One-Dimensional Phone Number Array
5-10. Two-Dimensional Address Array
5-11. Create Table Defaults
8-1. Book Town Employees
List of Figures
4-1. PgAccess
List of Examples
2-1. Verifying GNU Make
2-2. Verifying GCC
2-3. Verifying Disk Space
2-4. Verifying gzip and tar
2-5. Unpacking the PostgreSQL Source Package
2-6. Compiling the Source with GNU Make
2-7. Making Regression Tests
3-1. Spaces and Newlines
3-2. Keywords and Commands
3-3. Bending Rules
3-4. Using String Constants
3-5. Multi-line String Constants
3-6. Using Bit String Constants
3-7. Using Integer Constants
3-8. Valid Floating Point Values
3-9. The Difference Between true and 'true'
3-10. Operators in Statements
3-11. Single-line comments
3-12. Multi-line comments
3-13. Example SQL Query
3-14. A SQL Update
3-15. Using Type Conversion Functions
3-16. Observing NULL Values
3-17. Using NULL Values
3-18. Simple Boolean Table
3-19. Checking Boolean Values
3-20. Implying Boolean 'true'
3-21. Checking for 'false' Boolean Values
3-22. Correcting Null Values
3-23. Casting double precision to real
3-24. Avoiding Overflow Errors
3-25. A numeric Alternative to money
3-26. Using the serial Data Type
3-27. Setting Date Formats
3-28. Interpretting Interval Formats
3-29. Comparing now to current
3-30. Differentiating Rows via the OID
4-1. Setting System Path for psql
4-2. Listing psql Slash Commands
4-3. The psql Prompt
4-4. Entering Statements into psql
4-5. Leaving end-characters open
4-6. Creating the "books" Table
4-7. Adding a Column
4-8. Altering Column Defaults
4-9. Re-naming a Table
4-10. Re-naming a Column
4-11. Adding a Foreign Key to a Table
4-12. Changing Table Ownership
4-13. Re-structuring a Table with CREATE TABLE AS
4-14. Re-structuring a Table with CREATE TABLE and INSERT INTO
4-15. Inserting New Values into the "books" Table
4-16. Changing the Order of Target Columns
4-17. Inserting Values from Another Table
4-18. Author Table
4-19. Select using Boolean Operators
4-20. Publisher Table
4-21. Joining Tables
4-22. Using the GROUP BY Clause
4-23. Using the DISTINCT Clause
4-24. Using ORDER BY
4-25. Using the LIMIT Clause
4-26. The LIMIT and OFFSET
4-27. Using Case Statements
4-28. Sub-query using Equal
4-29. Sub-queries Using IN
4-30. A Nested Select
4-31. Using UPDATE
4-32. Using UPDATE on Several Columns
4-33. Using DELETE
4-34. The inv_totals View
4-35. Aggregating View
4-36. Correct Operator Usage
4-37. Incorrect Operator Usage
4-38. Using Mathematical Operators with the AS Clause
4-39. Using Comparison Operators
4-40. Using Comparison Operators
4-41. Using BETWEEN
4-42. Operator Equivalents to BETWEEN
4-43. Comparisons Using IS NULL
4-44. Comparisons Equal to NULL
4-45. Using the random() and round() Functions
4-46. Using the average() Function
4-47. Creating a Function
4-48. Removing a Function
4-49. Using the \o Slash Command
4-50. Inserting a File into the Current Buffer
4-51. Setting a Variable
4-52. The Variable List
4-53. Using Interpolation During an INSERT
4-54. Using Interpolation During a SELECT
4-55. Reading from a File into a Variable
4-56. Using a Variable in an INSERT
4-57. A Default PROMPT1
4-58. A Default PROMPT2
4-59. A Default PROMPT3
4-60. Setting the Prompt Variables
4-61. Setting the Prompt to Include Database Host, the Username, Port, and Current Username
4-62. Setting the Prompt to Include the Output of the Linux date Command, the Database Name, and Current Username
5-1. Creating an Index
5-2. Dropping an Index
5-3. RTREE index
5-4. Creating a Hash Index
5-5. Creating an Index
5-6. Using a Multi-Column Index
5-7. Creating a Simple Functional Index
5-8. Creating a Function
5-9. Creating a Complex Functional Index
5-10. Using Inheritance
5-11. Viewing Tables without Inherited Columns
5-12. Creating a Fixed Array
5-13. Inserting Values into a Fixed Array
5-14. Selecting an Array Position
5-15. Adding Multi-Dimensional Arrays to a Table
5-16. Updating Multi-Dimensional Arrays
5-17. Viewing Multi-Dimensional Arrays
5-18. Updating Multi-Dimensional Arrays
5-19. Viewing Consecutive Array Positions
5-20. Viewing Consecutive Array Positions in a Multi-Dimensional Array
5-21. Creating a Primary Key Constraint
5-22. Using a Constraint on an Existing Table
5-23. Creating a Table Constraint
5-24. Removing a Constraint
5-25. PL/pgSQL check_a_id() function
5-26. Creating a Trigger
5-27. PL/pgSQL inv_audit() function
5-28. Creating a Trigger
5-29. Dropping a Trigger - Syntax
5-30. Creating a Sequence
5-31. Viewing a Sequence
5-32. Incrementing a Sequence
5-33. Viewing the Current Value of a Sequence
5-34. Setting a Column to Default to a Sequence
5-35. Removing a Sequence
6-1. Using UPDATE to modify a users PostgreSQL password
6-2. Checking User Permissions on Tables
6-3. An invalid pg_hba.conf host record
6-4. A valid pg_hba.conf comment
6-5. Valid pg_hba.conf entry with spaces
6-6. A valid pg_hba.conf entry with tabs
6-7. The local pg_hba.conf entry syntax.
6-8. The host pg_hba.conf entry syntax.
6-9. The hostssl pg_hba.conf entry syntax.
6-10. Single host entry.
6-11. Example of a rejection entry
6-12. Single host, single database entry.
6-13. Small network connection entry
7-1. Creating a Database
7-2. Vacuuming a Table
7-3. Vacuuming a Database
7-4. Vacuuming a Remote Database
8-1. Creating a Postgres Superuser
8-2. Creating a User with a Password
8-3. Viewing User Attributes
8-4. Modifying a User Attribute
8-5. Creating a Group
8-6. Modifying a Group
8-7. Viewing group attributes
8-8. Dropping a Group - Syntax
8-9. Granting Group Privileges
8-10. Creating a View
8-11. Granting View Privileges to a Group
8-12. Removing Privileges
9-1. Skeletal PL/pgSQL function
9-2. Mixed case PL/pgSQL Block
9-3. Spanning Over Multiple Lines in PL/pgSQL
9-4. Sub blocks in PL/pgSQL Block
9-5. PL/pgSQL Comments
9-6. Commenting with no Purpose
9-7. Commenting with a Purpose
9-8. Scope Violation
9-9. Correct usage of Scope
9-10. Declaring a Variable in PL/pgSQL
9-11. Assigning Default Values in PL/pgSQL
9-12. PL/pgSQL Assignment Statements
9-13. Returning from a PL/pgSQL Function
9-14. PL/pgSQL avg3() Function
9-15. Concatenating Text
9-16. PL/pgSQL Aliases
9-17. PL/pgSQL RAISE Message levels
9-18. Using SELECT Expressions in PL/pgSQL
9-19. The PL/pgSQL %ROWTYPE Variable
9-20. PL/pgSQL Function Using If
9-21. PL/pgSQL Function Using IF ELSE
9-22. PL/pgSQL Function Using ELSE IF
9-23. PL/pgSQL Loop
9-24. PL/pgSQL While Loop
9-25. PL/pgSQL For Loop
9-26. Using a RECORD in PL/pgSQL
9-27. The FOUND keyword in PL/pgSQL
9-28. Using IS NULL in PL/pgSQL
9-29. PL/pgSQL check_a_id() Function used by a Trigger
9-30. The if_author_added Trigger
9-31. PL/pgSQL inv_track Function used by a Trigger
9-32. The audit_track Trigger