PostgreSQL Advanced Course

Training process

Training needs analysis

If you have specific requirements regarding the training programme, we will carry out a training needs analysis for you. This will guide us on which aspects of the programme should receive greater emphasis, so that the training programme meets your specific needs.

What will you gain?

Better data modeling - You will learn how to design 1:1, 1:n and m:n relationships and choose data types that keep your database consistent, readable and easier to extend and report on later.

Advanced SQL queries - You will write more precise queries with subqueries, joins, CTEs and conditional expressions, so you can prepare complex analyses and reports faster and with more confidence.

Clear data reporting - You will master aggregations, STRING_AGG, and ROLLUP, CUBE and GROUPING SETS, so you can build cross-sectional reports and summaries without moving data to other tools.

Confident data operations - You will practice INSERT, UPDATE, DELETE and MERGE, which will help you handle data changes more safely and organize your day-to-day work with PostgreSQL records more effectively.

Import, export and JSON - You will see how to analyze PostgreSQL data in Excel and use JSON functions, so you can process semi-structured data more easily and exchange it between systems with less effort.

Faster queries - You will learn how to read execution plans with EXPLAIN and choose indexes for specific query patterns, helping you remove bottlenecks and improve overall database performance.

Practical window analytics - You will use window functions such as RANK, LAG, LEAD, ROW_NUMBER and NTILE to compare rows, track changes and build analytical queries without overly complex SQL code.

Confidence with PostgreSQL - You will organize your knowledge of schemas, database objects, logical operators and regular expressions, so you can read existing SQL faster and write your own queries more reliably.

Training programme

1. Comprehensive introduction to PostgreSQL

1:1, 1:n, m:n models and joins,
schemas, database objects,
data types and their impact on modeling and query execution,
logical operators AND, OR, BETWEEN, EXISTS, IN, LIKE, NOT, SOME, ALL – review.

2. Issues related to creating advanced SQL queries and reports

data filtering,
basic DISTINCT functions,
correlated and uncorrelated subqueries,
joins,
Common Table Expression (CTE), that is the WITH construct,
aggregate functions MAX, MIN, AVG, COUNT, SUM,
multidimensional grouping:
- Rollup,
- Cube,
- Grouping Sets,
grouping with the STRING_AGG() function,
conditional expressions:
- CASE,
- COALESCE,
- NULLIF,
- GREATEST and LEAST,
REGEX regular expressions.

3. Detailed discussion of CRUD operations (Create, Read, Update, Delete) in PostgreSQL

INSERT, UPDATE, DELETE, MERGE.

4. Practical techniques for importing and exporting data in PostgreSQL

data analysis from Postgres in Excel,
JSON – functions handling data stored in JSON format.

5. Advanced query performance optimization techniques in PostgreSQL

overview of indexes,
performance analysis using Explain.

6. Introduction to advanced data analysis techniques in PostgreSQL

RANK / DENSE_RANK / PERCENT_RANK,
FIRST_VALUE / LAST_VALUE,
LAG / LEAD,
NTILE,
NTH_VALUE,
ROW_NUMBER,
CUME_DIST.

What are the prerequisites for participating in the training?

SQL basics - You should be comfortable writing simple SELECT queries with filtering, sorting and basic GROUP BY, so you can focus on advanced topics instead of absolute SQL fundamentals.

Relational database knowledge - You should understand tables, columns, primary and foreign keys, and relationships between data, because the training expands these ideas into more complex database models.

Hands-on data work - It helps if you have experience reading and modifying data in a database, because the training includes work with queries, reports, and INSERT, UPDATE and DELETE operations.

Basic PostgreSQL exposure - You should have some basic experience with PostgreSQL or a similar SQL engine, so you can navigate schemas, functions, indexes and query plan analysis without difficulty.