MASTER YOUR
DATA

// The world's most advanced open source database.

POSTGRESQL IS THE GOLD STANDARD.

PostgreSQL is an enterprise-grade, ACID-compliant relational database. It handles terabytes of data, supports complex queries, and powers applications from startups to Fortune 500 companies.

WHY POSTGRESQL?

PostgreSQL offers more features than any other database—JSON support, full-text search, spatial data, pub/sub, partitioning, and more. It's rock-solid reliable and completely free.

BECOME A DATA ARCHITECT.

Master SQL from basics to advanced queries. Learn to design schemas, create indexes for performance, and optimize your database. Every application needs a solid data foundation.

BEGIN YOUR JOURNEY →

// The Path to Mastery

12 lessons. Complete PostgreSQL control.

LESSON 01

Introduction to PostgreSQL

Install PostgreSQL and understand database concepts.

Beginner
LESSON 02

Basic SQL

Learn SELECT, INSERT, UPDATE, and DELETE commands.

Beginner
LESSON 03

Data Types

Understand PostgreSQL's rich type system.

Beginner
LESSON 04

Tables & Constraints

Create tables with primary keys, foreign keys, and constraints.

Beginner
LESSON 05

Filtering & Sorting

Master WHERE, ORDER BY, LIMIT, and OFFSET.

Beginner
LESSON 06

Joins

Combine data from multiple tables with JOINs.

Intermediate
LESSON 07

Aggregations

Use GROUP BY, HAVING, COUNT, SUM, AVG, and more.

Intermediate
LESSON 08

Indexes

Speed up queries with B-tree, GIN, and other index types.

Intermediate
LESSON 09

Subqueries

Write nested queries and common table expressions.

Advanced
LESSON 10

Transactions

Ensure data integrity with ACID transactions.

Advanced
LESSON 11

Views & Functions

Create reusable logic with views and stored functions.

Advanced
LESSON 12

JSON & Advanced Types

Work with JSONB, arrays, and PostgreSQL's advanced features.

Advanced

// Why PostgreSQL

PostgreSQL began as Ingres, a project at UC Berkeley in the 1970s. It evolved into PostgreSQL in 1996 and has been actively developed ever since. It's the most feature-rich open source database available.

PostgreSQL supports SQL standards fully and extends them with powerful features. It handles complex queries, massive datasets, and high concurrency. Companies like Apple, Instagram, and Spotify trust PostgreSQL for their most critical data.

With JSON support, PostgreSQL can also serve as a document database. With PostGIS, it's a spatial database. With full-text search, it's a search engine. PostgreSQL is the ultimate all-in-one database.

Data is the foundation. Own it with PostgreSQL.

// Tools & References

📖 PostgreSQL Docs

Official Documentation

postgresql.org

📦 PG Admin

Database Management

pgadmin.org

🎓 PostgreSQL Tutorial

Interactive Learning

postgresqltutorial.com

🔧 PSQL Cheatsheet

Command Reference

psql commands

// Introduction to PostgreSQL

×

What is PostgreSQL?

PostgreSQL is the world's most advanced open source relational database. It supports SQL standards, ACID transactions, and offers many advanced features like JSON, arrays, and full-text search.

Why PostgreSQL?

  • ACID Compliant: Reliable transactions
  • Open Source: Free with great community
  • Extensible: Custom types, functions, indexes
  • JSON Support: Both JSON and JSONB
  • Performance: Handles terabytes of data
HISTORY: PostgreSQL began as Ingres project at UC Berkeley in 1977. It's been actively developed for over 40 years.

Installing PostgreSQL

$ sudo apt update && sudo apt install postgresql postgresql-contrib # Install PostgreSQL $ sudo systemctl start postgresql # Start the service $ sudo systemctl enable postgresql # Enable on boot

Connecting to PostgreSQL

$ sudo -u postgres psql # Connect as postgres user postgres=# \q # Exit psql

Create a Database

postgres=# CREATE DATABASE myapp; CREATE DATABASE postgres=# \c myapp You are now connected to database "myapp"

Quiz

1. What does ACID stand for?

2. What command connects to a database in psql?

// Basic SQL

×

SELECT - Query Data

-- Select all columns SELECT * FROM users; -- Select specific columns SELECT name, email FROM users; -- With calculations SELECT name, price * quantity AS total FROM orders;

INSERT - Add Data

