PostgreSQL: Up and Running, 3rd Edition

PostgreSQL: Up and Running, 3rd Edition

by Regina O. Obe, Leo S. Hsu
Released October 2017
Publisher(s): O'Reilly Media, Inc.
ISBN: 9781491963364

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

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.5 to 10, you’ll also discover that PostgeSQL is more than a database system—it’s an impressive application platform as well.

With examples throughout, this book shows you how to achieve tasks that are difficult or impossible in other databases. This third edition covers new features, such as ANSI-SQL constructs found only in proprietary databases until now: foreign data wrapper (FDW) enhancements; new full text functions and operator syntax introduced in version 9.6; XML constructs new in version 10; query parallelization features introduced in 9.6 and enhanced in 10; built-in logical replication introduced in Version 10.e.

If you’re a current PostgreSQL user, you’ll pick up gems you may have missed before.

  • Learn basic administration tasks such as role management, database creation, backup, and restore
  • Apply the psql command-line utility and the pgAdmin graphical administration tool
  • Explore PostgreSQL tables, constraints, and indexes
  • Learn powerful SQL constructs not generally found in other databases
  • Use several different languages to write database functions
  • Tune your queries to run as fast as your hardware will allow
  • Query external and variegated data sources with foreign data wrappers
  • Learn how to use built-in replication to replicate data

Publisher resources

View/Submit Errata

Table of contents

  1. Preface
    1. Audience
    2. For More Information on PostgreSQL
    3. Code and Output Formatting
    4. Conventions Used in This Book
    5. Using Code Examples
    6. O’Reilly Safari
    7. How to Contact Us
  2. 1. The Basics
    1. Why PostgreSQL?
    2. Why Not PostgreSQL?
    3. Where to Get PostgreSQL
    4. Administration Tools
      1. psql
      2. pgAdmin
      3. phpPgAdmin
      4. Adminer
    5. PostgreSQL Database Objects
    6. What’s New in Latest Versions of PostgreSQL?
      1. Why Upgrade?
      2. Features Introduced in PostgreSQL 10
      3. Features Introduced in PostgreSQL 9.6
      4. Features Introduced in PostgreSQL 9.5
      5. Features Introduced in PostgreSQL 9.4
    7. Database Drivers
    8. Where to Get Help
    9. Notable PostgreSQL Forks
  3. 2. Database Administration
    1. Configuration Files
      1. Making Configurations Take Effect
      2. The postgresql.conf File
      3. The pg_hba.conf File
    2. Managing Connections
      1. Check for Queries Being Blocked
    3. Roles
      1. Creating Login Roles
      2. Creating 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
      2. Common Extensions
    7. Backup and Restore
      1. Selective Backup Using pg_dump
      2. Systemwide Backup Using pg_dumpall
      3. Restoring Data
    8. Managing Disk Storage with Tablespaces
      1. Creating Tablespaces
      2. Moving Objects Among Tablespaces
    9. Verboten Practices
      1. Don’t Delete PostgreSQL Core System Files and Binaries
      2. Don’t Grant Full OS Administrative Privileges 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. Retrieving Details of Database Objects
      4. Crosstabs
      5. Dynamic SQL Execution
    5. Importing and Exporting Data
      1. psql Import
      2. psql Export
      3. Copying from or 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. Autogenerating Queries from Table Definitions
      2. Accessing psql from pgAdmin3
      3. Editing postgresql.conf and pg_hba.conf from pgAdmin3
      4. Creating Database Assets and Setting Privileges
      5. Import and Export
      6. Backup and Restore
    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. Textuals
      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. Unnesting Arrays to Rows
      3. Array Slicing and Splicing
      4. Referencing Elements in an Array
      5. Array Containment Checks
    5. Range Types
      1. Discrete Versus Continuous Ranges
      2. Built-in Range Types
      3. Defining Ranges
      4. Defining Tables with Ranges
      5. Range Operators
    6. JSON
      1. Inserting JSON Data
      2. Querying JSON
      3. Outputting JSON
      4. Binary JSON: jsonb
      5. Editing JSONB data
    7. XML
      1. Inserting XML Data
      2. Querying XML Data
    8. Full Text Search
      1. FTS Configurations
      2. TSVectors
      3. TSQueries
      4. Using Full Text Search
      5. Ranking Results
      6. Full Text Stripping
      7. Full Text Support for JSON and JSONB
    9. Custom and Composite Data Types
      1. All Tables Are Custom Data Types
      2. Building Custom Data Types
      3. Composites and NULLs
      4. Building Operators and Functions for Custom Types
  7. 6. Tables, Constraints, and Indexes
    1. Tables
      1. Basic Table Creation
      2. Inherited Tables
      3. Partitioned Tables
      4. Unlogged Tables
      5. 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. ANY Array Search
      7. Set-Returning Functions in SELECT
      8. Restricting DELETE, UPDATE, and SELECT from Inherited Tables
      9. DELETE USING
      10. Returning Affected Records to the User
      11. UPSERTs: INSERT ON CONFLICT UPDATE
      12. Composite Types in Queries
      13. Dollar Quoting
      14. DO
      15. FILTER Clause for Aggregates
      16. Percentiles and Mode
    3. Window Functions
      1. PARTITION BY
      2. ORDER BY
    4. Common Table Expressions
      1. Basic CTEs
      2. Writable CTEs
      3. Recursive CTE
    5. Lateral Joins
    6. WITH ORDINALITY
    7. GROUPING SETS, CUBE, ROLLUP
  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
      3. Writing Window Functions in 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. Writing Better Queries
      1. Overusing Subqueries in SELECT
      2. Avoid SELECT *
      3. Make Good Use of CASE
      4. Using FILTER Instead of CASE
    4. Parallelized Queries
      1. What Does a Parallel Query Plan Look Like?
      2. Parallel Scans
      3. Parallel Joins
    5. Guiding the Query Planner
      1. Strategy Settings
      2. How Useful Is Your Index?
      3. Table Statistics
      4. Random Page Cost and Quality of Drives
    6. Caching
  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 Full Server Replication
      1. Configuring the Master
      2. Configuring the Slaves for Full Server Cluster Replication
      3. Initiating the Streaming Replication Process
      4. Replicating Only Some Tables or Databases with Logical Replication
    3. Foreign Data Wrappers
      1. Querying Flat Files
      2. Querying Flat Files as Jagged Arrays
      3. Querying Other PostgreSQL Servers
      4. Querying Other Tabular Formats with ogr_fdw
      5. Querying Nonconventional Data Sources
  12. A. Installing PostgreSQL
    1. Windows and Desktop Linux
    2. CentOS, Fedora, Red Hat, Scientific Linux
    3. Debian, Ubuntu
    4. FreeBSD
    5. macOS
  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

Product information

  • Title: PostgreSQL: Up and Running, 3rd Edition
  • Author(s): Regina O. Obe, Leo S. Hsu
  • Release date: October 2017
  • Publisher(s): O'Reilly Media, Inc.
  • ISBN: 9781491963364