Course Overview
This PostgreSQL developer training course is ideal for those looking to migrate to PostgreSQL or use Postgres in their applications. It was designed specifically for developers with SQL knowledge and experience, focuses on the features and functionality that are specific to PostgreSQL. Attendees will learn how to apply their SQL knowledge and experience to the PostgreSQL platform.
Course Prerequisites
It is expected that students have experience with SQL - including DDL, DML, and writing effective SQL is recommended
Outline
Introduction
- Installing & managing PostgreSQL
- Overview of PostgreSQL database
- Using the Psql client
Understanding PostgreSQL data types
- Numeric data types:
- Integer data types
- Floating point types
- Serial types
- Character types
- Binary data types
- Date & time values
- Boolean values
- Array types
- Other data types
Working with PostgresSQL
- Understanding sequences
- Creating & managing indexes
- Understanding PostgreSQL indexes
- Partial indexes
- Indexes on expressions
- Using COPY to load data
- Working with Array Values
Advanced SQL Functions
- Common Table Expressions (CTE)
- Using Window Functions
- Using Regular Expressions
- Using Recursive Queries
- Writing functions with PL/pgSQL
Writing triggers & stored procedures with PL/pgSQL
- Variables in procedures
- Performing loop operations
- The PERFORM and EXECUTE statements
- Triggers in PostgreSQL
Using the PostgreSQL query optimiser
- Analysing & optimising queries
- Viewing execution plans with EXPLAIN
- Understanding PostgreSQL query operators