Databases · DBC-50

PostgreSQL Administration & SQL Query for Reporting 2026

PostgreSQL Administration & SQL Query for Reporting 2026 is a hands-on course that covers just enough PostgreSQL 18 administration for reporting work, from installation, read-only roles and backups to analytical SQL with joins, CTEs and window functions. It suits report writers, data analysts and MIS teams, and you leave with an Excel report that refreshes directly from PostgreSQL.

Updated 6 Oct 2026

From 8,010 THB / person 8,900 −10% excl. VAT 7% · group rates available

Corporate training quote Join the waiting list

PDFDownload the course outline

Duration18 hours · 3 days
FormatOnsite / live online
Next roundOn request
CertificateIncluded

Course overview

In 2026 PostgreSQL is the main database of many organisations, both in newly built systems and in systems migrated from Oracle, SQL Server or MySQL. Reporting teams therefore need to pull data from PostgreSQL directly, yet many still wait for IT to send them files, or can only write basic queries. Complex reports such as running totals, month-on-month comparisons or multi-level summaries end up being finished by hand in Excel, which is slow and error-prone.

This course is adapted from the PostgreSQL Administration 2026 course for people who build reports. Day one covers only the administration they need: architecture, installing PostgreSQL 18, designing read-only access for reporting, and backups. The next two days focus on SQL for extracting and analysing data, from JOIN, GROUP BY and CTEs to window functions, ROLLUP/GROUPING SETS and materialised views. It closes with CSV export and connecting Excel / Power Query through psqlODBC, so learners can build reports that refresh themselves instead of copying data by hand. (3 days, 6 hours per day, 18 hours in total, Beginner to Intermediate level.)

What you’ll gain

Understand PostgreSQL architecture and its version support lifecycle
Install and use PostgreSQL 18 confidently through psql and pgAdmin 4
Design roles, schemas and read-only privileges for reporting, and back up and restore with pg_dump and pg_restore
Write correct SQL that pulls data from several tables with JOIN, subqueries and CTEs
Use date, text and numeric functions to format data for reports
Build analytical reports with window functions, ROLLUP and GROUPING SETS
Create views and materialised views as a reporting layer, and check basic query performance
Export data to CSV and connect Excel / Power Query to PostgreSQL

Who this course is for

Report writers, data analysts and MIS teams who need to pull data from PostgreSQL
Teams that already build reports in Excel and want to query the database directly instead of waiting for files
IT staff assigned to look after the organisation's reporting database
Organisations that have moved to PostgreSQL and want their reporting team to use it safely

Prerequisites

Basic SQL such as SELECT, WHERE and ORDER BY
Microsoft Excel skills at the level of everyday reporting
Able to use a computer and install software; no Linux or server administration experience needed
A Windows or macOS laptop with at least 8 GB RAM, 20 GB free disk space and permission to install software

Curriculum

Course Details

This course builds on and adapts the PostgreSQL Administration 2026 course for people who build reports. It keeps the administration topics that affect data access on day one, and drops high availability, replication, version upgrades and Kubernetes in favour of two days of SQL for extracting and analysing data. It runs for 3 days, 6 hours per day (18 hours in total, 09:00-16:00), as public classes or in-house training, as a continuous workshop on a single sample NovaMart database. Beginner to Intermediate level. Every lab runs on the learner's own laptop with Docker; the ODBC lab needs the learner's own Microsoft Excel for Windows with Power Query. Learners take home daily lab guides, SQL files and the executive report workbook built in the workshop.

Day 1 Installation, Architecture & Access Control for Reporting

Section 1: PostgreSQL Architecture and Version Lifecycle

How clusters, databases, schemas and tables relate to each other
Main processes, shared buffers and WAL in brief, and MVCC and why vacuum is needed
The 5-year support policy, PostgreSQL 18 as the current stable release, and PostgreSQL 19 in beta
The risks of running a version that is out of support

Section 2: Lab: Installing PostgreSQL 18 and the Tools

Run PostgreSQL 18 with Docker and a volume for persistent data
Use psql like a professional: l, dn, dt, d, x and display settings
Use pgAdmin 4: register a server, the Query Tool and the ERD Tool for reading table structures
Load the NovaMart sample database and explore its tables

Section 3: Configuration That Affects Reporting

postgresql.conf, ALTER SYSTEM and the difference between reload and restart
work_mem and its effect on sorting and aggregating large report queries
statement_timeout to stop report queries that run too long
Logging settings that capture slow queries for analysis

Section 4: Roles, Privileges and Schemas for the Reporting Team

Roles, users and groups in PostgreSQL, and privilege inheritance
GRANT and REVOKE at database, schema, table and column level
A read-only reporting role, and ALTER DEFAULT PRIVILEGES for future tables
Predefined roles such as pg_read_all_data and pg_monitor
Configure pg_hba.conf and SCRAM-SHA-256 so users can connect remotely

Section 5: Lab: Basic Backup and Restore

pg_dump and pg_restore: file formats and restoring selected tables
pg_dumpall for backing up roles and global objects
Keeping a separate copy of the database for reporting, apart from the production system
ANALYZE and the statistics the query planner uses after a large data load

Section 6: Workshop Day 1: NovaMart Sales Reporting

Install PostgreSQL 18 with Docker and load the NovaMart database
Create the sales and report schemas
Create a read-only report_reader role and test connecting with it from pgAdmin 4
Workshop: back up and restore the database ready for the next day

Day 2 SQL Query Fundamentals for Reporting

Section 7: Understanding the Data Before Writing Queries

Read an ERD: primary keys, foreign keys and one-to-many relationships
Common data types in reports: integer, numeric, text, date, timestamp and timestamptz
What NULL means and how it affects comparisons and calculations
Review SELECT, WHERE, ORDER BY, LIMIT and DISTINCT

