Complete PostgreSQL course! Updated v2026!

Install Rocky Linux 9 in VirtualBox, choose the ISO image, allocate RAM, CPU, and disk space, enable bridge networking, and complete the setup with an optional GUI and SSH access.

Install postgres on Windows from the official download page, choose your platform (64-bit or 32-bit), install pgadmin if desired, and set the default 5432 port with a strong password.

Learn how to use the psql command to connect to Postgres databases, run SQL and metadata commands, view help, perform backups, and securely connect to local or remote databases.

Learn how SQL, the structured query language for relational databases, uses select, insert, update, delete, and other commands to manage data in Postgres.

Explore populating a Northwind database in Postgres by downloading a SQL file, creating the database, and running inserts to practice essential SQL commands and table operations.

Use the select command to query data from tables, and fetch all columns with the asterisk. Filter results with where clauses and and/or operators, and combine conditions as shown.

Learn to use Postgres select to alias columns, concatenate first and last names, and filter results, then apply case when logic to classify orders by shipping urgency and employee seniority.

Explore the select command in PostgreSQL, using limit and order by to fetch random five rows, and apply is null, is not null, coalesce, cast, and case for data shaping.

Use join operators and the union clause to combine rows from multiple tables - customers, suppliers, and products - and apply distinct, filtering, and order by name.

Explore querying multiple tables in Postgres with subqueries to compute average, max, and min values, using aliases and distinct to filter products by average prices.

Explore numerical operations in Postgres, including multiplying unit prices, applying discounts, and summing shipping with avg, max, and min; apply rounding and casts to numeric.

Use the group by clause with count to group products by category and count them, then order results; identify top destinations and best selling products by mode.

Explore the insert into command for Postgres, populating mandatory columns and respecting constraints like primary keys across customers and products, with examples and visible insertion results.

Use the update command to modify existing records with set and where clauses in PostgreSQL, with practical examples of price changes, status updates, and null handling.

Master the delete command in PostgreSQL by filtering targets with a where clause, deleting by id or name, and optionally using return to show removed rows.

Explore how foreign keys and primary keys create referential integrity between tables, define constraints, and cascade deletions, with practical examples in PostgreSQL.

Explore how PostgreSQL uses roles, not just users, to define groups, privileges, and access to databases and objects. Learn to create roles with create role, assign privileges, and manage security.

Learn how to create databases in Postgres, using template models to pre-configure settings and easily clone new databases, then modify templates and apply updates.

Explore how PostgreSQL schemas organize data within a single database, creating named schemas (like personal data and flight plans) to isolate users and protect ownership and security.

Explore how the alter system command lets a superuser modify Postgres configuration without editing files, understand parameter categories (noneditable, restart required, editable), and use shared buffers as an example.

learn how to create and use postgres sequences to generate integer values, set start and increment, define min/max, caching, and cycles, with practical examples for serial primary keys and queues.

Learn how to create a recursive view in PostgreSQL, using a view of employees and subordinates to illustrate hierarchical levels and inner joins.

Apply limit and offset in PostgreSQL to limit rows and skip records. Practice with the categories table, ordering by id, retrieving the first or second record, and copying structure.

Explore the join commands in PostgreSQL, combining posts and categories with cross and inner joins, and filter results using where, in, and exists.

Explore left and right joins in PostgreSQL, returning all records from the left (or right) table with matching right (or left) records, using not exists and not in as alternatives.

Learn how the COALESCE function handles null values in PostgreSQL by returning the first non-null argument, with practical examples on posts and items that illustrate zero substitution and case equivalents.

Explore how PostgreSQL uses between and not between to filter data in a where clause across numbers, dates, and text, including inclusive ranges and practical Northwind examples.

Explore how check constraints enforce boolean expressions in PostgreSQL, with examples for positive price, discount logic, age must be at least 18, and multi-column checks.

Explore unique constraints in PostgreSQL, learning how a unique constraint guarantees a column's distinct values, how it compares to a primary key, and how to name constraints with practical examples.

Explore how primary keys uniquely identify table rows, enforce not null and unique constraints, and speed lookups with a built-in index. Learn to define composite primary keys across multiple columns.

Explain how foreign keys link to a parent table's primary key to ensure related records exist. Build examples with orders and products, and customers and contacts to illustrate referential integrity.

Explore generated as identity in PostgreSQL, including generated always and by default options, the role of identity columns, sequences, and practical table alterations.

Explore the default constraint in SQL, which assigns a value to a column on insert, with examples using create table, alter table, and dropping defaults.

Postgres 18 introduces not valid with not null, allowing new records to validate immediately without validating existing data and avoiding downtime during alter table schema changes on large tables.

Learn to list and drop indexes in Postgres using index or psql commands, filter by table or index name, and apply if exists, cascade, or strict options for safe removal.

Explore partial indexes in PostgreSQL and learn how using a where clause filters inactive customers to reduce index size and speed up queries.

Explore the explain command to visualize a query plan in Postgres. Examine execution nodes, startup and final costs, rows, and width to understand how queries are evaluated.

Discover HypoPG, a hypothetical index extension for PostgreSQL, and learn to install and enable it, then test whether an index would help queries using explain before creating a real index.

Explore Postgres table partitioning, including interval, list, and hash methods, to split large tables and boost query performance, with easier archiving, backups, and cost-effective storage.

Create and manage interval partitioning in PostgreSQL, using a main table with range-based time partitions and check constraints to route data to monthly partitions and boost query performance.

Learn how to partition by lists and by hashes in PostgreSQL, creating a products main table and partitions for electronics, clothing, and furniture, with hash partitioning for load balancing.