Duration 3 days – 21 hours
Overview
This Beginner-to-Intermediate PostgreSQL Training Course is designed to build strong foundational skills in PostgreSQL while preparing participants to confidently work with real-world database tasks in modern environments.
Participants will learn how PostgreSQL works, how to write efficient SQL queries, how to design and manage database structures, and how to handle basic administration tasks such as user access, performance basics, backup/restore, and troubleshooting.
Since your organization is already exposed to PostgreSQL, this course also includes practical comparisons to other databases (MySQL, SQL Server, Oracle concepts) so learners understand transferable skills and can adapt faster as technology evolves.
Objectives
- Understand PostgreSQL architecture and core database concepts
- Create and manage tables, relationships, and constraints properly
- Write SQL queries using filtering, joins, grouping, and functions
- Use PostgreSQL features like views, indexes, sequences, and transactions
- Apply intermediate querying techniques (subqueries, CTEs, window functions intro)
- Perform basic administration tasks: users, roles, access control, schemas
- Execute simple performance tuning techniques using EXPLAIN and indexing basics
- Perform database backup and restore using PostgreSQL tools
- Understand how PostgreSQL compares with other DB platforms and where it fits best
Target Audience
- Junior Developers / Software Engineers
- QA / Test Engineers who query databases
- Business Analysts / Reporting Analysts
- IT Support / Application Support Engineers
- DevOps / Cloud Engineers (who handle database deployments)
- Junior DBAs / Database Administrators
- Anyone transitioning into database roles
Prerequisites
- Basic computer skills
- Basic understanding of data concepts (tables, rows, columns) is helpful
- No prior PostgreSQL experience required
- No programming background required (but helpful)
Course Content
Day 1 — PostgreSQL Fundamentals + Core SQL
Module 1: Database & PostgreSQL Fundamentals
- What is PostgreSQL and why it’s widely used
- RDBMS concepts: tables, rows, columns, keys
- PostgreSQL vs MySQL vs SQL Server (high-level comparison)
- PostgreSQL ecosystem tools overview
- pgAdmin, psql, DBeaver (optional)
Module 2: PostgreSQL Setup & Navigation
- Installing PostgreSQL (local or remote setup overview)
- Connecting to database servers
- Understanding databases, schemas, and namespaces
- Using pgAdmin and psql basics
Module 3: SQL Basics (CRUD)
- SELECT, FROM, WHERE
- ORDER BY, LIMIT
- INSERT, UPDATE, DELETE
- Handling NULL values properly
- Common operators and expressions
Module 4: Filtering + Built-in Functions
- LIKE / ILIKE, IN, BETWEEN
- String functions, numeric functions
- Date/time functions (NOW, CURRENT_DATE, interval)
- Formatting output for reporting needs
Hands-on Lab: Query a sample dataset + mini reporting exercise
Day 2 — Data Modeling + Joins + Constraints
Module 5: Data Types & Table Design Best Practices
- Key PostgreSQL data types (text, numeric, boolean, timestamps, UUID)
- Choosing correct data types for performance & integrity
- Normalization basics: 1NF–3NF (practical version)
- Naming conventions and schema design standards
Module 6: Creating Objects (DDL Mastery)
- CREATE TABLE and ALTER TABLE
- PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, DEFAULT
- Identity/auto-increment (SERIAL vs GENERATED AS IDENTITY)
- Understanding referential integrity
Module 7: Joins & Relationships
- INNER JOIN, LEFT JOIN, RIGHT JOIN
- Join patterns (1-to-many, many-to-many)
- JOIN pitfalls and how to avoid duplicates
- When to use EXISTS vs JOIN
Module 8: Aggregation and Grouping
- COUNT, SUM, AVG, MIN/MAX
- GROUP BY + HAVING
- Aggregation with joins (real-world reporting scenario)
Hands-on Lab: Build a small relational schema + generate summary reports
Day 3 — Intermediate SQL + Performance + Basic Admin
Module 9: Intermediate Querying Techniques
- Subqueries (scalar, correlated subqueries)
- Common Table Expressions (CTEs)
- Intro to window functions (ROW_NUMBER, RANK, PARTITION BY)
- Views vs Materialized Views (concept + use case)
Module 10: Indexing & Query Performance Basics
- Why indexes matter
- Creating and using indexes (B-tree intro)
- EXPLAIN and EXPLAIN ANALYZE basics
- Common performance mistakes (SELECT *, missing indexes, wrong joins)
Module 11: Transactions & Concurrency Basics
- ACID fundamentals
- BEGIN / COMMIT / ROLLBACK
- Isolation levels overview (simple explanation)
- Locking behavior and common issues
Module 12: Basic PostgreSQL Administration
- Users, roles, privileges (GRANT/REVOKE)
- Database security basics (least privilege mindset)
- Backup and restore basics
- pg_dump / pg_restore overview
- Monitoring overview (what to check when DB is slow)
Module 13: PostgreSQL for the Future (Skills Transferability)
- How PostgreSQL skills translate to other DBs
- When to use PostgreSQL vs NoSQL options
- Trends: cloud-managed databases, performance scaling, AI workloads
Final Lab: End-to-end exercise (design + query + optimize + backup simulation)
