Practical PostgreSQL

Practical PostgreSQL

Released
Publisher(s): O'Reilly Media, Inc.
ISBN: None

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

None

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
  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. Copyright

Product information

  • Title: Practical PostgreSQL
  • Author(s):
  • Release date:
  • Publisher(s): O'Reilly Media, Inc.
  • ISBN: None