-- Insert single row INSERT INTO users (name, email, age) VALUES ('Alice', 'alice@example.com', 30); -- Insert multiple rows INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com'), ('Charlie', 'charlie@example.com');

UPDATE - Modify Data

-- Update single row UPDATE users SET email = 'newemail@example.com' WHERE id = 1; -- Update multiple columns UPDATE users SET name = 'Alice Smith', age = 31 WHERE name = 'Alice';

DELETE - Remove Data

-- Delete specific row DELETE FROM users WHERE id = 5; -- Delete all rows (careful!) DELETE FROM users;

Quiz

1. What command adds new data?

2. What command removes data?

// Data Types

×

Numeric Types

-- Integers SMALLINT -- -32768 to 32767 INTEGER -- -2B to 2B (use this) BIGINT -- very large numbers -- Decimals DECIMAL(10,2) -- 10 digits, 2 decimal places NUMERIC(10,2) -- Same as DECIMAL REAL -- 4-byte floating point DOUBLE PRECISION -- 8-byte floating point

String Types

CHAR(10) -- Fixed length, padded VARCHAR(255) -- Variable length, up to limit TEXT -- Unlimited length (use this)

Date/Time Types

DATE -- Calendar date TIME -- Time of day TIMESTAMP -- Date and time TIMESTAMPTZ -- Date with timezone INTERVAL -- Duration

Boolean

-- Boolean values is_active BOOLEAN -- TRUE, FALSE, NULL

Quiz

1. What type for unlimited text?

// Tables & Constraints

×

Creating Tables

CREATE TABLE users ( id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL, email VARCHAR(255) UNIQUE NOT NULL, age INTEGER, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );

Primary Keys

-- Auto-increment primary key id SERIAL PRIMARY KEY -- Or with BIGINT id BIGSERIAL PRIMARY KEY

Foreign Keys

CREATE TABLE orders ( id SERIAL PRIMARY KEY, user_id INTEGER REFERENCES users(id), total DECIMAL(10,2), status VARCHAR(20) DEFAULT 'pending' );

Constraints

CREATE TABLE products ( id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL, price DECIMAL(10,2) CHECK (price > 0), category VARCHAR(50) DEFAULT 'general', UNIQUE(name, category) );

Quiz

1. What uniquely identifies a row?

// Filtering & Sorting

×

WHERE Clause

-- Basic filtering SELECT * FROM users WHERE age >= 18; -- Multiple conditions SELECT * FROM users WHERE age >= 18 AND country = 'USA'; -- OR condition SELECT * FROM users WHERE country = 'USA' OR country = 'Canada';

IN and BETWEEN

-- IN - match any in list SELECT * FROM users WHERE country IN ('USA', 'Canada', 'UK'); -- BETWEEN - range SELECT * FROM users WHERE age BETWEEN 18 AND 30;

LIKE Pattern Matching

-- Contains SELECT * FROM users WHERE email LIKE '%@gmail.com'; -- Starts with SELECT * FROM users WHERE name LIKE 'A%'; -- Case insensitive SELECT * FROM users WHERE name ILIKE 'alice';

ORDER BY

-- Sort ascending (default) SELECT * FROM users ORDER BY name; -- Sort descending SELECT * FROM users ORDER BY created_at DESC; -- Multiple columns SELECT * FROM users ORDER BY country ASC, name ASC;

LIMIT and OFFSET

-- First 10 rows SELECT * FROM users LIMIT 10; -- Skip first 20, get next 10 SELECT * FROM users LIMIT 10 OFFSET 20;

Quiz

1. What sorts results?

// Joins

×

INNER JOIN

-- Matched rows from both tables SELECT orders.id, users.name, orders.total FROM orders INNER JOIN users ON orders.user_id = users.id;

LEFT JOIN

-- All from left, matched from right SELECT users.name, orders.id FROM users LEFT JOIN orders ON users.id = orders.user_id;

Multiple Joins

SELECT orders.id, users.name, products.name FROM orders JOIN users ON orders.user_id = users.id JOIN products ON orders.product_id = products.id;

Self Join

-- Join table to itself SELECT e.name AS employee, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.id;

Quiz

1. What join includes all left table rows?

// Aggregations

×

COUNT, SUM, AVG

-- Count rows SELECT COUNT(*) FROM users; -- Sum values SELECT SUM(total) FROM orders; -- Average SELECT AVG(age) FROM users;

MIN and MAX

