PostgreSQL for Developers

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

Improving Query PerformanceForeign Data Wrappers