PostgreSQL Development
Overview
Course Objective
By the end of this 3-day course, participants will be able to:
Deepen their understanding of PostgreSQL by mastering advanced SQL techniques and data processing capabilities.
Develop, manage, and optimize PL/pgSQL programs including stored procedures, functions, and triggers.
Implement complex queries using grouping sets, analytical functions, and recursive common table expressions.
Improve application performance through SQL tuning and query optimization strategies.
Utilize cursors, control structures, and exception handling to create efficient, robust PostgreSQL-based applications.
Manage transactions and concurrency effectively, understanding locking mechanisms and isolation levels.
Perform data import/export tasks using PostgreSQL’s COPY command for efficient bulk data handling.
Who Should Attend
Prerequisites
Training Calendar
Intake
Duration
Program Fees
Module
Module 1 - Introduction
• Administration and course materials
• Course structure and agenda
• Delegate and trainer introductions
Module 2 - Advanced SQL Language
• Advanced Datatypes
• Data Type Conversion
Module 3 - Enhanced Grouping Features
• Revision of aggregate functions and basic GROUP BY and HAVING
clauses
• ROLLUP extension
• CUBE extension
• GROUPING function
• GROUPING SETS function
Module 4 - 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
Module 5 - Recursive Queries
• The WITH clause
• Recursive Common Table Expressions
Module 6 - Regular Expression Support
• Match operators and notation
• Repetition operators
• Regular Expression functions
• Sub-expression grouping
• SQL Regular Expressions
Module 7 - 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
Module 8 - Program Logic
• IF THEN ELSIF ELSE Statements
• CASE Statements
• The Basic Loop Construct
• WHILE and FOR Loops
• Nested and Labelled Loops
• The CONTINUE Statemen
Module 9 - 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
Module 10 - 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
Module 11 - User-Defined Functions
• CREATE FUNCTION statement
• Function parameters
• Functions Returning Void
• Execute a function using PERFORM
• Listing Functions
• ALTER and DROP FUNCTION statements
Module 12 - 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
Module 13 - 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
Module 14 - 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
• Possible Causes of Contention
• Deadlocks
• Implicit and Explicit Locking of Tables and Rows
• Advisory Locks
• Lock Management Parameters
Module 15 - SQL Tuning
• Query Optimization
• Scan Methods
• Join Methods
• Join Order
• Statement Transformation
• Detect Slow Queries
• View Execution Plans using EXPLAIN
• Gather Optimizer Statistics with ANALYZE
• Gather Extended Statistics with CREATE STATISTICS
• Parameters that affect Optimization
• Memory Settings that affect Query Performance
• Overview of declarative Table Partitioning
Module 16 - Loading and Unloading Data
• Import and Export Operations with COPY
• Export using the COPY Command
• Import using the COPY Command
FAQs
General Questions:
Q: What is the PostgreSQL – Development course about?
A: This 3-day course provides developers with the knowledge and practical skills required to develop, optimize, and manage applications using PostgreSQL. It covers advanced SQL techniques, server programming, PL/pgSQL, performance tuning, and query optimization. Participants will also gain hands-on experience in working with triggers, procedures, functions, and transactions.
Q: Who should attend this course?
A: This course is designed for developers and programmers who already have a working knowledge of PostgreSQL and wish to deepen their understanding by learning advanced SQL techniques, server programming, and performance optimization.
Q: What are the prerequisites for this course?
A: Participants should have a good working knowledge of PostgreSQL SQL. Familiarity with basic SQL concepts can be gained by attending the prerequisite “SQL for PostgreSQL” course.
Q: How long is the course?
A: The course duration is 3 days.
Q: What key topics are covered in this course?
A: Key topics include:
Advanced SQL features such as grouping, analytical queries, and regular expressions
Recursive queries and windowing functions
PL/pgSQL programming, including procedures, triggers, and functions
Cursor management and exception handling
Transaction control, concurrency, and locking mechanisms
SQL query tuning, including performance optimization and execution plans
Data import/export using the COPY command
Q: Will I receive a certification after completing the course?
A: While this course provides the necessary skills for PostgreSQL development, it does not include a certification exam. A certificate of completion may be provided by the training provider.
Program Content & Skills:
Q: What skills will I gain from the PostgreSQL – Development course?
A: You will gain hands-on skills in advanced SQL techniques, server programming using PL/pgSQL, writing functions and procedures, working with triggers, optimizing queries, handling exceptions, managing transactions, and performance tuning for PostgreSQL applications.
Q: Will I learn how to optimize PostgreSQL performance?
A: Yes, the course covers query optimization, execution plans, join methods, scan methods, and techniques to enhance PostgreSQL performance, including the use of memory settings and optimizer statistics.
Q: Can I learn how to manage PostgreSQL triggers and procedures?
A: Yes, the course includes in-depth coverage of creating and managing triggers, stored procedures, and functions, including when to use them and how to handle exceptions effectively.
Q: Is transaction management included in the course?
A: Yes, the course covers transaction processing in PostgreSQL, including transaction control, isolation levels, multi-version concurrency control (MVCC), and locking mechanisms to ensure efficient database operation.
Q: Will I be able to extend PostgreSQL after the course?
A: Yes, you will learn to extend PostgreSQL by writing PL/pgSQL functions, creating stored procedures, and implementing triggers to enhance database capabilities. You will also gain experience with cursors and custom exception handling.
Submit your interest today !
