Postgresql

Introduction.     PostgreSQL Features     What Versions Does This Book Cover?       Who Is This Book For?     What Topics Does This Book Cover?     What's New in the Second Edition? I. GENERAL POSTGRESQL USE. 1. Introduction to PostgreSQL and SQL.     A Sample Database     Basic Database Terminology     Prerequisites       Installing PostgreSQL Using an RPM     Connecting to a Database       A (Very) Simple Query     Creating Tables     Viewing Table Descriptions     Adding New Records to a Table       Using the INSERT Command       Using the COPY Command     Installing the Sample Database     Retrieving Data from the Sample Database       SELECT Expression       SELECT  FROM Table       SELECT Single-Column FROM Table       SELECT Column-List FROM Table       SELECT Expression-List FROM Table       Selecting Specific Rows     The CASE Expression       Formatting Column Results       Matching Patterns     Aggregates       COUNT()       SUM()       AVG()       MIN() and MAX()       Other Aggregate Functions       Grouping Results     Multi-Table Joins       Join Types     UPDATE     DELETE     A (Very) Short Introduction to Transaction Processing     Creating New Tables Using CREATE TABLE...AS     Using VIEW     Summary 2. Working with Data in PostgreSQL.     NULL Values     Character Values       Syntax for Literal Values       Supported Operators     Numeric Values       Size, Precision, and Range-of-Values       Syntax for Literal Values       Supported Operators     Date/Time Values       Syntax for Literal Values       Supported Operators     Boolean (Logical) Values       Size and Valid Values       Syntax for Literal Values       Supported Operators     Geometric Data Types       Syntax for Literal Values       Sizes and Valid Values       Supported Operators     Object IDs (OID)       Syntax for Literal Values       Size and Valid Values       Supported Operators     BLOBs       Syntax for Literal Values       Supported Operators       Large-Objects     Network Address Data Types       MACADDR    ...

List of contents

Introduction.
    PostgreSQL Features
    What Versions Does This Book Cover?
      Who Is This Book For?
    What Topics Does This Book Cover?
    What's New in the Second Edition?
I. GENERAL POSTGRESQL USE.
1. Introduction to PostgreSQL and SQL.
    A Sample Database
    Basic Database Terminology
    Prerequisites
      Installing PostgreSQL Using an RPM
    Connecting to a Database
      A (Very) Simple Query
    Creating Tables
    Viewing Table Descriptions
    Adding New Records to a Table
      Using the INSERT Command
      Using the COPY Command
    Installing the Sample Database
    Retrieving Data from the Sample Database
      SELECT Expression
      SELECT  FROM Table
      SELECT Single-Column FROM Table
      SELECT Column-List FROM Table
      SELECT Expression-List FROM Table
      Selecting Specific Rows
    The CASE Expression
      Formatting Column Results
      Matching Patterns
    Aggregates
      COUNT()
      SUM()
      AVG()
      MIN() and MAX()
      Other Aggregate Functions
      Grouping Results
    Multi-Table Joins
      Join Types
    UPDATE
    DELETE
    A (Very) Short Introduction to Transaction Processing
    Creating New Tables Using CREATE TABLE...AS
    Using VIEW
    Summary
2. Working with Data in PostgreSQL.
    NULL Values
    Character Values
      Syntax for Literal Values
      Supported Operators
    Numeric Values
      Size, Precision, and Range-of-Values
      Syntax for Literal Values
      Supported Operators
    Date/Time Values
      Syntax for Literal Values
      Supported Operators
    Boolean (Logical) Values
      Size and Valid Values
      Syntax for Literal Values
      Supported Operators
    Geometric Data Types
      Syntax for Literal Values
      Sizes and Valid Values
      Supported Operators
    Object IDs (OID)
      Syntax for Literal Values
      Size and Valid Values
      Supported Operators
    BLOBs
      Syntax for Literal Values
      Supported Operators
      Large-Objects
    Network Address Data Types
      MACADDR
      CIDR
      INET
      Syntax for Literal Values
      Supported Operators
    Sequences
    Arrays
    Column Constraints
      NULL/NOT NULL
      UNIQUE
      PRIMARY KEY
      REFERENCES
      CHECK()
    Expression Evaluation and Type Conversion
    Creating Your Own Data Types
      Refining Data Types with CREATE DOMAIN
      Creating and Using Composite Types
    Summary
