PostgreSQL Intermediate

Inquire now

This course develops the skills needed to build, query, and maintain PostgreSQL databases beyond the fundamentals. Participants learn to write complex SQL queries, design reliable data structures, create reusable database objects, manage transactions, work with semi-structured data, and improve query performance. The five-day duration provides additional scope for database programming, security, routine maintenance, and an integrated business database capstone.

 

 

Duration 5 Days – 35 hrs.

 

Objectives

  • Apply appropriate PostgreSQL data types, relationships, and constraints.
  • Write complex queries using joins, subqueries, and common table expressions.
  • Develop analytical queries using window functions.
  • Create and manage views and materialized views.
  • Query and manipulate arrays and JSONB data.
  • Manage transactions, isolation levels, and concurrent database operations.
  • Interpret execution plans and improve queries through appropriate indexing.
  • Develop PL/pgSQL functions, procedures, and triggers.
  • Configure role privileges and perform routine maintenance and logical backup operations.
  • Integrate intermediate PostgreSQL features into a practical database solution.

 

Target Audience 

  • Developers who build applications using PostgreSQL.
  • Database professionals progressing beyond introductory SQL.
  • Data analysts who need complex reporting queries.
  • Application support engineers responsible for database operations.
  • Technical professionals transitioning from another relational database platform.

  

Prerequisites 

  • Basic understanding of relational databases, tables, keys, and relationships.
  • Ability to write SQL statements using SELECT, INSERT, UPDATE, and DELETE.
  • Familiarity with basic joins, aggregate functions, GROUP BY, and HAVING.
  • Experience creating tables and applying primary and foreign keys.
  • Basic familiarity with PostgreSQL and either psql or pgAdmin.

 

 

 

Course Outline

 

Day 1: Data Structures and Complex SQL Queries

Module 1: PostgreSQL Foundations and Database Objects

  • Databases, schemas, and object organization
  • Working with psql and pgAdmin
  • Object naming and schema qualification
  • PostgreSQL data types and explicit type conversion
  • Identity columns and sequences
  • NULL handling and conditional expressions

 

Module 2: Relational Design and Data Integrity

  • Reviewing table relationships and normalization
  • Selecting appropriate data types
  • Primary key, foreign key, unique, check, and not-null constraints
  • Referential actions and cascading operations
  • Adding and modifying constraints
  • Identifying and resolving common data integrity issues

 

Module 3: Complex Joins, Subqueries, and Set Operations

  • Inner, outer, self, and cross joins
  • Managing duplicate rows in joined results
  • Correlated and non-correlated subqueries
  • EXISTS and NOT EXISTS
  • UNION, UNION ALL, INTERSECT, and EXCEPT
  • Combining query techniques for business reporting

 

 

Day 2: Analytical SQL and Reusable Data Access

Module 4: Common Table Expressions and Recursive Queries

  • Structuring queries using WITH
  • Combining multiple common table expressions
  • Data-modifying statements in WITH
  • Recursive query structure and termination
  • Querying hierarchical data
  • Readability and performance considerations

 

Module 5: Window Functions and Analytical Queries

  • Window functions compared with aggregate functions
  • PARTITION BY and window ordering
  • ROW_NUMBER, RANK, and DENSE_RANK
  • LAG and LEAD
  • Running totals and moving averages
  • Window frames and their effect on results
  • Combining window functions with common table expressions

 

Module 6: Views and Materialized Views

  • Creating, replacing, and removing views
  • Using views to simplify reusable queries
  • Updatable views and their limitations
  • WITH CHECK OPTION
  • Creating and refreshing materialized views
  • Indexing materialized views
  • Choosing between views and materialized views

 

 

Day 3: Data Manipulation and Transaction Management

Module 7: Intermediate Data Manipulation and Data Transfer

  • INSERT, UPDATE, and DELETE with RETURNING
  • Handling conflicts with INSERT … ON CONFLICT
  • Updating and deleting data using related tables
  • Bulk data loading considerations
  • Importing and exporting data with COPY and \copy
  • Handling delimiters, NULL values, and file formats

 

Module 8: Arrays and JSONB

  • Creating, accessing, and querying arrays
  • Array operators and functions
  • Expanding arrays using UNNEST
  • JSON and JSONB fundamentals
  • Extracting and filtering JSONB values
  • Updating JSONB documents
  • Choosing between relational columns, arrays, and JSONB

 

Module 9: Transactions and Concurrency

  • Transaction boundaries and ACID properties
  • BEGIN, COMMIT, ROLLBACK, and savepoints
  • Multiversion concurrency control
  • Transaction isolation levels
  • Row locking and SELECT … FOR UPDATE
  • Blocking and deadlocks
  • Transaction retry considerations
  • Keeping transactions efficient and consistent

 

 

Day 4: Query Performance and Database Programming

