Hjem

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