Module 1: The Basics.

Where to Get PostgreSQL

Administration Tools

psql

pgAdmin

phpPgAdmin

Adminer

PostgreSQL Database Objects

What’s New in the 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

 

Module 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

 

Module 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

 

Module 4: Using pgAdmin

Getting Started

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

Backup and Restore

pgScript

Graphical Explain

Job Scheduling with pgAgent

Installing pgAgent

Scheduling Jobs

Helpful pgAgent Queries

 

Module 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

 

Module 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

 

Module 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

 

Module 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

 

Module 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 Sub-queries in SELECT

Avoid SELECT

Make Good Use of CASE

Using Filter Instead of CASE

 

Module 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 Non-conventional Data Sources

Hi, How Can We Help You?