Duration
14 hours (usually 2 days including breaks)
Requirements
A working knowledge of SQL
Overview
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.
Course Outline
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