Cover image for Practical PostgreSQL

Practical PostgreSQL

Author John C. Worsley , Joshua D. Drake
Release Date: 2002/01/01
ISBN: 9781449309770
Topic:
0%
189
Chapters
0-1
Hours read
0k
Total Words
    Start Reading Now    
       Add to Wishlist       
View table of contents

Book Description

Arguably the most capable of all the open source databases, PostgreSQL is an object-relational database management system first developed in 1977 by the University of California at Berkeley. In spite of its long history, this robust database suffers from a lack of easy-to-use documentation. Practical PostgreSQL fills that void with a fast-paced guide to installation, configuration, and usage.



This comprehensive new volume shows you how to compile PostgreSQL from source, create a database, and configure PostgreSQL to accept client-server connections. It also covers the many advanced features, such as transactions, versioning, replication, and referential integrity that enable developers and DBAs to use PostgreSQL for serious business applications. The thorough introduction to PostgreSQL's PL/pgSQL programming language explains how you can use this very useful but under-documented feature to develop stored procedures and triggers. The book includes a complete command reference, and database administrators will appreciate the chapters on user management, database maintenance, and backup & recovery. With Practical PostgreSQL, you will discover quickly why this open source database is such a great open source alternative to proprietary products from Oracle, IBM, and Microsoft.