SELECT MIN(price) AS cheapest, MAX(price) AS most_expensive FROM products;

GROUP BY

-- Group and aggregate SELECT country, COUNT(*) as user_count FROM users GROUP BY country; -- With multiple aggregates SELECT country, COUNT(*) as total, AVG(age) as avg_age FROM users GROUP BY country;

HAVING

-- Filter groups SELECT country, COUNT(*) as count FROM users GROUP BY country HAVING COUNT(*) > 10;

Quiz

1. What filters grouped results?

// Indexes

×

Creating Indexes

-- Basic index CREATE INDEX idx_users_email ON users(email); -- Composite index CREATE INDEX idx_orders_user_status ON orders(user_id, status);

Unique Index

CREATE UNIQUE INDEX idx_users_email ON users(email);

Index Types

-- B-tree (default, good for comparisons) CREATE INDEX idx_users_name ON users USING btree(name); -- GIN (good for arrays, JSONB) CREATE INDEX idx_products_tags ON products USING GIN(tags); -- Full-text search CREATE INDEX idx_articles_content ON articles USING GIN(to_tsvector('english', content));

Explain Plans

EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

Quiz

1. What command shows query plan?

// Subqueries

×

Subquery in WHERE

-- Users who placed orders SELECT * FROM users WHERE id IN (SELECT user_id FROM orders); -- Products not ordered SELECT * FROM products WHERE id NOT IN (SELECT product_id FROM orders);

Scalar Subquery

-- Subquery returning single value SELECT name, (SELECT COUNT(*) FROM orders WHERE user_id = users.id) as order_count FROM users;

Common Table Expressions (CTE)

WITH recent_orders AS ( SELECT * FROM orders WHERE created_at > '2024-01-01' ) SELECT * FROM recent_orders JOIN users ON recent_orders.user_id = users.id;

Quiz

1. What is CTE short for?

// Transactions

×

BEGIN, COMMIT, ROLLBACK

-- Start transaction BEGIN; -- Multiple operations UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2; -- Save work COMMIT; -- Or undo ROLLBACK;

Savepoints

BEGIN; INSERT INTO logs (action) VALUES ('start'); SAVEPOINT sp1; INSERT INTO logs (action) VALUES ('middle'); ROLLBACK TO SAVEPOINT sp1; INSERT INTO logs (action) VALUES ('end'); COMMIT;

Isolation Levels

-- Set isolation level SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- Options: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE

Quiz

1. What undoes a transaction?

// Views & Functions

×

Views

-- Create view CREATE VIEW user_orders AS SELECT users.name, COUNT(orders.id) as order_count FROM users LEFT JOIN orders ON users.id = orders.user_id GROUP BY users.id; -- Use view SELECT * FROM user_orders;

Stored Functions

CREATE OR REPLACE FUNCTION get_user_count() RETURNS INTEGER AS $$ BEGIN RETURN (SELECT COUNT(*) FROM users); END; $$ LANGUAGE plpgsql; -- Call function SELECT get_user_count();

Functions with Parameters

CREATE OR REPLACE FUNCTION get_user_by_name(name VARCHAR) RETURNS TABLE(id INTEGER, email VARCHAR) AS $$ BEGIN RETURN QUERY SELECT users.id, users.email FROM users WHERE users.name = get_user_by_name.name; END; $$ LANGUAGE plpgsql;

Quiz

1. What is a saved query called?

// JSON & Advanced Types

×

JSON and JSONB

-- Create table with JSON CREATE TABLE events ( id SERIAL PRIMARY KEY, data JSON ); -- Insert JSON data INSERT INTO events (data) VALUES ('{"type": "click", "page": "/home"}');

Querying JSON

-- Extract field (-> returns JSON, ->> returns text) SELECT data->>'type' FROM events; -- Query nested SELECT * FROM events WHERE data->>'page' = '/home';

Arrays

-- Create table with array CREATE TABLE products ( id SERIAL PRIMARY KEY, name VARCHAR(100), tags TEXT[] ); -- Query array SELECT * FROM products WHERE 'electronics' = ANY(tags);

Range Types

-- Date ranges CREATE TABLE reservations ( id SERIAL PRIMARY KEY, room INTEGER, dates DATERANGE ); -- Overlap operator SELECT * FROM reservations WHERE dates && '[2024-01-15,2024-01-20)'::daterange;

Quiz

1. What extracts text from JSON?