PostgreSQL Fundamentals · no prior SQL knowledge required
SQL and PostgreSQL from the ground up. Learned to stick.
You look after a PostgreSQL database without really knowing why it works? Your ORM writes SQL you can't evaluate? In 16 units on a real database, you learn to write queries that are correct, change data safely and make slow queries measurably faster.
- 16 units in 5 modules
- About 27 hours core, 35 with an elective track
- Verified on PostgreSQL 18
- Runs locally with Docker
Who this course is for
Administration
You have taken over a PostgreSQL instance and want to understand who accesses what, how data is backed up and why a query is slow. Elective track A introduces you to roles, pg_hba.conf, backup and restore.
Development
So far you have used SQL through frameworks or copy and paste. Here you learn to read, write and evaluate it. Elective track B shows you the N+1 problem and SQL injection in real Python code.
On the way to the Engineer Training
You want to build a solid foundation for the Engineer Training. Every unit ends by telling you where its topic is explored further there.
What you can do after the course
- Read and design data models, and justify tables, keys and relationships.
- Write queries that return what you mean, even when NULL is involved.
- Join tables correctly and build reports directly in the database, without a detour through a spreadsheet.
- Choose data types and constraints so that bad data never gets in.
- Change data safely, protect changes with transactions and import external data cleanly.
- Read an execution plan and make a query measurably faster with an index.
From 36 to 0.3 milliseconds.
In the final assignment you write a complete customer report, read its execution plan and make it more than a hundred times faster with two indexes. If you can solve it, you bring the prerequisites for the Engineer Training not just on paper.
How you learn
A database with real pitfalls
You work with an ordering system of customers, products and orders, later with 100,000 orders. The data deliberately contains duplicates, missing values and edge cases, just like in real life.
Predict first, then check
You write down the result you expect before running the query. The comparison shows you exactly where your picture of SQL is still off.
Everything is in the text
Every script and every exercise program is in the unit in full. Nothing to download, nothing to hunt for.
Solutions on demand
Every unit comes with solutions, real results and assessment guides. You reveal them when you are ready.
All 16 units at a glance
The foundation: the relational model, the tools, queries, types, joins and reports. By the end of module 1 you can answer questions about the course database directly in SQL.
- Understanding databases and the relational model Name tables, keys and relationships (one-to-many, many-to-many); justify integrity rules and the first three normal forms; understand what PostgreSQL is and what it isn't.
- First steps with psql and a client Tell instance, database and schema apart; use psql meta-commands and a graphical client; understand `search_path`; recognize connection errors by their message.
- SELECT, WHERE, ORDER BY, LIMIT Logical evaluation order, three-valued logic with NULL, the `NOT IN` trap, patterns with `LIKE`/`ILIKE`, deterministic sorting and limiting, readable formatting.
- Choosing the right data types in PostgreSQL `integer`/`bigint`/`numeric` versus floating point, `text` versus `varchar(n)`, `timestamptz` and daylight saving time, `boolean`, UUID with `uuidv7()` (new in PostgreSQL 18), identity instead of `serial`, an overview of arrays and JSONB.
- Mastering joins systematically INNER, LEFT and FULL OUTER JOIN, join chains, self-joins, subqueries with `IN`, `EXISTS` and `NOT EXISTS`; where a condition goes, `ON` or `WHERE`; cross products and the wrong join column as typical mistakes.
- Aggregation and grouping Aggregate functions and NULL, `GROUP BY`, `WHERE` versus `HAVING`, the counting traps `count(*)` and multiplication by joins, `FILTER`, intermediate results with `WITH`.
From reading to writing: changing data, designing rules, protecting changes and encapsulating queries.
- INSERT, UPDATE, DELETE and conflicts Statements that write, `RETURNING` with `old` and `new` (new in PostgreSQL 18), changes based on a second table, upserts with `ON CONFLICT`, an overview of `MERGE`, the habit of “SELECT first, then change”.
- DDL: designing tables and constraints The range of constraints with meaningful names, adding constraints to existing tables, justifying `ON DELETE` (`RESTRICT`, `CASCADE`, `SET NULL`) in plain language, drawing the line between integrity and process logic.
- Transactions for beginners, explained properly `BEGIN`, `COMMIT`, `ROLLBACK`, `SAVEPOINT`; the aborted transaction state; what transactions don't do; observing uncommitted changes, waiting writers and lost updates with two sessions.
- Views, materialized views and simple functions Views as stored queries, materialized views with `REFRESH` and the price of freshness, SQL functions with parameters, dependencies between objects.
Bringing data in from outside and taking the first deliberate look at the execution plan. Unit 12 is the handover to the Engineer Training.
- Importing and exporting data `COPY` and `\copy`, the four CSV mistakes (delimiter, quoting, encoding, number format), structural versus content errors, the import pattern with a staging table and a log, `ON_ERROR` and `REJECT_LIMIT`.
- Your first deliberate look at EXPLAIN Read plans, compare estimates with measurements, tell Seq Scan, Index Scan and Bitmap Heap Scan apart, prove the effect of an index, recognize missing indexes on foreign keys.
- Roles, privileges, reading pg_hba.conf Login and group roles, `GRANT`/`REVOKE` at every level, default privileges of `PUBLIC`, reading and changing `pg_hba.conf`, assigning every error message to its layer of control.
- Backup and restore basics `pg_dump` in three formats, `pg_restore` in full and in parts, what a dump doesn't contain (roles, configuration, and in PostgreSQL 18 no statistics by default), RPO and RTO, why `pg_dump` is not PITR.
- Understanding ORMs instead of fearing them Make an ORM's SQL visible, measure and fix the N+1 pattern, transaction boundaries in the framework, connection pools, a deliberate switch to SQL; translation to JPA, EF Core and Django.
- SQL injection and parameterized queries Understand how injection works, parameterized queries in psycopg, JDBC and ORMs, safe handling of column names, LIKE patterns and value lists, minimal privileges as a second layer.
Fundamentals online
All 16 units in the reading view, solutions to reveal, progress tracked per unit.
€249.00 incl. VAT
Businesses from other EU countries with a VAT ID pay no VAT.
Self-study course without individual support. You can report errors in the material to learn@bridge-layer.com at any time.
Frequently asked questions
Do I need any prior knowledge?
No prior SQL knowledge. You should be comfortable with a computer and willing to work in the terminal; the commands you need are in the text. For elective track B, basic knowledge of a programming language helps.
Do I have to do both elective tracks?
No. Modules 1 to 3 are the shared core. Track A is aimed at operations, track B at development; you choose one, both or neither.
Is my work laptop enough?
If you are allowed to install Docker and have 8 GB of RAM, yes. Alternatively, a local installation of PostgreSQL 18 works too; unit 2 shows both approaches.
What comes next?
The PostgreSQL Engineer Training picks up exactly where this course ends: how PostgreSQL plans queries, how transactions and VACUUM work, and how to run, monitor and secure databases professionally.
Do I get support or answers to my questions?
No. Fundamentals is a self-study course without individual support. Everything you need is in the text: every unit describes its exercise environment in full, and the solutions include measured values to compare against. You can report errors in the material to learn@bridge-layer.com at any time.
Where do I find my invoice?
After payment, your invoice is ready to download in your account.