25,000+ Courses Nationwide
0345 4506120

PostgreSQL for Developers

This course covers important basic and intermediate level details for developers interacting with PostgreSQL. Delegates will learn techniques, syntax and structures needed to develop quality applications using the PostgreSQL backend. This course also covers SQL Tuning best practices for writing efficient SQL.

The target audience includes developers and programmers who want to extend their knowledge of PostgreSQL to encompass advanced SQL techniques and the usage of Stored Program Units such as triggers, procedures and functions.

Exercises and examples are used throughout the course to give practical hands-on experience with the techniques covered.

Who will the Course Benefit?

Anyone who has a working knowledge of PostgreSQL but needs to enhance this to include PL/pgSQL, additional functions, and techniques to monitor and optimise query performance.

Select specific date to see price, venue and full details.

Learning Objectives

To provide a broader set of PostgreSQL skills for someone who currently has a good working knowledge of PostgreSQL.

The delegate will learn and acquire skills as follows:

  • Advanced datatypes and datatype conversion
  • Using regular expression patterns and functions
  • Using Analytical functions
  • Using extensions to the Group By clause
  • Using WITH clauses and Common Table Expressions (CTE)
  • Working with transaction management
  • Creating Stored Program Units with PL/pgSQL
  • Using best practice options to optimise query execution
  • Loading and Unloading data with the COPY option

Pre-Requisites

Requirements

This course assumes a good working knowledge of PostgreSQL SQL. This can be obtained by attendance on the pre-requisite SQL for PostgreSQL course.

Pre-Requisite Courses

    • SQL for PostgreSQL

Follow-On Courses

    • PostgreSQL Administration
Notes:
  • Course technical content is subject to change without notice.
  • Course content is structured as sessions, this does not strictly map to course timings. Concepts, content and practicals often span sessions.

Course Content

Course Contents - DAY 1

Course Introduction

  • Administration and course materials
  • Course structure and agenda
  • Delegate and trainer introductions

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

Course Contents - DAY 2

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

Course Contents - DAY 3

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 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

Session 15: LOADING AND UNLOADING DATA

  • Import and Export Operations with COPY
  • Export using the COPY Command
  • Import using the COPY Command

Related Courses

Privacy Notice

In order to provide you with the service requested we will need to retain and use your contact information in accordance with our Privacy Notice. If you choose to provide us with this information you explicitly consent to us using the information as necessary to provide the requested service to you. If you do not agree please do not proceed to request the service from us.

Marketing Permissions

Would you like to receive our newsletter and other information on products and services which we think will be of interest to you by email. We will always treat your information with care and in accordance with our Privacy Notice. You are free to withdraw this permission at any time.

 

We work with the best