3. PostgreSQL SQL Syntax and Use.
    PostgreSQL Naming Rules
      The Importance of the COMMENT Command
    Creating, Destroying, and Viewing Databases
      Tablespaces
      Creating New Databases
      Dropping a Database
      Viewing Databases
    Creating New Tables
      Temporary Tables
      Table Constraints
      Dropping Tables
      Inheritance
      ALTER TABLE
    Adding Indexes to a Table
      Tradeoffs
      Creating an Index
      Functional Indexes and Partial Indexes
      Creating Indexes on Array Values
      Indexes and Tablespaces
    Getting Information About Databases and Tables
    Transaction Processing
      Persistence
      Transaction Isolation
      Multi-Versioning and Locking
    Summary
4. Performance.
    How PostgreSQL Organizes Data
      Page Caching
      Summary
    Gathering Performance Information
      Dead Tuples
      Index Performance
    Understanding How PostgreSQL Executes a Query
      EXPLAIN
      Seq Scan
      Index Scan
      Sort
      Unique
      LIMIT
      Aggregate
      Append
      Result
      Nested Loop
      Merge Join
      Hash and Hash Join
      Group
      Subquery Scan and Subplan
      Tid Scan
      Materialize
      Setop (Intersect, Intersect All, Except, Except All)
    Execution Plans Generated by the Planner
    The ARC Buffer Manager
    Table Statistics
    Performance Tips
II. PROGRAMMING WITH POSTGRESQL.
5. Introduction to PostgreSQL Programming.
    Server-Side Programming
      PL/pgSQL
      Other Procedural Languages Supported by PostgreSQL
      Extending PostgreSQL Using External Languages
    Client-Side APIs
    General Structure of Client Applications
      Connection Properties
      LISTEN/NOTIFY
    Choosing an Application Environment
      Server-Side Code
      Client-Side Code
      Mixing Server-Side and Client-Side Code
    Summary
6. Extending PostgreSQL.
    Extending the PostgreSQL Server with Custom Functions
    Returning Multiple Values from an Extension Function
    The PostgreSQL SRF Interface
    Returning Complete Rows from an Extension Function
    Extending the PostgreSQL Server with Custom Data Types
    Internal and External Forms
    Defining a Simple Data Type in PostgreSQL
    Defining the Data Type in C
    Defining the Input and Output Functions in C
    Defining the Input and Output Functions in PostgreSQL
    Defining the Data Type in PostgreSQL
    Indexing Custom Data Types
    Summary
7. PL/pgSQL.
    Installing PL/pgSQL
    Language Structure
      Quoting Embedded Strings
      CREATE FUNCTION
      DROP FUNCTION
    Function Body
      Comments
      Variables
      PL/pgSQL Statement Types
    Cursors
      FETCH
      Parameterized Cursors
      Cursor References
    Triggers
      TRIGGER Return Values
      TRIGGER Function Arguments
    Polymorphic Functions
    PL/pgSQL and Security
    Summary
8. The PostgreSQL C API-libpq.
    Prerequisites
    Client 1-Connecting to the Server
      Compiling the Client
      Identifying the Server
    Client 2-Adding Error Checking
      Viewing Connection Attributes
    Client 3-Simple Processing-PQexec() and PQprint()
      Results Returned by PQexec()
      The Prepare/Execute Model
    Client 4-An Interactive Query Processor
      Processing Multiple Result Sets
      Asynchronous Processing
    Summary