Module 10: Execution Plans and Query Optimization

  • Reading EXPLAIN and EXPLAIN ANALYZE output
  • Estimated rows, actual rows, and execution costs
  • Sequential scans, index scans, and bitmap scans
  • Join strategies and sorting operations
  • Identifying inefficient filters and joins
  • Planner statistics and the role of ANALYZE
  • Comparing query plans before and after changes

 

 Module 11: Index Design and Management

  • B-tree indexes and common use cases
  • Overview of GIN, GiST, and BRIN indexes
  • Multicolumn indexes and column order
  • Expression and partial indexes
  • Covering indexes and index-only scans
  • Indexing JSONB data
  • Index maintenance overhead and trade-offs
  • Identifying unused and overlapping indexes

 

Module 12: PL/pgSQL Functions, Procedures, and Triggers

  • Choosing between functions and procedures
  • Parameters, variables, and return values
  • Conditional logic and loops
  • Returning query results
  • Basic exception handling
  • Trigger functions and trigger events
  • Row-level and statement-level triggers
  • Appropriate uses and limitations of database programming

 

 

Day 5: Security, Maintenance, and Integrated Capstone

Module 13: Roles and Access Control

  • Login roles and group roles
  • Object ownership and role membership
  • Database, schema, table, and sequence privileges
  • GRANT and REVOKE
  • Default privileges
  • Applying least-privilege access
  • Managing access for application and reporting users

 

Module 14: Routine Maintenance and Logical Backup

  • VACUUM, autovacuum, and ANALYZE fundamentals
  • Dead tuples and table maintenance
  • Inspecting sessions using pg_stat_activity
  • Reviewing table and index statistics
  • Identifying long-running queries and blocked sessions
  • Logical backups using pg_dump
  • Restoring backups using pg_restore and psql
  • Backup scope, dependencies, and restore verification

 

Module 15: Capstone — Sales and Inventory Database

  • Extend a supplied sales and inventory schema with appropriate constraints.
  • Develop reporting queries using common table expressions and window functions.
  • Create a reporting view or materialized view.
  • Store and query flexible product attributes using JSONB.
  • Implement a transactional order operation with stock validation.
  • Create a function, procedure, or trigger for a defined business requirement.
  • Examine query execution plans and apply suitable indexes.
  • Configure privileges for application and reporting roles.
  • Produce a logical backup and verify restoration in a separate database.

 

Inquire now

Best selling courses

Duration: 5 days – 35 hrs   Overview The “SOC Network and Threat Detection and Analysis” training course is designed to equip Security Operations Center (SOC) analysts and IT security professionals with the skills and knowledge required to detect, analyze, and respond to network threats effectively. This comprehensive course covers essential topics such as threat...

Duration 1 day – 7 hrs   Overview   This 1-day training builds upon basic warehouse operations knowledge and introduces key logistics concepts involved in the movement and coordination of goods—especially wet and dry food items—within and outside the warehouse. Participants will explore transport logistics, inbound and outbound coordination, documentation practices, and cold chain considerations,...

Duration 2 days – 14 hrs   Overview   This hands-on course provides an introduction to Splunk, a powerful platform for searching, monitoring, and analyzing machine-generated data. The training focuses on how developers and QA professionals can leverage Splunk to gain insights from logs and metrics, improve application observability, detect anomalies, and support test validation....

Duration 3 days – 21 hrs   Overview.   This course is designed for fresh graduates aspiring to build a career in Data Science. It introduces the fundamentals of data science, focusing on data analysis, visualization, and basic machine learning concepts using Python. The course provides hands-on practice with real-world datasets, equipping participants with the...

Duration 5 days – 35 hrs   Overview   The Microsoft Certified: Azure Administrator Associate (AZ-104) Training Course is designed for IT professionals who want to develop expertise in managing Microsoft Azure environments. This course provides hands-on training on deploying, managing, and monitoring Azure services, including virtual machines, networking, storage, identity, and security.   Objectives...

ARTIFICIAL INTELLIGENCE / MACHINE LEARNING / DEEP LEARNING

Capstone Workshop: Build Your Own GenAI Toolkit TECH AI

GenAI Toolkit Training is a practical, hands-on capstone workshop designed to help participants bring their generative AI knowledge together into a personalized toolkit of AI tools, prompts, workflows, and practical solutions. Rather than simply learning individual AI platforms, participants will learn how to select and combine the right tools to address real-world technical and professional...

In this course, you will gain practical foundation level training that enables immediate and effective participation in big data and other analytics projects. Learn ways of storing data that allow for efficient processing and analysis, and gain the skills you need to store, manage, process, and analyze massive amounts of unstructured data to create an appropriate data lake.

Advanced Laravel Engineering Scalable Architecture, Performance, Testing, Queues & DevOps equips developers with the skills to design scalable Laravel applications, optimize performance, implement automated testing, manage queues, and integrate DevOps practices for reliable, production-ready deployments.   Duration 5 Days – 35 hrs   Overview This advanced-level bootcamp is designed for teams building large-scale or mission-critical...

// BG EARTH WHEN NOT PLAYING

We use cookies on our website to personalize your experience by storing your preferences and recognizing repeat visits. By clicking “Accept”, you agree to the use of all cookies. You can also select “Cookie Settings” to adjust your preferences and provide more specific consent. Cookie Policy