PostgreSQL Full course 2026 | Learn PostgreSQL in One Video

Main ideas / lessons (what the video teaches)

• Purpose of the course

  • A practical PostgreSQL foundation intended to help beginners build real database skills via SQL files and running them in the terminal/psql.
  • It’s a prerequisite for an upcoming Node.js Masterclass; Postgres concepts will also be covered later there.

• Curriculum structure (3 sections)

  • Part 1: Foundations
    • Create databases, schemas, tables, and define data types
    • Concepts like constants/constraints and primary keys
  • Part 2: Queries & data operations
    • Insert, select, filtering, pattern matching, handling NULLs
    • Ordering, limiting, offset/pagination
    • Updating single/multiple rows, and RETURNING after INSERT
  • Part 3: Relationships & advanced SQL
    • Foreign keys, one-to-many, many-to-many (junction table)
    • INNER JOIN and LEFT JOIN
    • Aggregations: aggregate functions, GROUP BY, COUNT DISTINCT
    • Subqueries, indexes, and transactions

• Learning approach

  • The instructor emphasizes this is not purely conceptual:
    • Each concept is written into its own .sql file
    • The learner runs the file (and sometimes uses interactive commands in psql) to verify results.

---

Methodology / workflow presented in the video

1) Setup & tools

• Install PostgreSQL and pgAdmin 4 (and related components).
• Use pgAdmin visually for exploration (tables/schemas), but the course emphasizes mastering command line.
• Use psql for terminal-based interaction with PostgreSQL:
  • Run SQL files with:
    • psql -U <user> -d <database> -f <file.sql>
  • Connect interactively to execute:
    • queries
    • meta-commands (e.g., list databases/tables)
    • SQL directly

2) Recommended folder/file structure

• Create folders for sequential learning:
  • part 1 (basic DB objects + constraints + primary keys)
  • part 2 (SQL queries and operations)
  • (Part 3 comes later in the course)
• Inside each part, create numbered SQL files like:
  • 01_first_database.sql
  • 02_first_schema.sql
  • 03_first_table.sql
  • etc.

3) Part 1: SQL foundations (core steps)

Create database

• Learn:
  • CREATE DATABASE ...
  • (For learning only) DROP DATABASE IF EXISTS ...
• Connect to the new DB using psql.

Create schema

• Learn:
  • CREATE SCHEMA IF NOT EXISTS <schema_name>
  • Install extensions when needed, e.g.:
    • CREATE EXTENSION IF NOT EXISTS pgcrypto (for UUID generation)
• Use system catalogs to inspect schemas:
  • Query information_schema.schemata to list available schemas.

Create tables

• Learn:
  • CREATE TABLE <schema>.<table> ( ...column definitions... )
  • Dropping tables (learning):
    • DROP TABLE IF EXISTS ...
• Column-level concepts emphasized:
  • serial primary key (auto-increment integer)
  • NOT NULL
  • UNIQUE
  • CHECK constraints
  • DEFAULT values
  • timestamp / now() defaults for created time

Insert and read data

• Insert:
  • INSERT INTO <table>(cols...) VALUES (...);
• Read:
  • SELECT * FROM <table>
  • SELECT cols... FROM <table>

Data types overview

• Integer types: integer, bigint
• Strings: text, varchar(n)
• Currency/precision: numeric(p,s)
• Booleans: boolean
• UUID & JSON:
  • uuid with default gen_random_uuid()
  • jsonb for structured metadata
• JSON field extraction:
  • Access JSONB value (example uses metadata->>'browser'-style logic).

NULL vs empty string vs zero

• Teach distinctions:
  • NULL = missing/unknown value
  • '' (empty string) = known value but empty text
  • 0 = actual numeric zero
• Filtering rules:
  • Use IS NULL and IS NOT NULL (not = NULL)

Constraints (“constants”) and why they matter

• Emphasis:
  • Database constraints are stronger than application-level validation
  • Constraints ensure invalid writes fail even if bypassing app logic.
• Demonstrated with:
  • NOT NULL violations
  • UNIQUE violations
  • CHECK constraints (e.g., age >= 18)
  • DEFAULT handling when a value is omitted

Primary key behavior

• Primary key uniquely identifies rows.
• Demonstrated:
  • Duplicate primary key insert fails with a uniqueness/primary key violation.

---

Part 2: SQL concepts & operations (detailed list of instructions)

Core setup used for query practice

• A “base” setup file creates a products table used across query examples.
• Uses constraints like:
  • UUID PK
  • NOT NULL columns
  • CHECK constraints for price/stock ranges
  • UNIQUE SKU
  • defaults (e.g., is_active = true)

A) Insert

• Insert a single row
  • INSERT INTO products(col1, col2, ...) VALUES(val1, val2, ...);
• Notes:
  • Some columns can use defaults (e.g., is_active).
  • Attempting to insert duplicate UNIQUE values (SKU) fails.
• Insert multiple rows
  • INSERT INTO products(col1, ...) VALUES (v1,...), (v2,...), ...;

B) Select

• Select star vs select specific columns
  • SELECT * FROM products;
  • SELECT name, price, ... FROM products;
• Column aliases
  • SELECT name AS product_name, price AS selling_price, stock AS available_quantity FROM products;

C) Filtering

