Learn PostgreSQL Tutorial - Full Course for Beginners

Main ideas, concepts, and lessons (by topic)

1) Why Postgres / what the course will cover

• Introduces PostgreSQL (Postgres) as a widely used, open-source, robust, high-performance database engine.
• Emphasizes that Postgres is commonly used by startups for backend systems, so software engineers should learn it for both projects and career growth.
• Course learning approach:
  • No GUI-based learning for core concepts.
  • Uses the interactive terminal shell (psql) and the command line to understand the “raw logic” behind database operations.
  • Notes that with remote servers (e.g., SSH), GUIs are often unavailable or impractical.

2) What is a database? Core SQL + relational modeling

• Defines a database as a place to store, manipulate, and retrieve data (often on a server).
• Uses examples (e.g., Facebook, eBay) to illustrate that app-visible data is backed by databases.
• Defines Postgres vs SQL:
  • Postgres = database engine
  • SQL = Structured Query Language used to query/manipulate data
• Core relational concepts:
  • Data stored in tables
  • Tables composed of:
    • Columns (attributes)
    • Rows (records)
  • Relational databases split data into multiple related tables rather than one “everything table.”

3) Setup: install Postgres and connect

• Installation guidance:
  • Mac: download the Postgres.app, choose additional releases so multiple versions can run; start the server using the app’s elephant icon.
  • Windows: download the official installer, select components:
    • PostgreSQL server
    • PGAdmin (GUI)
    • Command line tools
  • Configure a superuser password, keep default port 5432.
• Connection methods (3 options):
  1. GUI client (easy viewing/inserting, etc.)
  2. psql / terminal (preferred for learning raw commands)
  3. Application-based connection (server-side app talks to DB)
• Practical connection notes:
  • In psql on Mac, psql may require PATH changes (editing .zshrc).
  • Connection defaults:
    • default DB: Postgres
    • default port: 5432
    • username: Postgres (superuser)
  • GUI connection via PGAdmin is presented as an alternative.

4) Fundamental psql commands and workflow

• psql meta-commands:
  • \l = list databases
  • \c <db> = connect/switch to a database
  • \d = list relations (tables/sequences)
  • \d <table> = describe a table
  • \i <file.sql> = execute SQL commands from a file
  • \dx / \x = toggle expanded display (used for readability)
• Connection via command options:
  • -h host
  • -p port
  • -U user
  • -d dbname
• Creating and listing a database:
  • CREATE DATABASE test;

5) Dangerous command warning: DROP DATABASE / DROP TABLE

• Deletion is immediate and catastrophic:
  • DROP DATABASE test; removes all content and the database itself.
  • Similarly warns against careless DROP TABLE usage.
• Recreates databases/tables after experiments to continue learning.

6) Table creation + data types

• Table creation pattern:
  • CREATE TABLE <table_name> ( <column_name> <data_type> [constraints...], ... );
• Example domain model:
  • A person table with columns like:
    • id (integer types)
    • first name, last name, gender
    • date of birth (uses date type rather than timestamp)
    • email (nullable)
• Introduces common Postgres data types:
  • bigint, serial-style auto increment concepts, boolean
  • varchar(n) / text
  • date, timestamp
  • numeric, money
  • json, uuid (later via extensions)
• Notes default description behavior and constraints like:
  • nullability (NOT NULL)
  • primary keys

7) Constraints, primary keys, sequences

• Improves the person table using constraints:
  • primary key on id
  • NOT NULL on key fields
  • nullable fields for optional data (like email)
• Uses bigserial (auto-incrementing 8-byte integer):
  • explains that bigserial is tied to a sequence
  • sequence generates new IDs automatically
• Dropping/recreating tables changes ID behavior because sequences are affected.

8) Inserting data

• Insert syntax:
  • INSERT INTO <table>(<col1>, <col2>, ...) VALUES (<v1>, <v2>, ...);
• Demonstrates omitting auto-managed id because bigserial/sequence generates it.
• Shows inserting into nullable columns (e.g., person without email).
• Bulk data generation:
  • uses mockaroo to generate 1000 rows
  • generates SQL file with CREATE TABLE + INSERT statements
  • imports into psql using \i <file.sql>

9) Reading data (SELECT), projection, and NULL behavior

• Basic read:
  • SELECT * FROM person;
• Projection (select specific columns):
  • SELECT first_name, last_name FROM person;
• Notes:
  • * means all columns
  • selecting columns containing NULL values returns NULLs for those rows (and may affect filtering/appearance)

10) Sorting (ORDER BY) and removing duplicates (DISTINCT)

• Sorting:
  • ORDER BY <column> ASC|DESC (default is ASC)
  • sorting multiple columns: ORDER BY id, email
• Removing duplicates:
  • SELECT DISTINCT country_of_birth FROM person;
  • shows count of unique countries

11) Filtering (WHERE) + logical operators + comparisons

• WHERE clause:
  • WHERE <condition>
• Logical operators:
  • AND
  • OR