9. A Simpler C API-libpgeasy.
    Prerequisites
    Client 1-Connecting to the Server
    Client 2-Adding Error Checking
    Client 3-Processing Queries
      Working with Binary Cursors
      Byte Ordering and NULL Values
    Client 4-An Interactive Query Processor
    Summary
10. The New PostgreSQL C++ API-libpqxx.
    Prerequisites
    Client 1-Connecting to the Server
      Using pqxx-config to Create a Simple Makefile
      connection Member Functions
    Client 2-Adding Error Checking
      Other Exceptions Thrown by libpqxx
      Handling Informational/Warning Messages with Notice Processor Objects
    Client 3-Processing Queries
      Working with Transactions
      Working with Result Sets
      Working with Large-Objects
      LISTEN/NOTIFY
      set_variable() and get_variable()
    Client 4-Working with transactors
    Summary
11. Embedding SQL Commands in C Programs-ecpg.
    Prerequisites
    Client 1-Connecting to the Server
      The ecpg Preprocessor
      Connection Strings
    Client 2-Adding Error Checking
      The sqlca Structure
    Client 3-Processing SQL Commands
      ecpg Data Types
    Client 4-An Interactive Query Processor
    Summary
12. Using PostgreSQL from an ODBC Client Application.
    ODBC Architecture Overview
      The ODBC Client Application
      The ODBC Driver Manager
      The ODBC Driver
      The ODBC-Compliant Database
      The Data Source
      Setting Up a Data Source on Unix Systems
      Setting Up a Data Source in Windows
      Datasource Connection Properties
    Prerequisites
    Client 1-Connecting to the Server
    Client 2-Adding Error Checking
    Client 3-Processing Queries
    Client 4-An Interactive Query Processor
    Summary
    Resources
13. Using PostgreSQL from a Java Client Application.
    JDBC Architecture Overview
      The JDBC DriverManager
      The JDBC Driver
      The JDBC-Compliant Database
    Prerequisites
    Client 1-Connecting to the Server
      JDBC URLs
    Client 2-Adding Error Checking
      JNDI and the DataSource Class
    Client 3-Processing Queries
      Statement Classes
      Metadata
    Client 4-An Interactive Query Processor
    Summary
14. Using PostgreSQL with Perl.
    DBI Architecture Overview
      The DBI
      The DBD Driver
      The DBI-Compliant Database
    Prerequisites
    Client 1-Connecting to the Server
      DBI URLs
    Client 2-Adding Error Checking
    Client 3-Processing Queries
      The Prepare/Execute Model
      Metadata and Result Set Processing
      Other Statement and Database Handle Attributes
    Client 4-An Interactive Query Processor
    Summary
15. Using PostgreSQL with PHP.
    PHP Architecture Overview
    Prerequisites
    Client 1-Connecting to the Server
    Client 2-Adding Error Checking
    Client 3-Query Processing
      Other Ways to Retrieve Result Set Values
      Metadata Access
    Client 4-An Interactive Query Processor
    Other Features
    Summary
16. Using PostgreSQL with Tcl and Tcl/Tk.
    Prerequisites
    Client 1-Connecting to the Server
      Making the Connection Dialog Reusable
    Client 2-Query Processing
      Result Set Processing
    Client 3-An Interactive Query Processor
    The libpgtcl Large-Object API
    Summary
17. Using PostgreSQL with Python.
    Python/PostgreSQL Interface Architecture
    Prerequisites
    Client 1-Connecting to the Server
    Client 2-Adding Error Checking
    Client 3-Query Processing
    Client 4-An Interactive Command Processor
    Summary
18. Npgsql: The .NET Data Provider.
    Prerequisites
    Preparing Visual Studio
    Understanding the ADO.NET Class Hierarchy
    Creating an Npgsql-enabled VB Project
    Client 1-Connecting to the Server
    Client 2-An Interactive Query Processor
    Client 3-Updating the Database with a DataSet
    Client 4-A More Robust Query Processor
    Client 5-Using a Typed DataSet
      Creating a Typed DataSet
    Summary
