Command Prompt, Inc.
Practical PostgreSQL

Practical PostgreSQL

Practical PostgreSQL, by Command Prompt, Inc.
Now Shipping!

John Worsley

Command Prompt, Inc.

Joshua Drake

Command Prompt, Inc.

Edited by

Andrew Brookins

Michael Holloway

Copyright © 2001-2002 by Commandprompt, 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 http://www.opencontent.org/openpub/).

'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
Preface
Who Is the Intended Audience?
Structure of This Book
Platform and Version Used
What Is Included on the CD?
Conventions Used in This Book
Acknowledgments
Comments and Questions
I. Introduction and Installation
1. What is PostgreSQL?
Open Source Free Version
PostgreSQL Feature Set
Where to Proceed from Here
2. Installing PostgreSQL
Preparing for Installation
10 Steps to Installing PostgreSQL
II. Using PostgreSQL
3. Understanding SQL
Introduction to SQL
Introduction to Relational Databases
SQL Statements
Data Types
Tables in PostgreSQL
4. Using SQL with PostgreSQL
Introduction to psql
Using Tables
Adding Data with INSERT and COPY
Retrieving Rows with SELECT
Modifying Rows with UPDATE
Removing Rows with DELETE
Using Sub-Queries
Using Views
Further SQL Application
5. Operators and Functions
Operators
Functions
6. PostgreSQL Clients
The psql Client: Advanced Topics
PgAccess: A Graphical Client
7. Advanced Features
Indices
Advanced Table Techniques
Arrays
Automating Common Routines
Transactions and Cursors
Extending PostgreSQL
III. Administrating PostgreSQL
8. Authentication and Encryption
Client Authentication
Encrypting sessions
9. Database Management
Starting and Stopping PostgreSQL
Initializing the Filesystem
Creating and Removing a Database
Maintaining a Database
Backing Up and Restoring Data
10. User and Group Management
Managing Users
Managing Groups
Granting Privileges
IV. Programming with PostgreSQL
11. PL/pgSQL
Adding PL/pgSQL to your Database
Language Structure
Using Variables
Controlling Program Flow
PL/pgSQL and Triggers
12. JDBC
Building the PostgreSQL JDBC Driver
Using the PostgreSQL Driver
Using JDBC
Issues Specific to PostgreSQL and JDBC
13. LXP
Why Use LXP?
Core Features
Installing and Configuring LXP
Understanding LXP Mark-Up
LXP Variables and Objects
Using Cookies with LXP
Tag Parsing
Branching Logic
Loop Iteration
Content Inclusion
Displaying Foreign Tags with <xtag>
V. PostgreSQL Command Reference
14. PostgreSQL Command Reference
ABORT  -- Rolls back changes made during a transaction block.
ALTER GROUP  -- Modifies the structure of a user group.
ALTER TABLE  -- Modifies table and column attributes.
ALTER USER  -- Modifies user properties and permissions.
BEGIN  -- Starts a chained-mode transaction block.
CLOSE  -- Closes a previously defined cursor object.
CLUSTER  -- Provides the backend server with clustering information about a table.
COMMENT  -- Adds a comment to an object within the database.
COMMIT  -- Ends the current transaction block and finalizes changes made within it.
COPY  -- Copies data between files and tables.
CREATE AGGREGATE  -- Defines a new aggregate function within the database.
CREATE DATABASE  -- Creates a new database in PostgreSQL.
CREATE FUNCTION  -- Defines a new function within the database.
CREATE GROUP  -- Creates a new PostgreSQL group within the database.
CREATE INDEX  -- Places an index on a table.
CREATE LANGUAGE  -- Defines a new language to be used by functions.
CREATE OPERATOR  -- Defines a new operator within the database.
CREATE RULE  -- Defines a new rule on a table.
CREATE SEQUENCE  -- Creates a new sequence number generator.
CREATE TABLE  -- Creates a new table.
CREATE TABLE AS  -- Creates a new table built from data retrieved by a SELECT.
CREATE TRIGGER  -- Creates a new trigger.
CREATE TYPE  -- Defines a new data type for use in the database.
CREATE USER  -- Creates a new PostgreSQL database user.
CREATE VIEW  -- Creates a view on a table.
CURRENT_DATE  -- Returns the current date.
CURRENT_TIME  -- Returns the current time.
CURRENT_TIMESTAMP  -- Returns the current date and time.
CURRENT_USER  -- Returns the current database username.
DECLARE  -- Defines a new cursor.
DELETE  -- Removes rows from a table.
DROP AGGREGATE  -- Removes an aggregate function from a database.
DROP DATABASE  -- Removes a database from the system.
DROP FUNCTION  -- Removes a user-defined function.
DROP GROUP  -- Removes a user group from the database.
DROP INDEX  -- Removes an index from a database.
DROP LANGUAGE  -- Removes a procedural language from a database.
DROP OPERATOR  -- Removes an operator from the database.
DROP RULE  -- Removes a rule from a database.
DROP SEQUENCE  -- Removes a sequence from a database.
DROP TABLE  -- Removes a table from a database.
DROP TRIGGER  -- Removes a trigger definition from a database.
DROP TYPE  -- Removes a type from the system catalogs.
DROP USER  -- Removes a PostgreSQL user.
DROP VIEW  -- Removes an existing view from a database.
END  -- Ends the current transaction block and finalizes its modifications.
EXPLAIN  -- Shows the statement execution plan for a supplied query.
FETCH  -- Retrieves rows from a cursor.
GRANT  -- Grants access privileges to a user, a group, or to all users in the database.
INSERT  -- Inserts new rows into a table.
LISTEN  -- Listen for a notification event.
LOAD  -- Dynamically loads object files into a database.
LOCK  -- Locks a table within a transaction.
MOVE  -- Repositions a cursor to another row.
NOTIFY  -- Signals all backends that are listening for the specified notify event.
REINDEX  -- Rebuilds indices on tables.
RESET  -- Restores runtime variables to their default settings.
REVOKE  -- Revokes access privileges from a user, a group, or all users.
ROLLBACK  -- Aborts the current transaction block and abandons any modifications it would have made.
SELECT  -- Retrieves rows from a table or view.
SELECT INTO  -- Construct a new table from the results of a SELECT.
SET  -- Set runtime variables.
SET CONSTRAINTS  -- Sets the constraint mode for the current transaction block.
SET TRANSACTION  -- Sets the transaction isolation level for the current transaction block.
SHOW  -- Displays the values of runtime variables.
TRUNCATE  -- Empties the contents of a table.
UNLISTEN  -- Stops the backend process from listening for a notification event.
UPDATE  -- Modifies the values of column data within a table.
VACUUM  -- Cleans and analyzes a database.
VI. Appendixes
A. Multibyte Encoding Types
B. Backend Options for postgres
C. Binary COPY Format
The Header
Tuples
Trailer
D. Internal psql Variables
List of Tables
3-1. An example SQL table
3-2. Fundamental PostgreSQL commands
3-3. PostgreSQL supported C-style escape sequences
3-4. Floating-point representations
3-5. Punctuation Symbols
3-6. Fundamental PostgreSQL operators
3-7. A simple SQL query
3-8. UPDATE example: the SET clause
3-9. UPDATE example: the WHERE clause
3-10. PostgreSQL supported data types
3-11. Supported true or false constants
3-12. Character types
3-13. Numeric types overview
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. Date and time constants
3-24. Geometric types
3-25. System columns
3-26. The authors table
3-27. The subjects table
4-1. The shipments table
5-1. Basic Character String Operators
5-2. Regular expression comparison operators
5-3. Regular expression symbols
5-4. Mathematical operators
5-5. Comparison operators
5-6. Bit-string operators
5-7. The AND, OR, and NOT operators
5-8. Operator precedence
5-9. Mathematical functions in PostgreSQL
5-10. Character string functions
5-11. Date and time functions
5-12. Timestamp and interval units
5-13. Type conversion functions
5-14. Numeric conversion formatting characters
5-15. Timestamp conversion formatting characters
5-16. Aggregate functions
6-1. Default PROMPT settings
6-2. Prompt substitution characters
7-1. Sequence attributes
7-2. The shipments table
7-3. The pg_trigger table
8-1. The pg_shadow table
10-1. The pg_shadow table
10-2. PostgreSQL ACL privileges
10-3. The stock table
11-1. Possible level values
11-2. Trigger function variables
13-1. LXP inclusion methods
13-2. Database Connection Attributes
14-1. Operator/index correspondence
A-1. Multibyte Encoding Types
List of Figures
6-1. PgAccess application window
6-2. The PgAccess Users tab
6-3. The PgAccess Create new table dialog box
6-4. The PgAccess Visual query designer
6-5. The Function dialog box
List of Examples
2-1. Verifying GNU make
2-2. Verifying GCC
2-3. Verifying gzip and tar
2-4. Verifying disk space
2-5. Adding the postgres User
2-6. Unpacking the PostgreSQL source package
2-7. Compiling the source with GNU make
2-8. Making regression tests
2-9. Regression check output
2-10. The gmake install command
2-11. Installing Perl and Python modules manually
2-12. Installing all headers
2-13. Setting LD_LIBRARY_PATH in a bash shell
2-14. Setting LD_LIBRARY_PATH in csh and tcsh
2-15. Initializing the database
2-16. Output from initdb
2-17. Running postmaster in the foreground
2-18. Running postmaster in the background
2-19. Copying the linux script
2-20. Making the linux script executable
2-21. Starting PostgreSQL with the SysV script
2-22. Creating a database
2-23. Accessing a database with psql
2-24. Querying a system table
3-1. Spaces and newlines
3-2. Keywords and commands
3-3. Bending rules
3-4. Using string constants
3-5. Multiline 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. Multiline comments
3-13. Example SQL query
3-14. A SQL update
3-15. Observing NULL values
3-16. Using NULL values
3-17. Simple Boolean table
3-18. Checking Boolean values
3-19. Implying Boolean 'true'
3-20. Checking for 'false' Boolean values
3-21. Correcting Null values
3-22. Avoiding overflow errors
3-23. A numeric alternative to money
3-24. Using the serial data type
3-25. Accomplishing the same goal manually
3-26. Setting date formats
3-27. Interpreting interval formats
3-28. Using the current and now constants
3-29. Comparing now to current
3-30. Using Type Conversion Functions
3-31. Differentiating rows via the OID
4-1. Setting system path for psql
4-2. Listing psql slash commands
4-3. Entering statements into psql
4-4. Leaving end-characters open
4-5. Setting the EDITOR variable
4-6. Creating the books table
4-7. The \d command's output
4-8. Adding a column
4-9. Altering column defaults
4-10. Renaming a table
4-11. Renaming a column
4-12. Adding constraints to a table
4-13. Changing table ownership
4-14. Restructuring a table with CREATE TABLE AS
4-15. Restructuring a table with CREATE TABLE and INSERT INTO
4-16. Inserting new values into the books table
4-17. Changing the order of target columns
4-18. Inserting values from another table
4-19. An example ASCII copy file
4-20. Copying an ASCII file
4-21. Copying a binary file
4-22. Copying the books table to an ASCII file
4-23. Selecting all from the books table
4-24. Re-Ordering columns
4-25. Using expressions and constants
4-26. Using the AS clause with expressions and constants
4-27. Selecting from multiple table sources
4-28. Selecting from a sub-query
4-29. Aliasing FROM sources
4-30. Aliasing columns
4-31. Using DISTINCT
4-32. A simple WHERE clause
4-33. Combining conditions in the WHERE clause
4-34. Grouping WHERE conditions with parentheses
4-35. A simple CROSS JOIN
4-36. Comparing INNER JOIN to WHERE
4-37. The NATURAL and USING clauses
4-38. Inner joins versus outer joins
4-39. Joining many data sources
4-40. Using GROUP BY
4-41. Using the HAVING clause
4-42. Using ORDER BY
4-43. Using ORDER BY with multiple expressions
4-44. Using DISTINCT with ORDER BY
4-45. Using LIMIT and OFFSET
4-46. Using UNION
4-47. Using INTERSECT
4-48. Using EXCEPT
4-49. Comparing sub-query result sets
4-50. Using case expressions in statements
4-51. Using case expressions with sub-queries
4-52. Using SELECT INTO
4-53. A simple UPDATE
4-54. Updating entire columns
4-55. Using UPDATE on several columns
4-56. Using UPDATE with several sources
4-57. Deleting rows from a table
4-58. Deleting all table rows
4-59. A simple sub-query
4-60. A sub-query using IN
4-61. A multi-column sub-query using IN
4-62. Creating a view
4-63. Using a view
5-1. Correct operator usage
5-2. Incorrect operator usage
5-3. Comparing strings
5-4. Concatenating strings
5-5. An example regular expression
5-6. A Simple Regular Expression Comparison
5-7. A more involved regular expression comparison
5-8. A Complicated Regular Expression Comparison
5-9. Using Mathematical Operators
5-10. Using comparison operators
5-11. Using BETWEEN
5-12. Operator equivalents to BETWEEN
5-13. Shifting bit strings
5-14. Combining comparisons with Boolean operators
5-15. Comparisons using IS NULL
5-16. Comparisons equal to NULL
5-17. Using operators with NULL values
5-18. Operator precedence
5-19. Using aggregate expressions
6-1. Inserting a file into the current buffer
6-2. Setting a variable
6-3. The variable list
6-4. Using interpolation during an INSERT
6-5. Reading from a file into a variable
6-6. Using a variable in an INSERT
6-7. Setting the prompt variables
6-8. Customizing the prompt with database host, port, and username
6-9. Customizing the prompt with the date, database name, and username
7-1. Creating an index
7-2. Implicit index creation
7-3. Creating a unique index
7-4. Specifying an index type
7-5. Creating a functional index
7-6. Dropping an index
7-7. Creating a table with column constraints
7-8. Creating a table with table constraints
7-9. Adding a constraint to an existing table
7-10. Removing a constraint
7-11. Creating a child table
7-12. Inserting into a child table
7-13. Selecting with inheritance
7-14. Modifying parent and child tables
7-15. Modifying parent tables with ONLY
7-16. Creating a table with an array column
7-17. Creating a table with a multidimensional array column
7-18. Inserting array constants
7-19. Inserting values into multidimensional arrays
7-20. Selecting entire array values
7-21. Selecting array values with subscripts
7-22. Avoiding NULL values in arrays
7-23. Selecting From a Multi-Dimensional Array
7-24. Selecting array values with slices
7-25. Using array_dims( )
7-26. Completely modifying an array
7-27. Modifying an array subscript
7-28. Creating a sequence
7-29. Viewing a sequence
7-30. Incrementing a sequence
7-31. Using currval( )
7-32. Setting a sequence value
7-33. Removing a sequence
7-34. Checking sequence dependencies
7-35. Creating the check_shipment trigger
7-36. Dropping a trigger
7-37. Selecting a trigger's assigned table
7-38. Beginning a transaction
7-39. Committing a transaction
7-40. Rolling back a transaction
7-41. Recovering from the abort state
7-42. Declaring a cursor
7-43. Fetching rows from a cursor
7-44. Moving a cursor
7-45. Closing a cursor
7-46. Creating a SQL function
7-47. Using a SQL function
7-48. is_zero.c, a simple C function
7-49. Creating a C function
7-50. Overloading a C function
7-51. Using a C function
7-52. Dropping a function
7-53. Creating a user-defined operator
7-54. Using a user-defined operator
7-55. Overloading a user-defined operator
7-56. Using an overloaded operator
7-57. Dropping an operator
7-58. Dropping an overloaded operator
8-1. A simple pg_hba.conf file
8-2. Checking user permissions
8-3. A valid pg_hba.conf entry with spaces and tabs
8-4. Valid pg_hba.conf comments
8-5. Host entry syntax
8-6. Single host entry
8-7. Rejection entry
8-8. Single host, single database entry
8-9. Small network connection entry
8-10. Larger network connection entry
8-11. An ident configuration in pg_hba.conf
8-12. A pg_ident.conf configuration
8-13. A sameuser configuration
8-14. Making an SSH tunnel to PostgreSQL
8-15. Using Stunnel remotely
8-16. Using Stunnel locally
8-17. An example inetd entry
8-18. An example xinetd entry
9-1. Starting PostgreSQL with pg_ctl
9-2. Stopping PostgreSQL with pg_ctl
9-3. Restarting PostgreSQL with pg_ctl
9-4. Checking status with pg_ctl
9-5. Starting PostgreSQL with service command
9-6. Checking status with postgresql script
9-7. Initializing a New Database Cluster
9-8. Initializing a Secondary Database Location
9-9. Checking usecreatedb rights
9-10. Creating a database
9-11. Using the createdb application
9-12. Using DROP DATABASE
9-13. Using the dropdb command
9-14. Using VACUUM on a table
9-15. Using VACUUM ANALYZE on a database
9-16. Using vacuumdb on all databases
9-17. Using vacuumdb on a remote database
9-18. Commenting the books table
9-19. Retrieving a comment
9-20. Using pg_dump
9-21. Using pg_dump remotely
9-22. Using pg_dumpall
9-23. Recreating the booktown database
9-24. Restore with pg_restore
9-25. Backing up the PostgreSQL filesystem
10-1. Creating a normal user
10-2. Creating a user with CREATEDB rights
10-3. Creating a superuser
10-4. Creating a user with createuser
10-5. Interactively creating a user with createuser
10-6. Resetting a password
10-7. Adding superuser rights
10-8. Removing superuser rights
10-9. Removing a user with DROP USER
10-10. Removing a user with dropuser
10-11. Creating a group
10-12. Verifying a group
10-13. Removing a group
10-14. Adding a user to a group
10-15. Verifying user addition
10-16. Removing a user from a group
10-17. Granting user privileges
10-18. Granting group privileges
10-19. Revoking rights
10-20. Controlling SELECT privileges with a view
10-21. Controlling SELECT
11-1. Creating the PL/pgSQL call handler
11-2. Adding PL/pgSQL with CREATE LANGUAGE
11-3. Using createlang as a database superuser
11-4. Explicitly passing a superuser account name to createlang
11-5. Structure of a PL/pgSQL code block
11-6. Using single-line comments
11-7. Using block comments
11-8. Using expressions
11-9. Output of a_ function( )
11-10. Using timestamp values correctly
11-11. Declaring a PL/pgSQL variable
11-12. Variable Declarations
11-13. Using variable declaration options
11-14. Default value assignment
11-15. Using the SELECT INTO statement
11-16. Result of the get_customer_id( ) function
11-17. Using SELECT INTO with multiple columns
11-18. Result of the get_customer_name( ) function
11-19. Using the FOUND boolean in get_customer_id( )
11-20. Result of the new get_customer_id( ) function
11-21. Function call examples
11-22. Directly using argument variables
11-23. Syntax of the ALIAS keyword
11-24. Using PL/pgSQL aliases
11-25. Result of the triple_price( ) function
11-26. Syntax of the RETURN statement
11-27. Declaring a variable using %TYPE
11-28. Using the %TYPE attribute
11-29. Results of the get_author( ) function
11-30. Using the %ROWTYPE attribute
11-31. Results of the new get_author( ) function
11-32. Returning a concatenated string
11-33. Assigning a concatenated value to a string
11-34. Syntax of an IF/THEN statement
11-35. Using the IF/THEN statement
11-36. Results of the stock_amount( ) function
11-37. Syntax of an IF/THEN/ELSE statement
11-38. Using the IF/THEN/ELSE statement
11-39. Results of the in_stock( ) function
11-40. Using the IF/THEN/ELSE/IF statement
11-41. Results of the books_by_subject() function
11-42. Using the basic loop
11-43. Result of the square_integer_loop( ) function
11-44. Using the WHILE loop
11-45. Using the FOR loop
11-46. Using the FOR loop with %ROWTYPE
11-47. Result of the extract_title() function
11-48. Using the RAISE statement
11-49. Results of the raise_test( ) function
11-50. Syntax of the PERFORM keyword
11-51. Using the PERFORM keyword
11-52. Creating trigger functions
11-53. The check_shipment_addition() PL/pgSQL trigger function
11-54. The check_shipment trigger
12-1. Class name lookup
12-2. A simple JDBC connection
12-3. A JDBC statement object
12-4. A simple JDBC select
12-5. A simple JDBC insert
12-6. A JDBC prepared statement
12-7. JDBC ResultSetMetaData
12-8. JDBC DatabaseMetaData
12-9. JDBC first row fetch
13-1. Installing LXP with lxpinstall.sh
13-2. Manually installing liblxp.so
13-3. Manually installing libpq.so.2.2
13-4. Manually installing lxp.conf
13-5. Configuring httpd.conf for LXP
13-6. A Simple LXP mark-up region
13-7. Displaying a cookie value
13-8. Substituting cookie values
13-9. Invalid variable substitution
13-10. Valid variable substitution
13-11. Using entity substitution
13-12. Using <varparser> to prepare SQL
13-13. Using the <if> tag
13-14. Using the <ifnot> tag
13-15. Using nested logic
13-16. Using ifcookie and ifnotcookie
13-17. Using the <else> tag
13-18. Using nested logic with <else> tags
13-19. A simple <for> loop
13-20. Handling array results with <for>
13-21. Including an LXP document
13-22. Including flat files
13-23. Including a token-delimited file
13-24. Including an XML file
13-25. Including an RDF file
13-26. Including other content types
13-27. Connecting to a non-default database
13-28. Including SQL content
13-29. Using SQL object variable values
13-30. Selecting SQL results into an LXP object
13-31. Using <xtag> for empty elements
13-32. Using nested <xtag> elements
Next
  Preface

Powered by Mammoth PostgreSQL
Copyright © 2000-2007 Command Prompt, Inc. All Rights Reserved. All trademarks property of their respective owners.