• Comparison operators covered:
  • =, != / <> (not equal), <, <=, >, >=
• Comparisons work across strings, dates, and numbers.

12) Limiting result sets (LIMIT / OFFSET / FETCH)

• Limit:
  • SELECT * FROM person LIMIT 10;
• Offset + limit:
  • SELECT * FROM person OFFSET 5 LIMIT 5;
• Alternative:
  • FETCH FIRST <n> ROWS ONLY

13) IN, BETWEEN, LIKE (pattern matching) and case-insensitivity

• IN for multiple values:
  • WHERE country_of_birth IN ('China','Brazil','France');
• BETWEEN for date ranges:
  • WHERE date_of_birth BETWEEN '2000-01-01' AND '2015-01-01'
• LIKE pattern matching:
  • % = any sequence of characters
  • _ = single character
  • examples:
    • emails ending in .com
    • emails containing @bloomberg.com
    • matching by prefix
• ILIKE supports case-insensitive matching.

14) Aggregation with GROUP BY + COUNT + HAVING

• GROUP BY groups rows to compute aggregates.
• Example count per country:
  • SELECT country_of_birth, COUNT(*) FROM person GROUP BY country_of_birth;
• HAVING filters groups after aggregation:
  • HAVING COUNT(*) > 5
• Placement: GROUP BY → HAVING → ORDER BY.

15) Aggregate functions (MAX, MIN, AVG, SUM) and grouped aggregates

• Demonstrates:
  • MAX(price), MIN(price), AVG(price), SUM(price)
• Uses ROUND(...) to round aggregate outputs.
• Aggregates per group (example):
  • min/max/avg/sum per make using GROUP BY make.

16) Arithmetic operators + expressions in SELECT

• Arithmetic:
  • +, -, *, /
  • power: ^ (“hat”)
  • factorial: !
  • modulus: mod or %
• Discounted price example uses expressions and ROUND.
• Uses column aliases:
  • SELECT price * 0.1 AS original_price, ...

17) NULL handling: COALESCE and avoiding division-by-zero

• COALESCE(a, b, c...):
  • returns the first non-NULL value
• Applies COALESCE for nullable fields (e.g., default “email not provided”).
• Division-by-zero:
  • Postgres throws division by zero
  • uses the “null if” pattern conceptually:
    • x / NULLIF(denominator, 0) (so results become NULL, then can be defaulted via COALESCE)

18) Date/time usage: NOW(), casting, INTERVAL, EXTRACT, AGE()

• NOW() returns timestamp including time zone context.
• Casting timestamps to:
  • date or time
• Date arithmetic:
  • NOW() - INTERVAL '1 year' (and months/days)
  • NOW() + INTERVAL '10 days'
• Extracting parts:
  • EXTRACT(YEAR FROM now) (month/day/week/century concepts included)
• age(start, birth_date):
  • computes age and can break down month/day components.

19) Primary keys and uniqueness rules

• Primary key uniquely identifies records.
• Demonstrates failure case:
  • inserting a duplicate id violates primary key uniqueness
• Shows altering constraints:
  • dropping primary key constraint allows duplicates
  • re-adding primary key requires uniqueness again
• Conclusion:
  • adding a primary key requires uniqueness across rows.

20) UNIQUE constraint (distinct values per column)

• Explains why UNIQUE matters (e.g., duplicate emails break identity mapping/logic).
• Demonstrates:
  • adding UNIQUE(email)
  • insertion fails if duplicates exist
• Resolving duplicates:
  • delete conflicting rows or update values
• Demonstrates dropping the constraint afterward.

21) CHECK constraint

• Enforces a row-validity condition.
• Example:
  • gender must be only 'female' or 'male'
• Adds check constraint and shows inserts fail when invalid.
• Demonstrates deleting invalid rows, then adding succeeds.

22) CRUD operations: DELETE and UPDATE

• DELETE:
  • DELETE FROM person WHERE id = <value>;
  • warns: omitting WHERE wipes the entire table
  • sequences may not reset IDs automatically
• UPDATE:
  • UPDATE person SET email = 'new' WHERE id = <value>;
  • warns: omitting WHERE updates all rows
  • multiple columns updated via comma-separated assignments.

23) Handling duplicate key errors: ON CONFLICT

• Do nothing on conflict:
  • INSERT ... ON CONFLICT (<unique_column>) DO NOTHING;
• Upsert style:
  • ON CONFLICT ... DO UPDATE SET ...
  • uses excluded.<col> to reference the incoming row values
  • demonstrates overwriting on conflict (useful for distributed systems).

24) Foreign keys, relationships, and JOINs

• Foreign key concept:
  • a column referencing another table’s primary key
  • types must match
• Relationship example:
  • person.car_id references car.id
  • models one person ↔ at most one car
  • nullable foreign key means “may or may not have a car”