19. Other Useful Programming Tools.
    PL/Java-Writing Stored Procedures in Java
      Installing PL/Java
      Writing a Simple PL/Java Function
      Accessing the Database from a PL/Java Function
      Returning Multiple Results from a PL/Java Function
      Writing PL/Java Trigger Functions
      Adding Install/Uninstall Commands to a Jar File
    pgcurl-Web-enabling Your PostgreSQL Server
    pgbash-Writing PostgreSQL-enabled Shell Scripts
III. POSTGRESQL ADMINISTRATION.
20. Introduction to PostgreSQL Administration.
    Security
    User Accounts
    Backup and Restore
    Server Startup and Shutdown
    Running PostgreSQL on a Windows Host
    Tuning
    Installing Updates
    Localization
    Summary
21. PostgreSQL Administration.
    Roadmap (Where's All My Stuff?)
    Installing PostgreSQL
      Unix/Linux
      Windows
    Managing Databases
      Creating a New Cluster
      Creating a New Database
      Routine Maintenance
      Logfile Rotation
    The PostgreSQL BGWRITER Process
    Managing User Accounts
      CREATE USER
      Managing Groups
    Configuring Your PostgreSQL Runtime Environment
      File Locations
      Security-Related Parameters
      Connection-Related Parameters
      Operational Parameters
      Write-Ahead Log Parameters
      Optimizer Parameters
      Debugging/Logging Parameters
      Performance Statistics
      Per-session Parameters
      Miscellaneous Parameters
      Read-only Parameters
    Arranging for PostgreSQL Startup and Shutdown
      Using pg_ctl
      Shutdown Modes
      Configuring PostgreSQL Startup on Unix/Linux Hosts
    Backing Up and Copying Databases
      Using pg_dump
      Using pg_dumpall
      Using pg_restore
    Point-in-time Recovery
    Summary
22. Internationalization and Localization.
    Locale Support
      Enabling Locale Support
      Effects of Locale Support
      PostgreSQL Locale Summary
    Multi-Byte Character Sets
      Encodings Supported by PostgreSQL
      Enabling Multi-Byte Support
      Selecting an Encoding
      Client/Server Translation
    Summary
23. Security.
    Securing the PostgreSQL Data Files
      Securing PostgreSQL Data Files in Windows
    Securing Network Access
      local Connections
      host, hostssl, and hostnossl Connections
      The trust Authentication Method
      The ident Authentication Method
      The password Authentication Method
      The crypt Authentication Method
      The md5 Authentication Method
      The pam Authentication Method
      The krb4 and krb5 Authentication Methods
      The reject Authentication Method
    Securing Tables
    Securing Functions
    Summary
24. Replicating PostgreSQLData with Slony.
    Overview
    Requirements
    Creating a Replication Cluster
    Starting the Replication Daemons
    Creating a Replication Set
    Subscribing to a Replication Set
      Copying Table and Sequence Definitions
      Creating a Subscriber
    Changing the Cluster Topology (Re-mastering and Failover)
    Summary
25. Contributed Modules.
    Exchanging PostgreSQL Data with XML
      XPath Queries
      Converting XML Data with XSLT
    Using Full-text Search
      Searching Multiple Columns
      Simplifying tsearch2 with Customized Functions
      Searching for Phrases
      Configuring tsearch2
Index.

Product details

• Authors: Korry Douglas, Susan Douglas, douglas Korry
• Publisher: External catalogues UK
• Languages: English
• Product format: Paperback / Softback
• Released: 15.08.2005
• EAN: 9780672327568
• ISBN: 978-0-672-32756-8
• Dimensions: 175 mm x 227 mm x 50 mm
• Series: Developer's Library; Sams; Sams; Developer's Library
• Subject: Natural sciences, medicine, IT, technology > IT, data processing > Application software