O'Reilly logo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

PostgreSQL: Up and Running, 2nd Edition

PostgreSQL: Up and Running, 2nd Edition

by Leo S. Hsu, Regina O. Obe
Publisher: O'Reilly Media, Inc.
Release Date: December 2014
ISBN: 9781449373184
View table of contents
Start reading

Book Description

Thinking of migrating to PostgreSQL? This clear, fast-paced introduction helps you understand and use this open source database system. Not only will you learn about the enterprise class features in versions 9.2, 9.3, and 9.4, you’ll also discover that PostgeSQL is more than a database system—it’s also an impressive application platform.

Table of Contents

  1. Preface
    1. Audience
    2. What Makes PostgreSQL Special, and Why Use It?
    3. Why Not PostgreSQL?
    4. Where to Get Data and Code Used in This Book
    5. For More Information on PostgreSQL
    6. Code and Output Formatting
    7. Conventions Used in This Book
    8. Using Code Examples
    9. Safari ® Books Online
    10. How to Contact Us
  2. 1. The Basics
    1. Where to Get PostgreSQL
    2. Administration Tools
      1. psql
      2. pgAdmin
      3. phpPgAdmin
      4. Adminer
    3. PostgreSQL Database Objects
    4. What’s New in Latest Versions of PostgreSQL?
      1. Why Upgrade?
      2. What’s New in PostgreSQL 9.4?
      3. PostgreSQL 9.3: New Features
      4. PostgreSQL 9.2: New Features
      5. PostgreSQL 9.1: New Features
    5. Database Drivers
    6. Where to Get Help
    7. Notable PostgreSQL Forks
  3. 2. Database Administration
    1. Configuration Files
      1. postgresql.conf
        1. “I edited my postgresql.conf and now my server is broken.”
      2. pg_hba.conf
        1. “I edited my pg_hba.conf and now my server is broken.”
        2. Authentication methods
      3. Reloading the Configuration Files
    2. Managing Connections
    3. Roles
      1. Creating Login Roles
      2. Creating Group Roles
        1. Inheriting rights from group roles
    4. Database Creation
      1. Template Databases
      2. Using Schemas
    5. Privileges
      1. Types of Privileges
      2. Getting Started
      3. GRANT
      4. Default Privileges
      5. Privilege Idiosyncrasies
    6. Extensions
      1. Installing Extensions
        1. Step 1: Installing on the server
        2. Step 2: Installing into a database (pre-9.1)
        3. Step 2: Installing into a database (version 9.1 and later)
        4. Upgrading to the new extension model
      2. Common Extensions
        1. Popular extensions
        2. Classic extensions
    7. Backup and Restore
      1. Selective Backup Using pg_dump
      2. Systemwide Backup Using pg_dumpall
      3. Restore
        1. Using psql to restore plain-text SQL backups
        2. Using pg_restore
    8. Managing Disk Storage with Tablespaces
      1. Creating Tablespaces
      2. Moving Objects Between Tablespaces
    9. Verboten Practices
      1. Don’t Delete PostgreSQL Core System Files and Binaries
      2. Don’t Give Full OS Administrative Rights to the Postgres System Account (postgres)
      3. Don’t Set shared_buffers Too High
      4. Don’t Try to Start PostgreSQL on a Port Already in Use
  4. 3. psql
    1. Environment Variables
    2. Interactive versus Noninteractive psql
    3. psql Customizations
      1. Custom Prompts
      2. Timing Executions
      3. Autocommit Commands
      4. Shortcuts
      5. Retrieving Prior Commands
    4. psql Gems
      1. Executing Shell Commands
      2. Watching Statements
      3. Lists
    5. Importing and Exporting Data
      1. psql Import
      2. psql Export
      3. Copy from/to Program
    6. Basic Reporting
  5. 4. Using pgAdmin
    1. Getting Started
      1. Overview of Features
      2. Connecting to a PostgreSQL Server
      3. Navigating pgAdmin
    2. pgAdmin Features
      1. Accessing psql from pgAdmin
      2. Editing postgresql.conf and pg_hba.conf from pgAdmin
      3. Creating Database Assets and Setting Privileges
        1. Creating databases and other database assets
        2. Privilege management
      4. Import and Export
        1. Importing files
        2. Exporting queries as a structured file or report
      5. Backup and Restore
        1. Backing Up an Entire Database
        2. Backing up system-wide objects
        3. Selective backup of database assets
    3. pgScript
    4. Graphical Explain
    5. Job Scheduling with pgAgent
      1. Installing pgAgent
      2. Scheduling Jobs
      3. Helpful pgAgent Queries
  6. 5. Data Types
    1. Numerics
      1. Serials
      2. Generate Series Function
    2. Characters and Strings
      1. String Functions
      2. Splitting Strings into Arrays, Tables, or Substrings
      3. Regular Expressions and Pattern Matching
    3. Temporals
      1. Time Zones: What They Are and Are Not
      2. Datetime Operators and Functions
    4. Arrays
      1. Array Constructors
      2. Referencing Elements in an Array
      3. Array Slicing and Splicing
      4. Unnesting Arrays to Rows
    5. Range Types
      1. Discrete Versus Continuous Ranges
      2. Built-in Range Types
      3. Defining Ranges
      4. Defining Tables with Ranges
      5. Range Operators
        1. Overlap operator
        2. Contains and contained in operators
    6. JSON
      1. Inserting JSON Data
      2. Querying JSON
      3. Outputting JSON
      4. Binary JSON: jsonb
    7. XML
      1. Inserting XML Data
      2. Querying XML Data
    8. Custom and Composite Data Types
      1. All Tables Are Custom Data Types
      2. Building Custom Data Types
      3. Building Operators and Functions for Custom Types
  7. 6. Tables, Constraints, and Indexes
    1. Tables
      1. Basic Table Creation
      2. Inherited Tables
      3. Unlogged Tables
      4. TYPE OF
    2. Constraints
      1. Foreign Key Constraints
      2. Unique Constraints
      3. Check Constraints
      4. Exclusion Constraints
    3. Indexes
      1. PostgreSQL Stock Indexes
      2. Operator Classes
      3. Functional Indexes
      4. Partial Indexes
      5. Multicolumn Indexes
  8. 7. SQL: The PostgreSQL Way
    1. Views
      1. Single Table Views
      2. Using Triggers to Update Views
      3. Materialized Views
    2. Handy Constructions
      1. DISTINCT ON
      2. LIMIT and OFFSET
      3. Shorthand Casting
      4. Multirow Insert
      5. ILIKE for Case-Insensitive Search
      6. Returning Functions
      7. Restricting DELETE, UPDATE, SELECT from Inherited Tables
      8. DELETE USING
      9. Returning Affected Records to the User
      10. Composite Types in Queries
      11. DO
    3. FILTER Clause for Aggregates
    4. Window Functions
      1. PARTITION BY
      2. ORDER BY
    5. Common Table Expressions
      1. Basic CTEs
      2. Writable CTEs
      3. Recursive CTE
    6. Lateral Joins
  9. 8. Writing Functions
    1. Anatomy of PostgreSQL Functions
      1. Function Basics
      2. Triggers and Trigger Functions
      3. Aggregates
      4. Trusted and Untrusted Languages
    2. Writing Functions with SQL
      1. Basic SQL Function
      2. Writing SQL Aggregate Functions
    3. Writing PL/pgSQL Functions
      1. Basic PL/pgSQL Function
      2. Writing Trigger Functions in PL/pgSQL
    4. Writing PL/Python Functions
      1. Basic Python Function
    5. Writing PL/V8, PL/CoffeeScript, and PL/LiveScript Functions
      1. Basic Functions
      2. Writing Aggregate Functions with PL/V8
  10. 9. Query Performance Tuning
    1. EXPLAIN
      1. EXPLAIN Options
      2. Sample Runs and Output
      3. Graphical Outputs
    2. Gathering Statistics on Statements
    3. Guiding the Query Planner
      1. Strategy Settings
      2. How Useful Is Your Index?
      3. Table Statistics
      4. Random Page Cost and Quality of Drives
    4. Caching
    5. Writing Better Queries
      1. Overusing Subqueries in SELECT
      2. Avoid SELECT *
      3. Make Good Use of CASE
      4. Using Filter Instead of CASE
  11. 10. Replication and External Data
    1. Replication Overview
      1. Replication Jargon
      2. Evolution of PostgreSQL Replication
      3. Third-Party Replication Options
    2. Setting Up Replication
      1. Configuring the Master
      2. Configuring the Slaves
      3. Initiating the Replication Process
    3. Foreign Data Wrappers
      1. Querying Flat Files
      2. Querying a Flat File as Jagged Arrays
      3. Querying Other PostgreSQL Servers
      4. Querying Nonconventional Data Sources
  12. A. Installing PostgreSQL
    1. Windows, Desktop Linux
    2. CentOS, Fedora, Red Hat, Scientific Linux
    3. Debian, Ubuntu
    4. FreeBSD
    5. Mac OS X
  13. B. PostgreSQL Packaged Command-Line Tools
    1. Database Backup Using pg_dump
    2. Server Backup: pg_dumpall
    3. Database Restore: pg_restore
    4. psql Interactive Commands
    5. psql Noninteractive Commands
  14. Index
  15. Colophon
  16. Copyright