• WHERE clause
  • WHERE category = 'electronics'
  • WHERE price > 1000
  • WHERE is_active = false
• AND / OR / NOT
  • AND: both conditions must be true
  • OR: at least one condition must be true
  • NOT: invert/exclude a condition
  • Parentheses used for complex logic.
• Pattern matching
  • LIKE: case-sensitive
  • ILIKE: case-insensitive
  • Wildcards:
    • % = any number of characters
    • _ = exactly one character
• IN / NOT IN / BETWEEN
  • WHERE category IN ('electronics','furniture')
  • WHERE category NOT IN (...)
  • WHERE price BETWEEN 100 AND 2000 (inclusive)
• NULL filtering
  • WHERE description IS NULL
  • WHERE description IS NOT NULL

D) Sorting: ORDER BY

• Ascending/descending:
  • ORDER BY price ASC
  • ORDER BY price DESC
• Multiple-column sorting:
  • ORDER BY category ASC, price DESC

E) Pagination with LIMIT and OFFSET

• Definitions:
  • LIMIT n = return at most n rows
  • OFFSET m = skip the first m rows
• Example pattern:
  • Page 1: ... ORDER BY ... LIMIT 5 OFFSET 0
  • Page 2: ... ORDER BY ... LIMIT 5 OFFSET 5
• Pagination formula:
  • offset = (page_number - 1) * limit

F) Updating rows

• Update a single row
  • UPDATE products SET price = ..., stock = ... WHERE sku = ...;
• Update multiple rows
  • UPDATE products SET price = ROUND(price * 1.10, 2) WHERE category = 'stationery';
  • Also demonstrated:
    • SET is_active = false WHERE stock = 0

G) Deleting rows

• Delete with a condition:
  • DELETE FROM products WHERE sku = ...;
• Demonstrated “check before/delete/verify after” pattern.

H) RETURNING (after INSERT/UPDATE/DELETE)

• RETURNING returns affected rows immediately after a write.
• Examples described:
  • After INSERT:
    • return id/name/category/price/stock/created_at
  • After UPDATE:
    • return updated fields
  • After DELETE:
    • return deleted row identifiers/fields

---

Part 3: Relationships & advanced SQL (core concepts covered)

1) Seed data and relational schema

• Creates and seeds multiple related tables:
  • users
  • posts (references users.id via user_id)
  • comments (references posts.id via post_id)
  • tags (unique tag names)
  • post_tags junction table for many-to-many:
    • composite primary key (post_id, tag_id)
    • references posts and tags

2) Foreign keys

• Definition:
  • A foreign key column points to a primary key in another table.
• Example taught:
  • posts.user_id references users.id
• Conceptual requirement:
  • Posts must belong to existing users.

3) One-to-many relationship

• Definition:
  • One parent row (user) can have many child rows (posts).
• Query concept shown:
  • Display post with author name by joining:
    • users.id = posts.user_id

4) INNER JOIN

• Meaning:
  • Returns only matching rows from both sides.
• Example:
  • Join posts to users to show only posts whose author matches
  • Apply filtering such as posts.status = 'published'

5) LEFT JOIN

• Meaning:
  • Keeps all rows from the left table
  • If no match exists in the right table, right-side columns become NULL
• Example:
  • Show all posts plus their comments if present; posts with no comments appear with NULL comment body.

6) Many-to-many relationship

• Definition:
  • Use a junction table (post_tags) to connect:
    • one post ↔ many tags
    • one tag ↔ many posts
• Example query concept:
  • Show each post with its tag name by joining:
    • posts → post_tags → tags

7) Table aliases

• Benefit:
  • Makes join queries shorter and easier to read.
• Concept:
  • Alias tables and reference columns using alias prefixes (e.g., p.title, u.name, c.body).

8) Aggregate functions

• Examples:
  • COUNT, SUM, AVG, MIN, MAX
• Used with grouping/filtering to produce analytics-style results.

9) GROUP BY and HAVING

• GROUP BY:
  • creates groups of rows
• HAVING:
  • filters groups after aggregation (unlike WHERE, which filters rows before grouping)
• Example:
  • Authors who wrote at least two posts:
    • join users/posts
    • GROUP BY user
    • HAVING COUNT(post.id) >= 2

10) COUNT DISTINCT

• Purpose:
  • Count unique values when joins create repeated rows.
• Example:
  • Count unique posts connected to each tag.

11) Subqueries

• Definition:
  • Nested query where the inner query runs first.
• Example concept:
  • “Posts with views greater than the average views.”

12) Indexes (conceptual coverage + examples)

• Definition:
  • Indexes help Postgres find rows faster.
• Taught use cases:
  • Index on filtering column (e.g., status)
  • Composite index for filter + sort (e.g., (status, views DESC))
  • Indexing foreign key columns (e.g., posts.user_id) to speed lookups by parent.

13) Transactions

• Definition:
  • Run multiple SQL statements as a single atomic unit.
  • Either all succeed and are saved, or failures can undo changes (rollback concept).
• Steps taught:
  • BEGIN
  • run multiple updates
  • COMMIT to permanently save changes
• Motivation example:
  • E-commerce order process: update stock, create order/payment records—must stay consistent.

---

Speakers / sources featured

• Speaker: Unspecified instructor (narrator/teacher who installs tools, explains SQL, and demonstrates commands).