PostgreSQL Fundamentals

Why PostgreSQL?

What PostgreSQL is, why companies choose it, and how it compares with MySQL and NoSQL databases.

Installing PostgreSQL, psql & pgAdmin

Install PostgreSQL on your computer (or use a free cloud database), connect with psql and pgAdmin, and create your first database.

Creating Tables & Choosing Data Types

Pick the right PostgreSQL types (identity, text, numeric, timestamptz, boolean, uuid) so your data stays accurate.

Insert, Select, Update, Delete & RETURNING

The four everyday commands, plus PostgreSQL's RETURNING clause and upserts with ON CONFLICT.

Filtering, Sorting & Pagination

WHERE with AND/OR, IN, BETWEEN, ILIKE and NULL handling, ORDER BY and LIMIT/OFFSET pagination.

Keys & Constraints: Let the Database Enforce the Rules

Primary and foreign keys, UNIQUE, CHECK and NOT NULL, plus ON DELETE behaviour.

Joining Tables

INNER, LEFT and multi-table joins to answer questions that span customers, orders, items and products.

Aggregates, GROUP BY & HAVING

Totals, counts and averages per group, filtered with HAVING, plus PostgreSQL extras like FILTER and string_agg.

Subqueries & CTEs (WITH)

Break complex questions into readable steps with subqueries and common table expressions, including recursive CTEs.

Window Functions

Rankings, running totals and comparisons with previous rows, without collapsing rows.

JSON in PostgreSQL with jsonb

Store flexible attributes in jsonb columns, query them with -> and ->> and @>, and index them with GIN.

Arrays, Enums & Generated Columns

Use PostgreSQL array columns, enum types and generated columns for cleaner schemas, and know when not to.

Full-Text Search

Build fast, relevance-ranked search with tsvector, tsquery and GIN indexes, no extra search server needed.

Indexes & EXPLAIN ANALYZE

Find out why a query is slow and fix it with the right index: B-tree, composite, partial and expression indexes.

Transactions, Isolation & Locking

Group changes so they all succeed or all fail, understand MVCC, and avoid double-spending with row locks.

Views, Functions & Triggers

Save queries as views, write SQL and PL/pgSQL functions, and keep data consistent automatically with triggers.

Roles, Permissions & Row-Level Security

Give each app and person only the access they need, and enforce per-user data access inside the database with RLS.

Backups, Restores & Maintenance

Back up with pg_dump, restore with pg_restore, automate it, and keep the database healthy with VACUUM and monitoring.

Connecting PostgreSQL to Your Apps

Use PostgreSQL from Python (psycopg), PHP/Laravel and Django safely, with parameterised queries and connection pooling.

Project: A Mobile-Money Wallet Ledger

Design and build a double-entry wallet ledger in PostgreSQL: accounts, transfers, balances and audit, the core of every fintech.