PostgreSQL: Complete Course

How to install PostgreSQL on Windows, Mac, and Linux

PostgreSQL installation, psql CLI, pgAdmin setup, postgres superuser, initdb, pg_hba.conf basics

What is psql and how to use the psql command line tool

psql meta-commands, \l \c \dt \d, connecting to a database, quitting psql, SQL terminator

How to create a PostgreSQL database and connect to it

CREATE DATABASE, DROP DATABASE, OWNER clause, template databases, database encoding, connecting with \c

PostgreSQL data types explained with examples

integer types, text vs varchar, boolean, date and timestamp, numeric precision, serial vs identity, UUID

How to create your first table in PostgreSQL

CREATE TABLE syntax, column constraints, PRIMARY KEY, NOT NULL, DEFAULT, IF NOT EXISTS, DROP TABLE, TRUNCATE

How to SELECT data from a PostgreSQL table

SELECT syntax, WHERE clause, comparison operators, AND OR NOT, ORDER BY, LIMIT OFFSET, column aliases

INSERT UPDATE DELETE in PostgreSQL with examples

INSERT INTO VALUES, INSERT with SELECT, UPDATE SET WHERE, DELETE WHERE, RETURNING clause, upsert with ON CONFLICT

How SQL aggregate functions work in PostgreSQL

COUNT SUM AVG MIN MAX, GROUP BY, HAVING, COUNT DISTINCT, filter with FILTER clause, NULL in aggregates

How to JOIN tables in PostgreSQL: INNER, LEFT, RIGHT, FULL

INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, join condition ON, USING clause, self-join, cross join

What are subqueries and CTEs in PostgreSQL and when to use them

scalar subquery, correlated subquery, IN EXISTS ANY ALL, WITH clause CTE, recursive CTE basics, CTE vs subquery readability

Database normalization explained: 1NF 2NF 3NF with examples

first normal form, second normal form, third normal form, functional dependency, partial dependency, transitive dependency, denormalization trade-offs

How to use foreign keys and referential integrity in PostgreSQL

FOREIGN KEY constraint, REFERENCES clause, ON DELETE CASCADE RESTRICT SET NULL, ON UPDATE, deferred constraints, foreign key indexes

PostgreSQL CHECK UNIQUE and NOT NULL constraints explained

CHECK constraint, UNIQUE constraint, NOT NULL, table-level vs column-level, adding constraints to existing tables, naming constraints, disabling constraints

How sequences and IDENTITY columns work in PostgreSQL

CREATE SEQUENCE, nextval currval setval, SERIAL vs IDENTITY, sequence options increment start minval maxval, gap behavior, resetting sequences

PostgreSQL schemas explained: namespaces and search_path

CREATE SCHEMA, search_path, public schema, schema-qualified names, schema per tenant, moving tables between schemas, DROP SCHEMA

How PostgreSQL B-tree indexes work and when to use them

B-tree index structure, index scan vs sequential scan, index selectivity, composite indexes, column order in composite index, covering index, index bloat

How to read PostgreSQL EXPLAIN ANALYZE output

EXPLAIN vs EXPLAIN ANALYZE, cost estimation, actual time rows loops, Seq Scan vs Index Scan vs Bitmap Scan, node types, width, planning time vs execution time

PostgreSQL index types: Hash, GIN, GiST, BRIN — when to use each

Hash index equality, GIN for arrays JSONB full-text, GiST for geometric and range types, BRIN for sequential data, partial indexes, expression indexes

How to optimize slow PostgreSQL queries in production

pg_stat_statements, slow query log, auto_explain, vacuum and analyze, table statistics, parallel query, enable_seqscan, rewriting inefficient queries

What are window functions in PostgreSQL and how do they work

OVER clause, PARTITION BY, ORDER BY in window, ROW_NUMBER RANK DENSE_RANK, LAG LEAD, running totals with SUM OVER, ROWS vs RANGE frame

How database transactions work in PostgreSQL: ACID explained

BEGIN COMMIT ROLLBACK, ACID properties, atomicity isolation consistency durability, transaction blocks, SAVEPOINT, autocommit

PostgreSQL transaction isolation levels explained with examples

READ COMMITTED, REPEATABLE READ, SERIALIZABLE, dirty read, non-repeatable read, phantom read, serialization failure, SET TRANSACTION ISOLATION LEVEL

How PostgreSQL row locking works: SELECT FOR UPDATE and advisory locks

SELECT FOR UPDATE, SELECT FOR SHARE, NOWAIT SKIP LOCKED, row-level vs table-level locks, advisory locks, deadlock detection, lock_timeout

What is MVCC in PostgreSQL and how does it affect performance

multi-version concurrency control, xmin xmax system columns, dead tuples, VACUUM, autovacuum, table bloat, visibility rules, HOT updates

How to detect and fix deadlocks in PostgreSQL

deadlock definition, deadlock detection in PostgreSQL, deadlock error message, consistent lock ordering, deadlock_timeout, pg_locks, canceling blocking queries

How to write PostgreSQL functions and stored procedures

CREATE FUNCTION, PL/pgSQL, RETURNS, DECLARE variables, IF ELSE, FOR loop, EXCEPTION block, CREATE PROCEDURE, CALL, function vs procedure difference

How triggers work in PostgreSQL with real examples

CREATE TRIGGER, trigger timing BEFORE AFTER INSTEAD OF, ROW vs STATEMENT trigger, trigger function NEW OLD, audit log pattern, WHEN condition

Working with JSONB in PostgreSQL: operators and indexing

JSON vs JSONB, -> ->> #> operators, @> containment, ? key exists, jsonb_set, jsonb_agg, GIN index on JSONB, when to use JSONB vs relational columns

PostgreSQL roles and permissions explained: GRANT REVOKE CREATE ROLE

CREATE ROLE CREATE USER, GRANT privileges, REVOKE, role inheritance, superuser, pg_hba.conf auth, DEFAULT PRIVILEGES, schema permissions, principle of least privilege

How Row Level Security (RLS) works in PostgreSQL

ALTER TABLE ENABLE ROW LEVEL SECURITY, CREATE POLICY, USING clause, WITH CHECK, current_user, current_setting, policy for SELECT INSERT UPDATE DELETE, BYPASSRLS