Course description
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.
Course ObjectivesTo provide a broader set of PostgreSQL skills for someone who currently has a good working knowledge of PostgreSQL.
Upcoming start dates
Suitability - Who should attend?
This course assumes a good working knowledge of PostgreSQL SQL. This can be obtained by attendance on the pre-requisite SQL for PostgreSQL course.
Experience to the level as demonstrated in this course is recommended:
- SQL for PostgreSQL
Training Course Content
PostgreSQL for Developers Training Course
Course Contents - DAY 1
Course Introduction- Administration and course materials
- Course structure and agenda
- Delegate and trainer introductions
- Advanced Datatypes
- Data Type Conversion
- Revision of aggregate functions and basic GROUP BY and HAVING clauses
- ROLLUP extension
- CUBE extension
- GROUPING function
- GROUPING SETS function
- Ranking functions
- NTILE and WIDTH_BUCKET functions
- Analytic aggregates
- Windowing functions
- Row and Range specifications
- FIRST_VALUE and LAST_VALUE
- LAG and LEAD functions
- The WITH clause
- Recursive Common Table Expressions
- Match operators and notation
- Repetition operators
- Regular Expression functions
- Sub-expression grouping
- SQL Regular Expressions
- 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
- 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
- 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
- CREATE FUNCTION statement
- Function parameters
- Functions Returning Void
- Execute a function using PERFORM
- Listing Functions
- ALTER and DROP FUNCTION statements
- 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
- 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
- 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
- Import and Export Operations with COPY
- Export using the COPY Command
- Import using the COPY Command
Customer Outreach Award
We believe that it should be easy for you to find and compare training courses.
Our Customer Outreach Award is presented to trusted providers who are excellent at responding to enquiries, making your search quicker, more efficient and easier, too.
Continuing Studies
Further Learning
- PostgreSQL Administration
Request info
Why StayAhead. From a single person on a scheduled course to large scale training projects StayAhead Training have the expertise and experience to help. Established since 1992, StayAhead Training is recognised as one of the leading independent IT Training specialists...