PostgreSQL for Developers

A working knowledge of SQL

This course provides programmatic interaction with PostgreSQL databases. Learn techniques, syntaxes and structures needed to develop quality applications using PostgreSQL backend. This training also covers SQL Tuning covering best practices for writing efficient SQL.

Target audience includes developers who want to use or extend PostgreSQL, as well as database architects.

Introduction to PostgreSQL

• A Brief History of PostgreSQL
• Features
• Internals Summary
• Limits and Terminology

Installation and Configuration

• Pre-requisites
• Installation from Packages and Creating Database
• Installation from Source Code
• Client Installation
• Starting and Stopping a Database Server
• Environment Setup

The SQL Language

• SQL Syntax
• Data Definition
• Data Manipulation
• Queries
• Data Types
• JSON
• Functions and Operators
• Type Conversion
• Indexes

Transactions and Concurrency

• Transactions and Isolation
• Multi-Version Concurrency Control

Client Interfaces

• Command Line Interface - psql
• Graphical Interface - pgadmin4

Server Programming

• Extending SQL
• Triggers
• The Rule System
• Procedural Languages
• PL/pgSQL - SQL Procedural Language
• Error Handling
• Cursors

Foreign Data Wrappers

• Extension in PostgreSQL
• Adding FDW in a Database
• postgres_fdw
• file_fdw
• Other FDWs

SQL Tuning

• Logging in PostgreSQL
• Query Plans
• Optimizing Queries
• Statistics
• Planner Parameters
• Parallel Query Scans
• SQL Best Practices
• Indexes
• Table Partitioning