PostgreSQL : up and running / Regina O. Obe and Leo S. Hsu.

Format:

Book

Author/Creator:

Obe, Regina O., author.

Hsu, Leo S., author.

Language:

English

Subjects (All):

PostgreSQL.

Database management.

Physical Description:

1 online resource (231 p.)

Edition:

Second edition.

Place of Publication:

Sebastopol, California : O'Reilly, [2015]

Language Note:

English

System Details:

text file

Summary:

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.

Contents:

Intro

Copyright

Table of Contents

Preface

Audience

What Makes PostgreSQL Special, and Why Use It?

Why Not PostgreSQL?

Where to Get Data and Code Used in This Book

For More Information on PostgreSQL

Code and Output Formatting

Conventions Used in This Book

Using Code Examples

Safari ® Books Online

How to Contact Us

Chapter 1. The Basics

Where to Get PostgreSQL

Administration Tools

psql

pgAdmin

phpPgAdmin

Adminer

PostgreSQL Database Objects

What's New in Latest Versions of PostgreSQL?

Why Upgrade?

What's New in PostgreSQL 9.4?

PostgreSQL 9.3: New Features

PostgreSQL 9.2: New Features

PostgreSQL 9.1: New Features

Database Drivers

Where to Get Help

Notable PostgreSQL Forks

Chapter 2. Database Administration

Configuration Files

postgresql.conf

pg_hba.conf

Reloading the Configuration Files

Managing Connections

Roles

Creating Login Roles

Creating Group Roles

Database Creation

Template Databases

Using Schemas

Privileges

Types of Privileges

Getting Started

GRANT

Default Privileges

Privilege Idiosyncrasies

Extensions

Installing Extensions

Common Extensions

Backup and Restore

Selective Backup Using pg_dump

Systemwide Backup Using pg_dumpall

Restore

Managing Disk Storage with Tablespaces

Creating Tablespaces

Moving Objects Between Tablespaces

Verboten Practices

Don't Delete PostgreSQL Core System Files and Binaries

Don't Give Full OS Administrative Rights to the Postgres System Account (postgres)

Don't Set shared_buffers Too High

Don't Try to Start PostgreSQL on a Port Already in Use

Chapter 3. psql

Environment Variables

Interactive versus Noninteractive psql

psql Customizations

Custom Prompts

Timing Executions

Autocommit Commands

Shortcuts.

Retrieving Prior Commands

psql Gems

Executing Shell Commands

Watching Statements

Lists

Importing and Exporting Data

psql Import

psql Export

Copy from/to Program

Basic Reporting

Chapter 4. Using pgAdmin

Overview of Features

Connecting to a PostgreSQL Server

Navigating pgAdmin

pgAdmin Features

Accessing psql from pgAdmin

Editing postgresql.conf and pg_hba.conf from pgAdmin

Creating Database Assets and Setting Privileges

Import and Export

pgScript

Graphical Explain

Job Scheduling with pgAgent

Installing pgAgent

Scheduling Jobs

Helpful pgAgent Queries

Chapter 5. Data Types

Numerics

Serials

Generate Series Function

Characters and Strings

String Functions

Splitting Strings into Arrays, Tables, or Substrings

Regular Expressions and Pattern Matching

Temporals

Time Zones: What They Are and Are Not

Datetime Operators and Functions

Arrays

Array Constructors

Referencing Elements in an Array

Array Slicing and Splicing

Unnesting Arrays to Rows

Range Types

Discrete Versus Continuous Ranges

Built-in Range Types

Defining Ranges

Defining Tables with Ranges

Range Operators

JSON

Inserting JSON Data

Querying JSON

Outputting JSON

Binary JSON: jsonb

XML

Inserting XML Data

Querying XML Data

Custom and Composite Data Types

All Tables Are Custom Data Types

Building Custom Data Types

Building Operators and Functions for Custom Types

Chapter 6. Tables, Constraints, and Indexes

Tables

Basic Table Creation

Inherited Tables

Unlogged Tables

TYPE OF

Constraints

Foreign Key Constraints

Unique Constraints

Check Constraints

Exclusion Constraints

Indexes

PostgreSQL Stock Indexes

Operator Classes

Functional Indexes.

Partial Indexes

Multicolumn Indexes

Chapter 7. SQL: The PostgreSQL Way

Views

Single Table Views

Using Triggers to Update Views

Materialized Views

Handy Constructions

DISTINCT ON

LIMIT and OFFSET

Shorthand Casting

Multirow Insert

ILIKE for Case-Insensitive Search

Returning Functions

Restricting DELETE, UPDATE, SELECT from Inherited Tables

DELETE USING

Returning Affected Records to the User

Composite Types in Queries

DO

FILTER Clause for Aggregates

Window Functions

PARTITION BY

ORDER BY

Common Table Expressions

Basic CTEs

Writable CTEs

Recursive CTE

Lateral Joins

Chapter 8. Writing Functions

Anatomy of PostgreSQL Functions

Function Basics

Triggers and Trigger Functions

Aggregates

Trusted and Untrusted Languages

Writing Functions with SQL

Basic SQL Function

Writing SQL Aggregate Functions

Writing PL/pgSQL Functions

Basic PL/pgSQL Function

Writing Trigger Functions in PL/pgSQL

Writing PL/Python Functions

Basic Python Function

Writing PL/V8, PL/CoffeeScript, and PL/LiveScript Functions

Basic Functions

Writing Aggregate Functions with PL/V8

Chapter 9. Query Performance Tuning

EXPLAIN

EXPLAIN Options

Sample Runs and Output

Graphical Outputs

Gathering Statistics on Statements

Guiding the Query Planner

Strategy Settings

How Useful Is Your Index?

Table Statistics

Random Page Cost and Quality of Drives

Caching

Writing Better Queries

Overusing Subqueries in SELECT

Avoid SELECT *

Make Good Use of CASE

Using Filter Instead of CASE

Chapter 10. Replication and External Data

Replication Overview

Replication Jargon

Evolution of PostgreSQL Replication

Third-Party Replication Options

Setting Up Replication

Configuring the Master

Configuring the Slaves.

Initiating the Replication Process

Foreign Data Wrappers

Querying Flat Files

Querying a Flat File as Jagged Arrays

Querying Other PostgreSQL Servers

Querying Nonconventional Data Sources

Appendix A. Installing PostgreSQL

Windows, Desktop Linux

CentOS, Fedora, Red Hat, Scientific Linux

Debian, Ubuntu

FreeBSD

Mac OS X

Appendix B. PostgreSQL Packaged Command-Line Tools

Database Backup Using pg_dump

Server Backup: pg_dumpall

Database Restore: pg_restore

psql Interactive Commands

psql Noninteractive Commands

Index

About the Authors.

Notes:

Includes index.

Description based on online resource; title from PDF title page (ebrary, viewed December 17, 2014).

ISBN:

9781449373184

1449373186

9781449373191

1449373194

9781449373177

1449373178

OCLC:

1024267988