Table of Contents

  1. Practical PostgreSQL
  2. A Note Regarding Supplemental Files
  3. Preface
    1. Who Is the Intended Audience?
    2. Structure of This Book
    3. Platform and Version Used
    4. What Is Included on the CD?
    5. Conventions Used in This Book
    6. Acknowledgments
    7. Comments and Questions
  4. I. Introduction & Installation
    1. 1. What Is PostgreSQL?
      1. Open Source Free Version
        1. Commercial PostgreSQL Products
        2. Open Source Versus Commercial Products
        3. The Bottom Line
        4. Commercial Support
        5. Community Support
      2. PostgreSQL Feature Set
      3. Where to Proceed from Here
    2. 2. Installing PostgreSQL
      1. Preparing for Installation
        1. Required Software Packages
        2. Optional Packages
        3. Disk Space
      2. 10 Steps to Installing PostgreSQL
        1. Step 1: Creating the “postgres” User
        2. Step 2: Installing the PostgreSQL Source Package
        3. Step 3: Configuring the Source Tree
        4. Step 4: Compiling the Source
        5. Step 5: Regression Testing
        6. Step 6: Installing Compiled Programs and Libraries
        7. Step 7: Setting Environment Variables
        8. Step 8: Initializing and Starting PostgreSQL
        9. Step 9: Configuring the PostgreSQL SysV Script
        10. Step 10: Creating a Database
  5. II. Using PostgreSQL
    1. 3. Understanding SQL
      1. Introduction to SQL
        1. A Brief History of SQL
        2. SQL and Its Predecessors
        3. SQL Standards
      2. Introduction to Relational Databases
        1. Understanding Databases
        2. Understanding Tables
      3. SQL Statements
        1. The Anatomy of a SQL Statement
        2. Token Formatting Considerations
        3. Keywords and Identifiers
          1. Quoted identifiers
          2. When quotes are required
          3. Identifier validity
        4. Constants
          1. String constants
          2. Bit string constants
          3. Integer constants
          4. Floating-point constants
          5. Boolean constants
        5. Special Character Symbols
          1. Punctuation symbols
          2. Operator symbols
        6. Comments
        7. Putting It All Together
      4. Data Types
        1. NULL Values
        2. Boolean Values
        3. Character Types
        4. Numeric Types
          1. The numeric type
          2. The money type
          3. The serial type
        5. Date and Time Types
          1. Backward compatibility
          2. Date conventions
          3. Time conventions
          4. Timestamps
          5. Intervals
          6. Built-in date and time constants
        6. Geometric Types
        7. Arrays
          1. Arrays in tables
          2. Array constants
        8. Type Coercion
      5. Tables in PostgreSQL
        1. System Columns
        2. Object Identifiers
        3. Planning Ahead
    2. 4. Using SQL with PostgreSQL
      1. Introduction to psql
        1. Starting psql
        2. Introduction to psql Syntax
        3. Executing Queries
          1. Entering queries at the psql prompt
          2. Editing the query buffer
      2. Using Tables
        1. Creating Tables with CREATE TABLE
          1. CREATE TABLE syntax
          2. Creating an example table
          3. Examining a created table
        2. Altering Tables with ALTER TABLE
          1. Adding columns
          2. Setting and removing default values
          3. Renaming a table
          4. Renaming columns
          5. Adding constraints
          6. Changing ownership
        3. Restructuring Existing Tables
          1. Restructuring with CREATE TABLE AS
          2. Restructuring with CREATE TABLE and INSERT INTO
        4. Destroying Tables with DROP TABLE
      3. Adding Data with INSERT and COPY
        1. Inserting New Values
        2. Inserting Values from Other Tables with SELECT
        3. Copying Values from External Files with COPY
          1. Binary format
          2. The difference between COPY and copy
          3. COPY TO
          4. Copying WITH OIDS
      4. Retrieving Rows with SELECT
        1. A Simple SELECT
        2. Specifying Target Columns
        3. Expressions, Constants, and Aliases
        4. Selecting Sources with the FROM Clause
        5. Aliasing FROM Sources
        6. Removing Duplicate Rows with DISTINCT
        7. Qualifying with the WHERE Clause
        8. Joining Data Sets with JOIN
          1. Cross joins
          2. Inner and outer join syntax
          3. Inner joins
          4. Outer joins
          5. Intricate joins
        9. Grouping Rows with GROUP BY
        10. Sorting Rows with ORDER BY
        11. Setting Row Range with LIMIT and OFFSET
        12. Comparing Sets with UNION, INTERSECT and EXCEPT
        13. Using Case Expressions
        14. Creating Tables from Other Tables
      5. Modifying Rows with UPDATE
        1. Updating Entire Columns
        2. Updating Several Columns
        3. Updating from Several Sources
      6. Removing Rows with DELETE
      7. Using Sub-Queries
      8. Using Views
        1. Creating a View
        2. Applying Views
        3. Destroying a view
      9. Further SQL Application
    3. 5. Operators and Functions
      1. Operators
        1. Using Operators
        2. Character String Operators
          1. Basic comparison
          2. String concatenation
          3. Regular expression matching operators
        3. Numeric Operators
          1. Mathematical operators
          2. Numeric comparison operators
          3. Numeric comparison keywords
          4. Binary operators
        4. Logical Operators
        5. Using Operators with NULL
        6. Operator Precedence
      2. Functions
        1. Using Functions
        2. Mathematical Functions
          1. abs( )
          2. acos( )
          3. asin( )
          4. atan( )
          5. atan2( )
          6. cbrt( )
          7. ceil( )
          8. cos( )
          9. cot( )
          10. degrees( )
          11. exp( )
          12. floor( )
          13. ln( )
          14. log( )
          15. mod( )
          16. pi( )
          17. pow( )
          18. radians( )
          19. random( )
          20. round( )
          21. sin( )
          22. sqrt( )
          23. tan( )
          24. trunc( )
        3. Character String Functions
          1. ascii( )
          2. btrim( )
          3. char_length( )
          4. chr( )
          5. initcap( )
          6. length( )
          7. like( ) and ilike( )
          8. lower( )
          9. lpad( )
          10. ltrim( )
          11. octet_length( )
          12. position( )
          13. repeat( )
          14. rpad( )
          15. rtrim( )
          16. strpos( )
          17. substr( )
          18. substring( )
          19. to_ascii( )
          20. translate( )
          21. trim( )
          22. upper( )
        4. Date and Time Functions
          1. current_date
          2. current_time
          3. current_timestamp
          4. date_part( )
          5. date_trunc( )
          6. extract( )
          7. isfinite( )
          8. now( )
          9. timeofday( )
        5. Type Conversion Functions
          1. bitfromint4( )
          2. bittoint4( )
          3. to_char( ) with numbers
          4. to_char( ) with timestamps
          5. to_date( )
          6. to_number( )
          7. to_timestamp( )
          8. timestamp( )
        6. Aggregate Functions
          1. Aggregate expressions
          2. avg( )
          3. count( )
          4. max( )
          5. min( )
          6. stddev( )
          7. sum( )
          8. variance( )
    4. 6. PostgreSQL Clients
      1. The psql Client: Advanced Topics
        1. Command Line Options
        2. Slash Commands
          1. Formatting commands
          2. Information display commands
          3. PostgreSQL and psql informative commands
          4. Input and output commands
          5. System commands
        3. Using External Files to Enter Queries
        4. The Readline and History Libraries
        5. Variable Substitution
        6. About the psql Prompt
          1. Modifying the prompt
          2. Prompt examples
      2. PgAccess: A Graphical Client
        1. Installation and Basic Configuration
        2. Managing Users
        3. Managing Groups
        4. Creating Databases
        5. Creating Tables
          1. Adding fields to a table
          2. Inserting and updating values
          3. Deleting values
        6. Using Queries
          1. Manually designing a query
          2. Using the visual designer
          3. Executing a query
          4. Modifying a query
        7. Creating Functions
    5. 7. Advanced Features
      1. Indices
        1. Creating an Index
          1. Unique indices
          2. Index types
          3. Functional indices
        2. Destroying an Index
      2. Advanced Table Techniques
        1. Using Constraints
          1. Column constraints
          2. Table constraints
          3. Adding a constraint
          4. Removing a constraint
        2. Inheritance
          1. Creating a child table
          2. Using inherited tables
          3. Modifying inherited tables
      3. Arrays
        1. Creating an Array Column
        2. Inserting Values into Array Columns
        3. Selecting Values From Array Columns
          1. Array subscripts
          2. Array slices
          3. Array dimensions
        4. Updating Values in Array Columns
      4. Automating Common Routines
        1. Sequences
          1. Creating a sequence
          2. Viewing a sequence
          3. Using a sequence
          4. Destroying a sequence
        2. Triggers
          1. Creating a trigger
          2. Viewing a trigger
          3. Removing a trigger
      5. Transactions and Cursors
        1. Using Transaction Blocks
        2. Using Cursors
          1. Declaring a cursor
          2. Fetching from a cursor
          3. Moving a cursor
          4. Closing a cursor
      6. Extending PostgreSQL
        1. Creating New Functions
          1. Creating SQL functions
          2. Creating C functions
          3. Destroying functions
        2. Creating New Operators
          1. Creating an operator
          2. Overloading an operator
          3. Dropping an operator
          4. III. Administrating PostgreSQL
            1. 8. Authentication and Encryption
              1. Client Authentication
                1. Password Authentication
                2. The pg_hba.conf file
                  1. Structure of the pg_hba.conf file
                  2. Example pg_hba.conf entries
                  3. The pg_ident.conf file
                3. Authentication Failure
              2. Encrypting Sessions
                1. Built-in SSL
                2. SSH/OpenSSH
                3. Configuring and Using Stunnel
                  1. OpenSSL
                  2. Stunnel
                  3. Knowing how to start Stunnel
                  4. Running Stunnel in daemon mode
                  5. Running with inetd
                  6. Wrapping up
            2. 9. Database Management
              1. Starting and Stopping PostgreSQL
                1. Using pg_ctl
                  1. Starting PostgreSQL with pg_ctl
                  2. Stopping PostgreSQL with pg_ctl
                  3. Restarting PostgreSQL with pg_ctl
                  4. Checking status of PostgreSQL with pg_ctl
                2. Using the SysV Script
                3. Calling postmaster Directly
              2. Initializing the Filesystem
                1. Initializing a Database Cluster
                2. Initializing a Secondary Database Location
              3. Creating and Removing a Database
                1. Creating a Database
                  1. Using CREATE DATABASE
                  2. Using createdb
                2. Removing a Database
                  1. Using DROP DATABASE
                  2. Using dropdb
              4. Maintaining a Database
                1. Using VACUUM
                2. Using vacuumdb
                3. Documenting a Database
                  1. Using COMMENT
                  2. Retrieving comments
              5. Backing Up and Restoring Data
                1. Using pg_dump
                2. Using pg_dumpall
                3. Restoring a Database
                  1. Using psql for plain text dumps
                  2. Using pg_restore for tarred and compressed dumps
                4. When to Backup and Restore Data
                  1. When to backup
                  2. When to restore
                5. Backing Up the Filesystem
            3. 10. User and Group Management
              1. Managing Users
                1. Viewing Users
                2. Creating Users
                  1. Creating a user with the CREATE USER SQL command
                  2. Creating a user with the createuser script
                3. Altering Users
                4. Removing Users
                  1. Removing users with the DROP USER SQL command
                  2. Removing users with the dropuser operating system command
              2. Managing Groups
                1. Creating and Removing Groups
                  1. Creating a group
                  2. Removing a group
                2. Associating Users with Groups
                  1. Adding a user to a group
                  2. Removing a user from a group
              3. Granting Privileges
                1. Understanding Access Control
                2. Granting Privileges with GRANT
                3. Restricting Rights with REVOKE
                4. Using Views for Access Control
          5. IV. PostgreSQL Programming
            1. 11. PL/pgSQL
              1. Adding PL/pgSQL to Your Database
                1. Adding PL/pgSQL to Your Database
                  1. Using psql to add PL/pgSQL
                  2. Using createlang to add PL/pgSQL
              2. Language Structure
                1. Code Blocks
                2. Comments
                  1. Comment syntax
                  2. Good commenting style
                3. Statements and Expressions
                  1. Statements
                  2. Expressions
              3. Using Variables
                1. Data Types
                2. Declaration
                3. Assignment
                4. Argument Variables
                5. Returning Variables
                6. Attributes
                  1. The %TYPE attribute
                  2. The %ROWTYPE attribute
                7. Concatenation
              4. Controlling Program Flow
                1. Conditional Statements
                  1. The IF/THEN statement
                  2. The IF/THEN/ELSE statement
                  3. The IF/THEN/ELSE/IF statement
                2. Loops
                  1. The basic loop
                  2. The WHILE loop
                  3. The FOR loop
                3. Handling Errors and Exceptions
                4. Calling Functions
              5. PL/pgSQL and Triggers
            2. 12. JDBC
              1. Building the PostgreSQL JDBC Driver
              2. Using the PostgreSQL Driver
              3. Using JDBC
                1. Basic JDBC Usage
                2. Using Advanced JDBC Features
                  1. CallableStatement
                  2. PreparedStatement
                  3. ResultSetMetaData
                  4. DatabaseMetaData
              4. Issues Specific to PostgreSQL and JDBC
            3. 13. LXP
              1. Why Use LXP?
              2. Core Features
                1. Content Inclusion and Management
                2. Direct SQL Methods and PostgreSQL Connectivity
                3. Fingerless
              3. Installing and Configuring LXP
                1. Installing LXP
                  1. Using lxpinstall.sh
                  2. Manual installation
                2. Nuts and Bolts: Configuring lxp.conf
                  1. General settings
                  2. Database settings
              4. Understanding LXP Mark-Up
                1. LXP Tags
                2. LXP Regions
              5. LXP Variables and Objects
                1. Naming Conventions
                2. Using Variables and Objects
                3. CGI Arguments
                4. CGI Arrays
                5. Direct SQL objects
                6. Global LXP objects
              6. Using Cookies with LXP
                1. Setting Cookies
                2. Accessing Cookie Values
              7. Tag Parsing
                1. Variable Substitution
                2. Object Variable Value Substitution
                3. Entity substitution
                4. Using <varparser>
              8. Branching Logic
                1. The <if> and <ifnot> Tags
                  1. Using <if>
                  2. Using <ifnot>
                  3. Nesting logic
                2. Using <ifcookie> and <ifnotcookie>
                3. The <else>, <elseif>, and <elseifnot> Tags
              9. Loop Iteration
              10. Content Inclusion
                1. Including LXP Files
                2. Including Flat Files
                3. Including Token-Delimited Files
                4. Including XML, RSS and RDF Files
                5. Including External Content Types
                6. Including SQL Content
                  1. Setting the database source
                  2. Accessing column values
                  3. Accessing SQL meta-data
                  4. Setting SQL object variables
              11. Displaying Foreign Tags with <xtag>
          6. V. Command Reference
            1. 14. PostgreSQL Command Reference
              1. Abort
                1. Synopsis
                  1. Parameters
                  2. Results
                  3. Examples
              2. Alter Group
                1. Synopsis
                  1. Parameters
                  2. Results
                2. Description
                3. Examples
              3. Alter Table
                1. Synopsis
                  1. Parameters
                  2. Results
                2. Description
                3. Examples
              4. Alter User
                1. Synopsis
                  1. Parameters & Keywords
                  2. Results
                2. Description
                3. Examples
              5. Begin
                1. Synopsis
                  1. Parameters
                  2. Results
                2. Description
                3. Examples
              6. Close
                1. Synopsis
                  1. Parameters
                  2. Results
                2. Description
                3. Examples
              7. Cluster
                1. Synopsis
                  1. Parameters
                  2. Results
                2. Description
                3. Examples
              8. Comment
                1. Synopsis
                  1. Parameters
                  2. Results
                2. Description
                3. Examples
              9. Commit
                1. Synopsis
                  1. Parameters
                  2. Results
                2. Description
                3. Examples
              10. Copy
                1. Synopsis
                  1. Parameters
                  2. Results
                2. Description
                3. Restrictions and limitations
                4. File formatting
                5. Examples
              11. Create Aggregate
                1. Synopsis
                  1. Parameters
                  2. Results
                2. Description
                3. Examples
              12. Create Database
                1. Synopsis
                  1. Parameters
                  2. Results
                2. Description
                3. Examples
              13. Create Function
                1. Synopsis
                  1. Parameters
                  2. Results
                2. Description
                  1. Function attributes
                  2. Function overloading
                3. Examples
              14. Create Group
                1. Synopsis
                  1. Parameters
                  2. Results
                2. Description
                3. Examples
              15. Create Index
                1. Synopsis
                  1. Parameters
                  2. Results
                2. Description
                  1. Column index
                  2. Functional index
                  3. Operators and operator classes
                3. Examples
              16. Create Language
                1. Synopsis
                  1. Parameters
                  2. Results
                2. Description
                3. Examples
              17. Create Operator
                1. Synopsis
                  1. Parameters
                  2. Results
                2. Description
                3. Examples
              18. Create Rule
                1. Synopsis
                  1. Parameters
                  2. Results
                2. Description
                3. Examples
              19. Create Sequence
                1. Synopsis
                  1. Parameters
                  2. Results
                2. Description
                3. Examples
              20. Create Table
                1. Synopsis
                  1. Parameters
                  2. Results
                2. Description
                3. Examples
              21. Create Table As
                1. Synopsis
                  1. Parameters
                  2. Results
                2. Description
                3. Example
              22. Create Trigger
                1. Synopsis
                  1. Parameters
                  2. Results
                2. Description
                3. Examples
              23. Create Type
                1. Synopsis
                  1. Parameters
                  2. Results
                2. Description
                3. Example
              24. Create User
                1. Synopsis
                  1. Parameters
                  2. Results
                2. Description
                3. Example
              25. Create View
                1. Synopsis
                  1. Parameters
                  2. Results
                2. Description
                3. Example
              26. Current_Date
                1. Synopsis
                  1. Parameters
                  2. Results
                2. Description
                3. Examples
              27. Current_Time
                1. Synopsis
                  1. Parameters
                  2. Results
                2. Description
                  1. Examples
              28. Current_Timestamp
                1. Synopsis
                  1. Parameters
                  2. Results
                2. Description
                3. Examples
              29. Current_User
                1. Synopsis
                  1. Parameters
                  2. Results
                2. Description
                3. Examples
              30. Declare
                1. Synopsis
                  1. Parameters
                  2. Results
                2. Description
                3. Example
              31. Delete
                1. Synopsis
                  1. Parameters
                  2. Results
                2. Description
                3. Example
              32. Drop Aggregate
                1. Synopsis
                  1. Parameters
                  2. Results
                2. Description
                3. Examples
              33. Drop Database
                1. Synopsis
                  1. Parameters
                  2. Results
                2. Description
                3. Examples
              34. Drop Function
                1. Synopsis
                  1. Parameters
                  2. Results
                2. Description
                3. Example
              35. Drop Group
                1. Synopsis
                  1. Parameters
                  2. Results
                2. Description
                3. Example
              36. Drop Index
                1. Synopsis
                  1. Parameters
                  2. Results
                2. Description
                3. Example
              37. Drop Language
                1. Synopsis
                  1. Parameters
                  2. Results
                2. Description
                3. Example
              38. Drop Operator
                1. Synopsis
                  1. Parameters
                  2. Results
                2. Description
                3. Example
              39. Drop Rule
                1. Synopsis
                2. Parameters
                3. Results
                4. Description
                5. Example
              40. Drop Sequence
                1. Synopsis
                  1. Parameters
                  2. Results
                2. Description
                3. Example
              41. Drop Table
                1. Synopsis
                  1. Parameters
                  2. Results
                2. Description
                3. Example
              42. Drop Trigger
                1. Synopsis
                  1. Parameters
                  2. Results
                2. Description
                3. Examples
              43. Drop Type
                1. Synopsis
                  1. Parameters
                  2. Results
                2. Description
                3. Examples
              44. Drop User
                1. Synopsis
                  1. Parameters
                  2. Results
                2. Description
                3. Example
              45. DROP VIEW
                1. Synopsis
                  1. Parameters
                  2. Results
                2. Description
                3. Example
              46. End
                1. Synopsis
                  1. Parameters
                  2. Results
                2. Description
                3. Example
              47. Explain
                1. Synopsis
                  1. Parameters
                  2. Results
                2. Description
                3. Example
              48. FETCH
                1. Synopsis
                  1. Parameters
                  2. Results
                2. Description
                3. Examples
              49. Grant
                1. Synopsis
                  1. Parameters
                  2. Results
                2. Description
                3. Example
              50. Insert
                1. Synopsis
                  1. Parameters
                  2. Results
                2. Description
                3. Examples
              51. Listen
                1. Synopsis
                  1. Parameters
                  2. Results
                2. Description
                3. Example
              52. Load
                1. Synopsis
                  1. Parameters
                  2. Results
                2. Description
                3. Example
              53. Lock
                1. Synopsis
                  1. Parameters
                  2. Results
                2. Description
                3. Examples
              54. Move
                1. Synopsis
                  1. Parameters
                  2. Results
                2. Description
                3. Examples
              55. Notify
                1. Synopsis
                  1. Parameters
                  2. Results
                2. Description
                  1. Transactions
                  2. Multiple signals
                3. Example
              56. Reindex
                1. Synopsis
                  1. Parameters
                  2. Results
                2. Description
                3. Examples
              57. Reset
                1. Synopsis
                  1. Parameters
                  2. Results
                2. Description
                3. Example
              58. Revoke
                1. Synopsis
                  1. Parameters
                  2. Results
                2. Description
                3. Example
              59. Rollback
                1. Synopsis
                  1. Parameters
                  2. Results
                2. Description
                3. Example
              60. Select
                1. Synopsis
                  1. Parameters
                  2. Results
                2. Description
                3. Examples
              61. Select Into
                1. Synopsis
                  1. Parameters
                  2. Results
                2. Description
                3. Example
              62. Set
                1. Synopsis
                  1. Parameters
                  2. Results
                2. Description
                3. Examples
              63. Set Constraints
                1. Synopsis
                  1. Parameters
                  2. Results
                2. Description
                3. Example
              64. Set Transaction
                1. Synopsis
                  1. Parameters
                  2. Results
                2. Description
                3. Examples
              65. Show
                1. Synopsis
                  1. Parameters
                  2. Results
                2. Description
                3. Examples
              66. Truncate
                1. Synopsis
                  1. Parameters
                  2. Results
                2. Description
                3. Example
              67. Unlisten
                1. Synopsis
                  1. Examples
                  2. Results
                2. Description
                3. Example
              68. Update
                1. Synopsis
                  1. Parameters
                  2. Results
                2. Description
                3. Example
              69. Vacuum
                1. Synopsis
                  1. Parameters
                  2. Results
                2. Description
                3. Example
            2. A. Multibyte Encoding Types
            3. B. Backend Options for postgres
            4. C. Binary COPY Format
              1. The Header
              2. Tuples
              3. Trailer
            5. D. Internal psql Variables
          7. E. About the Authors
          8. Index
          9. Colophon
          10. Copyright
18.118.0.145