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.

Learn to use ssh from Windows with PowerShell 7, enabling open ssh by default and testing connections to devices via IPv4, IPv6, or hostname.

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.

Define and enforce primary keys in SQL to uniquely identify rows, prohibit nulls, and support composite keys, while learning table creation, alteration, and constraint naming.

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

Learn how to manage database connections and queries, monitor activity with pg_stat_activity, and safely terminate or cancel resources to prevent crashes and optimize performance across Windows and Linux.

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.

Discover how PostgreSQL tablespaces map data to physical disk locations, configure clusters, and manage storage using create tablespace, alter database, and alter table commands across Linux and Windows paths.

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.

Learn to run multiple postgres instances on a linux host using separate data directories and ports to avoid conflicts; create a second cluster and verify via psql on distinct ports.

Explore temporary tables and the exists logic in Postgres, including create or drop if exists, and how transactions control visibility within a session.

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.

Explain how null values work in PostgreSQL, distinguish null from empty strings, and demonstrate inserting, selecting, and using is null and is not null with ordering.

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 drop views in postgres using the drop view command, with if exists, cascade, and restrict options to manage dependencies and multiple views.

Master updatable views in PostgreSQL, learn the check option, cascade and local options, and perform inserts, updates, and deletes through a city-based view.

Learn how materialized views in PostgreSQL store the result of complex queries. Create, refresh, and index these views for fast access and controlled updates.

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

learn how to use distinct to return only unique values and how to apply coalesce to replace nulls with the first non-null value, with examples in text fields and descriptions.

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 in, not in, exists, and not exists subqueries in PostgreSQL with practical examples on categories and posts. Learn how to filter data using subqueries in where clauses.

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.

Learn to concatenate strings in PostgreSQL using concat and concat_ws, joining fields like first name and last name, and building rich strings with separators while handling nulls.

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.

Learn how to implement fuzzy search in PostgreSQL with the pg_trgm extension, trigram indexing via a gist index, and similarity metrics to find near matches.

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.

Explore explain analyze in PostgreSQL to inspect query plans, execution time, timings, memory, costs, buffers, and json output, and understand parallel versus non-parallel execution.

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.