• Demonstrates updating relationships via UPDATE.
• Foreign key prevents assigning a non-existent car.
• JOIN types:
  • INNER JOIN: only matching rows appear
  • LEFT JOIN: includes all left rows; non-matching right columns become NULL
  • shows filtering for “no car” using LEFT JOIN and checking NULLs.

25) Deleting with foreign key constraints (+ cascade mention)

• Deleting a referenced parent row (car) fails while child rows (person) still reference it.
• Safe approaches:
  • delete child rows first, or
  • set foreign key to NULL/update it first
• Mentions ON DELETE CASCADE conceptually (not taught) and warns against careless cascading behavior.

26) Exporting query results to CSV

• Uses psql backslash copy:
  • \copy (<SELECT ...>) TO '<path>/results.csv' WITH (FORMAT csv, HEADER true, DELIMITER ',');
• Example includes rows with and without relationships using LEFT JOIN.

27) Sequences in detail

• Sequence stores the “next value.”
• Insertion uses nextval.
• Demonstrates restarting a sequence:
  • ALTER SEQUENCE <name> RESTART WITH <n>;

28) Extensions and UUIDs (universally unique identifiers)

• Postgres supports extensions.
• Lists extensions from pg_available_extensions.
• Installs uuid-ossp.
• Uses UUID generation:
  • uuid_generate_v4()
• Benefits:
  • extremely low collision risk (globally unique)
  • improves security (harder to guess numeric IDs)
  • simplifies merging/migrating datasets across systems.

29) Migrating schema from serial IDs to UUID keys

• Transformation approach in SQL exercises:
  • change id from serial/bigserial to uuid
  • rename PK columns to <table>_uuid (e.g., person_uuid, car_uuid)
  • update foreign key column types to UUID
  • update INSERT statements to insert UUID values via uuid_generate_v4()
  • recreate tables in correct FK order (car first, then person)
• Joins remain structurally similar; uses USING when key names match.

30) Course wrap-up

• Learner can now use psql, write core SQL queries, model relational schemas, and use constraints and joins.
• Encourages next steps:
  • backend development courses (Spring Boot / Node.js & Express)
  • advanced Postgres course for topics like indexes, functions, CTEs, triggers, views, etc.

---

Methodologies / instruction-style steps (detailed)

A) Learning/using Postgres in this course (method)

• Prefer terminal + psql over GUI:
  • learn raw SQL commands and psql meta-commands
• Follow sequence:
  1. install Postgres
  2. start DB server
  3. connect with psql
  4. create DB and tables
  5. use INSERT/SELECT/UPDATE/DELETE
  6. enforce correctness with constraints (NOT NULL, PK, UNIQUE, CHECK)
  7. relate tables with foreign keys
  8. query relationships with INNER JOIN / LEFT JOIN
  9. export results to CSV when needed

B) Key workflow for a new database + table

• Create database:
  • CREATE DATABASE <db_name>;
• Connect:
  • \c <db_name> (or psql -d <db_name> ...)
• Create table:
  • CREATE TABLE <table_name> ( ...columns with data types and constraints... );
• Inspect:
  • \d <table_name>
• Insert records:
  • INSERT INTO <table_name>(...) VALUES (...);
• Read records:
  • SELECT * FROM <table_name>;

C) Bulk load via SQL file

• Generate SQL (e.g., via mockaroo) into person.sql
• Run in psql:
  • \i /path/to/person.sql
• Validate:
  • \d to ensure the table exists
  • SELECT * FROM person; to confirm inserts

D) Common SELECT query patterns taught

• Sorting:
  • SELECT ... FROM ... ORDER BY <col> ASC|DESC;
• Removing duplicates:
  • SELECT DISTINCT <col> FROM ...;
• Filtering:
  • SELECT ... FROM ... WHERE <condition> [AND/OR <condition>];
• Limiting/pagination:
  • SELECT ... FROM ... LIMIT n;
  • SELECT ... FROM ... OFFSET m LIMIT n;
• Pattern matching:
  • WHERE <text_col> LIKE 'pattern%';
  • use ILIKE for case-insensitive
• Ranges:
  • WHERE <date_col> BETWEEN <start> AND <end>;
• Multi-value filter:
  • WHERE <col> IN (v1, v2, v3);

E) Aggregation patterns

• Group counts:
  • SELECT <group_col>, COUNT(*) FROM <table> GROUP BY <group_col>;
• Filter aggregated groups:
  • ... GROUP BY <group_col> HAVING COUNT(*) >= <n>;
• Aggregate functions:
  • MAX(<col>), MIN(<col>), AVG(<col>), SUM(<col>)
  • optionally ROUND(<expr>, <decimals>)

F) Constraint enforcement patterns

• Primary key:
  • add PRIMARY KEY to a column definition
• NOT NULL:
  • add NOT NULL so inserts must supply values
• UNIQUE:
  • add UNIQUE(<col>) or ALTER TABLE ... ADD CONSTRAINT ... UNIQUE(<col>);
• CHECK:
  • ALTER TABLE ... ADD CONSTRAINT <...