PostgreSQLAuthorsDatabase

Practical PostgreSQL by John C. Worsley, Joshua D. Drake

By Admin

Practical PostgreSQL 
by John C. Worsley, Joshua D. Drake

Publisher: O’Reilly 2002
ISBN/ASIN: 1565928466
ISBN-13: 9781565928466
Number of pages: 636

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.

Home page url

Download or read it online here: 
Read online

 

Table of Contents

  1. Practical PostgreSQL
    1. SPECIAL OFFER: Upgrade this ebook with O’Reilly
    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
    6. 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
    7. 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>
    8. 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
    9. E. About the Authors
    10. Index
    11. Colophon
    12. SPECIAL OFFER: Upgrade this ebook with O’Reilly
You might also like
History

وقت کا سفر

History

تاریخ عالم کا ایک جائزہ

History

History of Muslim Philosophy 2

History

Sources of Mughal History

History

Sources on Punjab History

History

History of Bhakti