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