Intermediate

INNER JOIN

Combine rows from two tables where a matching condition is met.

LEFT JOIN (LEFT OUTER JOIN)

Return all rows from the left table, with NULLs where no match exists in the right table.

FULL OUTER JOIN

Return all rows from both tables, with NULLs where no match exists on either side.

Joining Multiple Tables

Chain multiple JOINs to combine data from three or more tables in a single query.

One-to-Many and Many-to-Many Relationships

Design and query the two most common relationship types in relational databases.

LATERAL JOIN

Use LATERAL to reference earlier FROM items in subqueries for row-by-row computation.

Subqueries

Subqueries: Scalar, IN, and Derived Tables

Use nested SELECT statements for complex filtering, computation, and data transformation.

EXISTS and NOT EXISTS

Check for the presence or absence of related rows efficiently using EXISTS.

Set Operators

UNION, INTERSECT, and EXCEPT

Combine result sets from multiple SELECT statements using set operators.

Advanced Functions

Advanced String and Date Functions

Master powerful string manipulation and date formatting functions in PostgreSQL.

Regular Expressions

Match, extract, and transform text using POSIX regular expressions in PostgreSQL.

Window Functions Introduction

Perform calculations across related rows without collapsing them using window functions.

LEAD and LAG Window Functions

Access previous and next row values in the same result set using LEAD and LAG.

Window Frame: ROWS and RANGE

Control the window frame size for moving averages and rolling calculations.

STRING_AGG and ARRAY_AGG

Aggregate multiple rows into a comma-separated string or an array.

Full-Text Search

Search text documents intelligently using to_tsvector, to_tsquery, and GIN indexes.

Conditional Aggregates and FILTER

Aggregate values conditionally using CASE inside aggregates and the FILTER clause.

JSON & JSONB

JSONB Working with JSON Data

Store, query, and index semi-structured JSON data using PostgreSQL's powerful JSONB type.

Array Data Type

Store multiple values in a single column using PostgreSQL's native array type.

Advanced JSONB Operations

Query, filter, and update JSONB documents using advanced operators and functions.

Range Types

Store and query continuous ranges of values using PostgreSQL's built-in range types.

Views & Indexes

Views (CREATE VIEW)

Create reusable named queries as views to simplify complex SQL.

Materialized Views

Cache expensive query results with materialized views and refresh on demand.

Indexes: B-Tree, GIN, GiST, Hash

Speed up queries by creating the right type of index for your data and access patterns.

EXPLAIN and EXPLAIN ANALYZE

Read query execution plans to understand and optimize how PostgreSQL runs your queries.

Transactions

Transaction Isolation Levels

Control how concurrent transactions see each other's data with isolation levels.

Stored Programs

Functions (CREATE FUNCTION)

Encapsulate reusable SQL logic in user-defined functions.

Stored Procedures (CREATE PROCEDURE)

Create procedures that can contain transaction control and have no return value.

PL/pgSQL Basics

Write procedural logic in PostgreSQL's native PL/pgSQL language.

Generated Columns

Automatically compute column values from other columns using GENERATED ALWAYS AS.

ENUM Types

Create enumerated types for columns with a fixed set of allowed values.

UUID Primary Keys

Use UUIDs as globally unique identifiers for distributed and security-sensitive applications.

PostgreSQL Extensions

Extend PostgreSQL functionality with installable modules using CREATE EXTENSION.

CTEs

CTEs Common Table Expressions (WITH)

Write readable, reusable query fragments using the WITH clause.

Recursive CTEs (WITH RECURSIVE)

Traverse hierarchical and graph data with recursive common table expressions.

Upsert: INSERT ON CONFLICT and MERGE

Insert or update rows atomically using ON CONFLICT and the MERGE statement.

Temporary Objects

Temporary Tables

Create session-scoped tables that exist only for the duration of a session or transaction.

COPY and Bulk Loading

Import and export large datasets efficiently using the COPY command.