Complete PostgreSQL course! Updated v2026!

Install a Linux VM in VirtualBox. Learn to select an ISO, allocate RAM and CPUs, set up a 60 GB disk, bridge networking, and create root and user accounts.

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 and configure PostgreSQL on Rocky Linux, set up data directory, initialize the database, secure access, and enable remote connections with proper authentication for server deployments.

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.

Install and use pgAdmin, a free cross-platform graphical interface for Postgres, to connect remotely, manage databases and schemas, and practice SQL alongside the terminal-focused course.

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.

master core psql commands for local and remote postgres connections, including encrypted sessions. list and switch databases, view tables, schemas, functions, views, and users; manage history and sql scripts.

Discover how the Postgres postmaster forks backend processes per connection, uses memory buffers and work memory, and orchestrates parallel queries, background workers, checkpoint, and WAL files in a single instance.

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.

Learn how to use the order by clause to sort results in Postgres and SQL, by price and ship date, with ascending, descending, null handling, and multi-column sorts.

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.

Learn PostgreSQL string manipulation with core functions like like and ilike, upper and lower, length, substring, replace, and position to query and format text, with products and customers as examples.

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 to manage privileges and permissions in PostgreSQL using grant and revoke to control who can select, insert, update, or delete data across databases and schemas.

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.

Master safe PostgreSQL upgrades from minor to major versions. Learn to locate data and binary directories, back up, and run pg_upgrade with verification.

Explore unlogged tables in Postgres to speed handling of temporary, non-logged data, with security caveats and potential restart data loss; learn table creation, type inheritance, and cascade changes.

Learn to insert data into PostgreSQL tables using insert into, handling single or multiple rows and auto-generated IDs, and use where and order by in selects.

learn to update and remove data in PostgreSQL by using update and delete statements, create and use temporary tables for safe testing, and understand truncate for fast data removal.

Explore auto incrementing IDs in PostgreSQL with serial and sequences, set default values and primary keys, fetch generated IDs with the returning clause, and adjust start and increment values.

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.

Demonstrates how a full outer join combines left and right joins, and contrasts it with inner and cross joins. Showcases using aliases to cross-reference posts and tags across related tables.

Explore using case to implement conditional logic in PostgreSQL queries, categorize film duration and price, and apply case in selects, where clauses, and aggregates.

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 the NULLIF function in PostgreSQL to gracefully handle nulls and prevent division by zero, using examples with posts and members tables to return excerpts or nulls.

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 how to create indexes in PostgreSQL, focusing on the btree standard and other types like hash, gist, gin, spgist, brin, and when to use them.

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 how unique indexes enforce cross-row uniqueness for one or more columns, handle nulls as non-distinct, and support multi-column constraints with examples on emails and work phone extensions.

Create expression-based indexes in PostgreSQL to speed up queries that involve expressions, such as lower(name). Understand the syntax, practical usage, and the trade-off: faster reads but heavier maintenance.

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

Explore multicolumn indexes in PostgreSQL, including composite and concatenated indexes on multiple columns. Build and verify index usage with create index, where clauses, and explain to optimize searches by surname.

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.

Explore how the BRIN index, a block-range index, offers a compact, low-maintenance solution for large time-series data and range queries, with a sensor logs example and performance comparison to B-tree.

Explore how PostgreSQL index fill factor, default 90, affects page occupancy, update costs, and fragmentation; compare 90 vs 70 in practical experiments to balance space and performance.