PostgreSQL: The definitive guide

Joshua Drake

Command Prompt, Inc.

Copyright © 2005 Joshua D. Drake

Copyright (c) 2005 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
1. Who Is the Intended Audience?
2. Structure of This Book
3. Platform and Version Used
4. Conventions Used in This Book
5. Acknowledgments
6. Comments and Questions
1. What is PostgreSQL?
1.1. Open Source Free Version
1.1.1. Commercial PostgreSQL Distributions
1.1.2. Pervasive
1.1.3. Open Source Versus Commercial Products
1.1.4. The Bottom Line
1.1.5. Commercial Support
1.1.6. Community Support
1.2. PostgreSQL Feature Set
I. Installation, Configuration and Management
2. Getting Started
2.1. Choosing the right hardware
2.2. Preparing for Source Installation
2.3. 7 Steps to Installing PostgreSQL from source on Linux
2.4. Starting and Stopping PostgreSQL
2.5. Initializing the Filesystem
2.6. Creating and Removing a Database
3. Configuration of PostgreSQL
3.1. The postgresql.conf
3.2. Authentication and Encryption
3.3. The pg_ident.conf file
3.4. Encrypting sessions
4. Database Management
4.1. Maintaining a Database
4.2. Backing Up and Restoring Data
II. Using PostgreSQL
5. Understanding SQL
5.1. Introduction to SQL
5.2. Introduction to Relational Databases
5.3. SQL Statements
5.4. Data Types
5.5. Tables in PostgreSQL
6. Using SQL with PostgreSQL
6.1. Introduction to psql
6.2. Using Tables
6.3. Adding Data with INSERT and COPY
6.4. Retrieving Rows with SELECT
6.5. Modifying Rows with UPDATE
6.6. Removing Rows with DELETE
6.7. Using Sub-Queries
6.8. Using Views
6.9. Further SQL Application
7. Operators and Functions
7.1. Operators
7.2. Functions
8. PostgreSQL Clients
8.1. The psql Client: Advanced Topics
8.2. PgAccess: A Graphical Client
9. Advanced Features
9.1. Indices
9.2. Advanced Table Techniques
9.3. Arrays
9.4. Automating Common Routines
9.5. Transactions and Cursors
9.6. Extending PostgreSQL
III. Administrating PostgreSQL
10. User and Group Management
10.1. Managing Users
10.2. Managing Groups
10.3. Granting Privileges
IV. Programming with PostgreSQL
11. PL/pgSQL
11.1. Adding PL/pgSQL to your Database
11.2. Language Structure
11.3. Using Variables
11.4. Controlling Program Flow
11.5. PL/pgSQL and Triggers
12. JDBC
12.1. Building the PostgreSQL JDBC Driver
12.2. Using the PostgreSQL Driver
12.3. Using JDBC
12.4. Issues Specific to PostgreSQL and JDBC
VI. Appendixes
A. Multibyte Encoding Types
B. Binary COPY Format
B.1. The Header
B.2. Tuples
B.3. Trailer
C. Internal psql Variables
List of Tables
3-1.
3-2. The pg_shadow table
5-1. An example SQL table
5-2. Fundamental PostgreSQL commands
5-3. PostgreSQL supported C-style escape sequences
5-4. Floating-point representations
5-5. Punctuation Symbols
5-6. Fundamental PostgreSQL operators
5-7. A simple SQL query
5-8. UPDATE example: the SET clause
5-9. UPDATE example: the WHERE clause
5-10. PostgreSQL supported data types
5-11. Supported true or false constants
5-12. Character types
5-13. Numeric types overview
5-14. Date and time types
5-15. Valid date formats
5-16. Month abbreviations
5-17. Day of the week abbreviations
5-18. Date output formats
5-19. Extended date output formats
5-20. Valid time formats
5-21. Valid time zone formats
5-22. Some valid timestamp formats
5-23. Date and time constants
5-24. Geometric types
5-25. System columns
5-26. The authors table
5-27. The subjects table
6-1. The shipments table
7-1. Basic Character String Operators
7-2. Regular expression comparison operators
7-3. Regular expression symbols
7-4. Mathematical operators
7-5. Comparison operators
7-6. Bit-string operators
7-7. The AND, OR, and NOT operators
7-8. Operator precedence
7-9. Mathematical functions in PostgreSQL
7-10. Character string functions
7-11. Date and time functions
7-12. Timestamp and interval units
7-13. Type conversion functions
7-14. Numeric conversion formatting characters
7-15. Timestamp conversion formatting characters
7-16. Aggregate functions
8-1. Default PROMPT settings
8-2. Prompt substitution characters
9-1. Sequence attributes
9-2. The shipments table
9-3. The pg_trigger 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
A-1. Multibyte Encoding Types
List of Figures
8-1. PgAccess application window
8-2. The PgAccess Users tab
8-3. The PgAccess Create new table dialog box
8-4. The PgAccess Visual query designer
8-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 make install command
2-11. The /etc/ld.so.conf file
2-12. Starting PostgreSQL with pg_ctl
2-13. Stopping PostgreSQL with pg_ctl
2-14. Restarting PostgreSQL with pg_ctl
2-15. Checking status with pg_ctl
2-16. Copying the linux script
2-17. Making the linux script executable
2-18. Starting PostgreSQL with the SysV script
2-19. Starting PostgreSQL with service command
2-20. Checking status with postgresql script
2-21. Using the postmaster -P option
2-22. Initializing a New Database Cluster
2-23.
2-24. Checking usecreatedb rights
2-25. Creating a database
2-26. Using the createdb application
2-27. Using DROP DATABASE
2-28. Using the dropdb command
3-1. Using the data_directory option
3-2. Using pg_ctl with the data_directory directive
3-3. unix_socket_permission permission examples
3-4. Create a user with the db_user_namespace option
3-5. Knowing the max_fsm_pages needed
3-6. Compiling and using test_fsync
3-7. Watching for the checkpoint notice.
3-8. Using cp for to archive segments
3-9. Using set to disable/enable query tuning options.
3-10. Counting from clauses
3-11. Showing the row count mismatch
3-12. Different logfile data formats.
3-13. Debug2 output
3-14. log_statement
3-15. log_line_prefix
3-16. NULLs when tranform is on
3-17. A simple pg_hba.conf file
3-18. Checking user permissions
3-19. A valid pg_hba.conf entry with spaces and tabs
3-20. Valid pg_hba.conf comments
3-21. Host entry syntax
3-22. Single host entry
3-23. Rejection entry
3-24. Single host, single database entry
3-25. Single host, single database entry with CIDR style
3-26. Small network connection entry
3-27. Small network connection entry using CIDR style
3-28. Larger network connection entry
3-29. Larger network connection entry with CIDR style
3-30. Using an external file for user lists
3-31. An ident configuration in pg_hba.conf
3-32. A pg_ident.conf configuration
3-33. A sameuser configuration
3-34. Turning on SSL
3-35. Creating a self signed certificate
3-36. Removing the PEM pass phrase
3-37. Unlocking your self signed certificate and key
3-38. Making an SSH tunnel to PostgreSQL
4-1. Executing a VACUUM with the option table parameter.
4-2. Executing a VACUUM VERBOSE
4-3. Using vacuumdb on all databases
4-4. Using vacuumdb on a remote database
4-5. Commenting the books table
4-6. Removing the books table comment
4-7. Retrieving a comment
4-8. Insert statement with column inserts
4-9. Using pg_dump
4-10. Using pg_dump remotely
4-11. Using pg_dumpall
4-12. Recreating the booktown database
4-13. Restore with pg_restore
4-14. Backing up the PostgreSQL filesystem
5-1. Spaces and newlines
5-2. Keywords and commands
5-3. Bending rules
5-4. Using string constants
5-5. Multiline string constants
5-6. Using bit string constants
5-7. Using integer constants
5-8. Valid floating-point values
5-9. The difference between true and 'true'
5-10. Operators in statements
5-11. Single-line comments
5-12. Multiline comments
5-13. Example SQL query
5-14. A SQL update
5-15. Observing NULL values
5-16. Using NULL values
5-17. Simple Boolean table
5-18. Checking Boolean values
5-19. Implying Boolean 'true'
5-20. Checking for 'false' Boolean values
5-21. Correcting Null values
5-22. Avoiding overflow errors
5-23. A numeric alternative to money
5-24. Using the serial data type
5-25. Accomplishing the same goal manually
5-26. Setting date formats
5-27. Interpreting interval formats
5-28. Using the current and now constants
5-29. Comparing now to current
5-30. Using Type Conversion Functions
5-31. Differentiating rows via the OID
6-1. Setting system path for psql
6-2. Listing psql slash commands
6-3. Entering statements into psql
6-4. Leaving end-characters open
6-5. Setting the EDITOR variable
6-6. Creating the books table
6-7. The \d command's output
6-8. Adding a column
6-9. Altering column defaults
6-10. Renaming a table
6-11. Renaming a column
6-12. Adding constraints to a table
6-13. Changing table ownership
6-14. Restructuring a table with CREATE TABLE AS
6-15. Restructuring a table with CREATE TABLE and INSERT INTO
6-16. Inserting new values into the books table
6-17. Changing the order of target columns
6-18. Inserting values from another table
6-19. An example ASCII copy file
6-20. Copying an ASCII file
6-21. Copying a binary file
6-22. Copying the books table to an ASCII file
6-23. Selecting all from the books table
6-24. Re-Ordering columns
6-25. Using expressions and constants
6-26. Using the AS clause with expressions and constants
6-27. Selecting from multiple table sources
6-28. Selecting from a sub-query
6-29. Aliasing FROM sources
6-30. Aliasing columns
6-31. Using DISTINCT
6-32. A simple WHERE clause
6-33. Combining conditions in the WHERE clause
6-34. Grouping WHERE conditions with parentheses
6-35. A simple CROSS JOIN
6-36. Comparing INNER JOIN to WHERE
6-37. The NATURAL and USING clauses
6-38. Inner joins versus outer joins
6-39. Joining many data sources
6-40. Using GROUP BY
6-41. Using the HAVING clause
6-42. Using ORDER BY
6-43. Using ORDER BY with multiple expressions
6-44. Using DISTINCT with ORDER BY
6-45. Using LIMIT and OFFSET
6-46. Using UNION
6-47. Using INTERSECT
6-48. Using EXCEPT
6-49. Comparing sub-query result sets
6-50. Using case expressions in statements
6-51. Using case expressions with sub-queries
6-52. Using SELECT INTO
6-53. A simple UPDATE
6-54. Updating entire columns
6-55. Using UPDATE on several columns
6-56. Using UPDATE with several sources
6-57. Deleting rows from a table
6-58. Deleting all table rows
6-59. A simple sub-query
6-60. A sub-query using IN
6-61. A multi-column sub-query using IN
6-62. Creating a view
6-63. Using a view
7-1. Correct operator usage
7-2. Incorrect operator usage
7-3. Comparing strings
7-4. Concatenating strings
7-5. An example regular expression
7-6. A Simple Regular Expression Comparison
7-7. A more involved regular expression comparison
7-8. A Complicated Regular Expression Comparison
7-9. Using Mathematical Operators
7-10. Using comparison operators
7-11. Using BETWEEN
7-12. Operator equivalents to BETWEEN
7-13. Shifting bit strings
7-14. Combining comparisons with Boolean operators
7-15. Comparisons using IS NULL
7-16. Comparisons equal to NULL
7-17. Using operators with NULL values
7-18. Operator precedence
7-19. Using aggregate expressions
8-1. Inserting a file into the current buffer
8-2. Setting a variable
8-3. The variable list
8-4. Using interpolation during an INSERT
8-5. Reading from a file into a variable
8-6. Using a variable in an INSERT
8-7. Setting the prompt variables
8-8. Customizing the prompt with database host, port, and username
8-9. Customizing the prompt with the date, database name, and username
9-1. Creating an index
9-2. Implicit index creation
9-3. Creating a unique index
9-4. Specifying an index type
9-5. Creating a functional index
9-6. Dropping an index
9-7. Creating a table with column constraints
9-8. Creating a table with table constraints
9-9. Adding a constraint to an existing table
9-10. Removing a constraint
9-11. Creating a child table
9-12. Inserting into a child table
9-13. Selecting with inheritance
9-14. Modifying parent and child tables
9-15. Modifying parent tables with ONLY
9-16. Creating a table with an array column
9-17. Creating a table with a multidimensional array column
9-18. Inserting array constants
9-19. Inserting values into multidimensional arrays
9-20. Selecting entire array values
9-21. Selecting array values with subscripts
9-22. Avoiding NULL values in arrays
9-23. Selecting From a Multi-Dimensional Array
9-24. Selecting array values with slices
9-25. Using array_dims( )
9-26. Completely modifying an array
9-27. Modifying an array subscript
9-28. Creating a sequence
9-29. Viewing a sequence
9-30. Incrementing a sequence
9-31. Using currval( )
9-32. Setting a sequence value
9-33. Removing a sequence
9-34. Checking sequence dependencies
9-35. Creating the check_shipment trigger
9-36. Dropping a trigger
9-37. Selecting a trigger's assigned table
9-38. Beginning a transaction
9-39. Committing a transaction
9-40. Rolling back a transaction
9-41. Recovering from the abort state
9-42. Declaring a cursor
9-43. Fetching rows from a cursor
9-44. Moving a cursor
9-45. Closing a cursor
9-46. Creating a SQL function
9-47. Using a SQL function
9-48. is_zero.c, a simple C function
9-49. Creating a C function
9-50. Overloading a C function
9-51. Using a C function
9-52. Dropping a function
9-53. Creating a user-defined operator
9-54. Using a user-defined operator
9-55. Overloading a user-defined operator
9-56. Using an overloaded operator
9-57. Dropping an operator
9-58. Dropping an overloaded operator
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
  Next
  Preface