Section 8: Filtering and Formatting Data

The IN, BETWEEN, LIKE/ILIKE and IS NULL operators
CASE WHEN for grouping data, such as purchase bands or customer tiers
Date functions: date_trunc, extract, interval, to_char and AT TIME ZONE
Text and numeric functions: concat, trim, round and type conversion with CAST
COALESCE and NULLIF for handling empty values and avoiding division by zero

Section 9: Combining Tables with JOIN

INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN
Self joins and joining more than two tables
Common mistakes: duplicated rows from joining at the wrong grain, and inflated totals
Finding unmatched rows, such as customers who never ordered or products with no sales

Section 10: Summarising Data with Aggregates

COUNT, SUM, AVG, MIN, MAX and COUNT(DISTINCT)
GROUP BY on several columns, and HAVING to filter the summary
The FILTER clause to count or sum several conditions in one query
string_agg to combine a list into a single text value

Section 11: Subqueries and CTEs

Subqueries in WHERE, FROM and SELECT
EXISTS and NOT EXISTS
WITH (CTEs) to break complex queries into steps that are easy to read and check
generate_series to build a calendar table so months with no sales still appear in the report

Section 12: Workshop Day 2: Sales Report Queries

Monthly sales by branch and product category
Top 10 products, and customers who bought only once
Monthly sales that show every month even when a month has no sales
Workshop: check that the totals in every report are correct

Day 3 Analytical SQL, Performance & Report Delivery

Section 13: Window Functions for Analytical Reports

OVER, PARTITION BY and ORDER BY, and how they differ from GROUP BY
ROW_NUMBER, RANK and DENSE_RANK for ranking within groups
Running totals and moving averages
LAG and LEAD for month-on-month and year-on-year comparisons
NTILE to segment customers by spend

Section 14: Multi-Level Summaries and Pivot Tables

ROLLUP for subtotals and grand totals
CUBE and GROUPING SETS for multi-dimensional summaries in one query
The GROUPING function to tell total rows apart from detail rows
Pivot tables with FILTER and with crosstab from the tablefunc extension

Section 15: Views and Materialised Views as a Reporting Layer

Create views in the report schema and grant the reporting team access to views only
Materialised views for reports that are expensive to compute
REFRESH MATERIALIZED VIEW and CONCURRENTLY so users are not blocked
Naming and organising reporting views

Section 16: Report Query Performance

Read query plans with EXPLAIN and EXPLAIN ANALYZE
Indexes that help reports: date columns, join columns and composite indexes
Write conditions that can use an index, and avoid wrapping columns in functions unnecessarily
Find resource-heavy queries with pg_stat_statements and view sessions with pg_stat_activity

Section 17: Lab: Exporting Data and Connecting Excel

Export CSV with copy and COPY, and the difference between them
Make Thai text display correctly when the CSV is opened in Excel (UTF-8 encoding)
Export results from the pgAdmin 4 Query Tool
Install psqlODBC and create a data source for the report_reader role
Pull data from views into Excel with Power Query and set up refresh

Section 18: Master Workshop: NovaMart Executive Report

Build the report schema: month-on-month sales, product rank by category and running totals by branch
Create a materialised view for a multi-level summary report
Check EXPLAIN ANALYZE and add indexes for slow queries
Workshop: export CSV and build an executive Excel report that refreshes through Power Query

Schedule & training options

For individuals — public rounds

No public rounds are open right now. Join the waiting list and we will contact you first when the next round opens, or ask us on LINE. Or call 02-570-8449 or 088-807-9770

Join the waiting list Ask about rounds

For organisations — in-house / private

Tailor the content to your team’s tools and projects
Your dates, at your office or live online
Quotation with tax ID for procurement

Corporate training quote

Instructors

Samit KoyomFounder & CEO, IT Genius Engineering

Frequently asked questions

Who is PostgreSQL Administration & SQL Query for Reporting 2026 for, and what background is needed?

Built for Report writers, data analysts and MIS teams who need to pull data from PostgreSQL · Teams that already build reports in Excel and want to query the database directly instead of waiting for files · IT staff assigned to look after the organisation's reporting database Background you should have: Basic SQL such as SELECT, WHERE and ORDER BY · Microsoft Excel skills at the level of everyday reporting Not sure the fit is right? Talk to our team on LINE @itgenius or call 02-570-8449.

How much does PostgreSQL Administration & SQL Query for Reporting 2026 cost and how long does it run?

THB 8,900 (currently THB 8,010 on promotion). The course runs 18 hours. The price excludes 7% VAT (for payment in a company's name). Pay by bank transfer to the company account, confirm it on our payment page, and we can issue the receipt or tax invoice in your company's name.

Do I get a certificate?

Yes. Everyone who completes the course receives a Certificate of Completion from IT Genius Institute. Each certificate carries its own number, and anyone holding that number can verify it online on our certificate page, so you can add it to your portfolio or pass it to HR as evidence of training.

Where does the training take place, and is there an online option?

You can attend onsite at IT Genius Institute or arrange to join online, and we also run it as a private in-house session for your team. Ask about dates and venues on LINE @itgenius or call 02-570-8449.

What if I fall behind or miss a session — can I retake it?

Yes. You may retake the same course free of charge in a later round, under the institute's conditions. Tell our team which course and round you attended, and we will check it and offer you the rounds that still have seats. Ask us on LINE @itgenius or call 02-570-8449.

How do I enrol, or request a quotation for my company?

Enrol online with the registration form on this page. You can register several attendees at once and enter your tax ID and billing address for the tax invoice. Or request a company quotation straight from the quote button. For anything else call 02-570-8449 or reach us on LINE @itgenius.

Articles are published in Thai.