PostgreSQL for Developers

Course Description

This hands-on three-day course provides the skills required to develop efficient PostgreSQL code. Participants will explore advanced PostgreSQL features used in modern applications, create stored routines, improve performance and work with real data. The course covers PL/ pgSQL, triggers, common table expressions and query optimisation through structured practical exercises. Participants will gain the knowledge to apply the techniques directly in their day-to-day roles and gain the capability to deliver faster, more reliable PostgreSQL applications.
3 Days
Contact us for pricing
 

Prerequisites

You should have a solid understanding of SQL and some practical experience with PostgreSQL. Attendance on our SQL for PostgreSQL course, or a similar level of knowledge would be suitable.

Day One

Session 1: ADVANCED SQL LANGUAGE
Advanced Datatypes
Data Type Conversion

Session 2: ENHANCED GROUPING FEATURES
Revision of aggregate functions and basic GROUP BY and HAVING clauses
ROLLUP extension
CUBE extension
GROUPING function
GROUPING SETS function

Session 3: ANALYTICAL QUERIES
Ranking functions
NTILE and WIDTH_BUCKET functions
Analytic aggregates
Windowing functions
Row and Range specifications
FIRST_VALUE and LAST_VALUE
LAG and LEAD functions

Session 4: RECURSIVE QUERIES
The WITH clause
Recursive Common Table Expressions

Session 5: REGULAR EXPRESSION SUPPORT
Match operators and notation
Repetition operators
Regular Expression functions
Sub-expression grouping
SQL Regular Expressions

Session 6: SERVER PROGRAMMING BASICS
Extending SQL with Procedural Code
Basic Elements
Variables and Constants
Data Types
Initialising Variables and Assigning Values
Using DML Statements in Code
Generating Output

Day Two


Session 7: PROGRAM LOGIC
IF THEN ELSIF ELSE Statements
CASE Statements
The Basic Loop Construct
WHILE and FOR Loops
Nested and Labelled Loops
The CONTINUE Statement


Session 8: CURSORS
What is a Cursor?
Implicit and Explicit Cursors
Cursor Operations
Declaring, Opening and Closing Cursors
Fetching Rows
Status Checking
Where current of clause
Unbound cursors (refcursors)
The Cursor FOR Loop
Parameterised Cursors

Session 9: EXCEPTION HANDLING
Errors and Messages
The EXCEPTION clause
System Raised Exceptions
The RAISE statement
STRICT option in a SELECT ..INTO
Programmer Raised Exceptions
The GET STACKED DIAGNOSTICS command
Nested and Labelled Blocks
Scope of Variables and Cursors

Session 10: USER-DEFINED FUNCTIONS
CREATE FUNCTION statement
Function parameters
Functions Returning Void
Execute a function using PERFORM
Listing Functions
ALTER and DROP FUNCTION statements

Session 11: USER-DEFINED PROCEDURES
CREATE PROCEDURE statement
Procedure parameters
Invoke a procedure using CALL
Definer's and Invoker's rights execution
ALTER and DROP PROCEDURE statements

Day Three

Session 12: TRIGGERS
DML Triggers
The Trigger Function
The CREATE TRIGGER Statement
BEFORE, AFTER and INSTEAD OF Triggers
The OLD and NEW qualifiers
Errors in Triggers
DDL and Database Event Triggers
Managing Triggers
Privileges to create Triggers

Session 13: TRANSACTIONS AND CONCURRENCY
Overview of Transaction Processing in PostgreSQL
Transaction Control
COMMIT, ROLLBACK and SAVEPOINT statements
AUTOCOMMIT
Multi-version Concurrency Control (MVCC)
Transaction Isolation Levels
Locking Concepts
Implicit and Explicit Locking of Tables and Rows
Possible Causes of Contention
Deadlocks
Advisory Locks
Lock Management Parameters

Session 14: SQL TUNING
Query Optimisation
Scan Methods
Join Methods
Join Order
Statement Transformation
Detect Slow Queries
View Execution Plans using EXPLAIN
Gather Optimizer Statistics with ANALYSE
Gather Extended Statistics with CREATE STATISTICS
Parameters that affect Optimisation
Memory Settings that affect Query Performance
Overview of declarative Table Partitioning

Session 15: LOADING AND UNLOADING DATA
Import and Export Operations with COPY
Export using the COPY Command
Import using the COPY Command

SQLPostgresPostgreSQL