🐘 PostgreSQL Tutorials

Choose a lesson below to start learning PostgreSQL.

  1. 1.PostgreSQL Tutorial: Introduction to PostgreSQL (Open-Source RDBMS)Learn what PostgreSQL is, why it's called 'the world's most advanced open-source database', and how it compares to MySQL and other relational databases.
  2. 2.Install PostgreSQL and Get Started with the psql Command LineLearn how to install PostgreSQL, connect with the psql client, and use its most useful backslash commands like \l, \dt, and \d.
  3. 3.PostgreSQL Databases: CREATE DATABASE, DROP DATABASE & ConnectingLearn how to create, list, connect to, and drop databases in PostgreSQL, plus the role of the default postgres and template databases.
  4. 4.PostgreSQL Schemas Explained: Organizing Tables Within a DatabaseLearn what a PostgreSQL schema is, how the default public schema works, how to create your own schemas, and how the search_path resolves table names.
  5. 5.PostgreSQL Data Types: Numeric, Text, Date/Time, JSON, Array & MoreA complete overview of PostgreSQL's data types — integers, numeric/decimal, text, boolean, date/time, UUID, JSON/JSONB, and arrays — with when to use each.
  6. 6.CREATE TABLE in PostgreSQL: Syntax, Defaults & Auto-Increment IDsLearn the CREATE TABLE syntax in PostgreSQL, how SERIAL auto-increments a primary key, default values, and how to create a table only if it doesn't already exist.
  7. 7.PostgreSQL Constraints: PRIMARY KEY, FOREIGN KEY, CHECK, UNIQUE & NOT NULLLearn every major PostgreSQL constraint — PRIMARY KEY, FOREIGN KEY (with ON DELETE options), UNIQUE, CHECK, NOT NULL and DEFAULT — with real table examples.
  8. 8.ALTER TABLE in PostgreSQL: Add, Drop, Rename & Modify ColumnsLearn how to change an existing PostgreSQL table with ALTER TABLE — adding, dropping, renaming, and changing the type of columns, plus renaming the table itself.
  9. 9.DROP TABLE vs TRUNCATE TABLE in PostgreSQL: What's the Difference?Learn the difference between DROP TABLE and TRUNCATE TABLE in PostgreSQL, when to use CASCADE, and why TRUNCATE is faster than deleting every row.
  10. 10.INSERT INTO in PostgreSQL: Single Rows, Multiple Rows & RETURNINGLearn the INSERT INTO statement in PostgreSQL — inserting one row, multiple rows in a single statement, and getting the new row back with RETURNING.
  11. 11.UPDATE Statement in PostgreSQL: Syntax, WHERE Clause & RETURNINGLearn how to update rows in PostgreSQL with the UPDATE statement, why the WHERE clause matters, updating from another table, and RETURNING the changed rows.
  12. 12.DELETE FROM in PostgreSQL: Removing Rows SafelyLearn how to delete rows in PostgreSQL with DELETE FROM, why the WHERE clause is critical, deleting based on another table, and using RETURNING and TRUNCATE.
  13. 13.UPSERT in PostgreSQL: INSERT ... ON CONFLICT DO UPDATELearn how to perform an upsert in PostgreSQL using INSERT ... ON CONFLICT — insert a new row or update the existing one if a unique constraint conflicts.
  14. 14.SELECT and WHERE in PostgreSQL: Querying and Filtering RowsLearn PostgreSQL's SELECT statement — choosing columns, aliasing them, and filtering rows with the WHERE clause using comparison and logical operators.
  15. 15.ORDER BY, LIMIT and OFFSET in PostgreSQL (With Pagination Example)Learn how to sort query results with ORDER BY, cap them with LIMIT, and page through them with OFFSET — the standard PostgreSQL pagination pattern.
  16. 16.PostgreSQL Filtering Operators: BETWEEN, IN, LIKE, IS NULLLearn PostgreSQL's most useful filtering operators — BETWEEN for ranges, IN for lists, LIKE/ILIKE for pattern matching, and IS NULL for checking missing values.
  17. 17.PostgreSQL Aggregate Functions, GROUP BY and HAVING ExplainedLearn PostgreSQL's aggregate functions (COUNT, SUM, AVG, MIN, MAX), how GROUP BY summarizes rows into groups, and why HAVING filters groups where WHERE can't.
  18. 18.SQL Joins in PostgreSQL: INNER, LEFT, RIGHT, FULL & Self JoinsLearn every type of SQL join in PostgreSQL — INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, CROSS JOIN, and self joins — with clear examples for each.
  19. 19.Subqueries in PostgreSQL: Scalar, IN, EXISTS & Correlated SubqueriesLearn PostgreSQL subqueries — scalar subqueries, filtering with IN and EXISTS, and correlated subqueries that reference the outer query's row.
  20. 20.UNION, INTERSECT and EXCEPT in PostgreSQLLearn PostgreSQL's set operators — UNION and UNION ALL for combining results, INTERSECT for common rows, and EXCEPT for rows in one query but not another.
  21. 21.Common Table Expressions (WITH) in PostgreSQL, Including Recursive CTEsLearn how to use WITH clauses (CTEs) in PostgreSQL to break complex queries into readable steps, and how recursive CTEs walk hierarchical data.
  22. 22.PostgreSQL Window Functions: ROW_NUMBER, RANK, LAG, LEAD & OVERLearn PostgreSQL window functions — ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD — and how PARTITION BY lets you compute running totals and rankings per group.
  23. 23.JSON and JSONB in PostgreSQL: Storing and Querying Semi-Structured DataLearn how to store and query JSON data in PostgreSQL with the JSONB type, the -> and ->> operators, containment queries, and indexing JSONB with GIN.
  24. 24.Array Data Type in PostgreSQL: Storing and Querying ListsLearn how to store arrays in a PostgreSQL column, insert and update array values, query with ANY/ALL and the contains operator, and unnest an array into rows.
  25. 25.Full-Text Search in PostgreSQL: tsvector, tsquery & GIN IndexesLearn how PostgreSQL's built-in full-text search works with to_tsvector, to_tsquery, the @@ match operator, ranking with ts_rank, and speeding it up with a GIN index.
  26. 26.PostgreSQL Indexing Basics: How Indexes Speed Up QueriesLearn what an index is in PostgreSQL, how to create one with CREATE INDEX, and how to prove it's being used with EXPLAIN — the foundation of query performance.
  27. 27.Types of PostgreSQL Indexes: B-tree, Hash, GIN, GiST & BRINLearn every PostgreSQL index type — the default B-tree, Hash indexes, GIN for arrays/JSONB/full-text, GiST for geometric/range data, and BRIN for huge sorted tables.
  28. 28.EXPLAIN and EXPLAIN ANALYZE in PostgreSQL: Reading a Query PlanLearn how to read PostgreSQL's EXPLAIN and EXPLAIN ANALYZE output — Seq Scan vs Index Scan, cost estimates, actual execution time, and how to spot a slow query.
  29. 29.PostgreSQL Query Optimization: Practical Tips for Faster QueriesPractical PostgreSQL performance tips — indexing the right columns, avoiding SELECT *, using EXISTS over IN, keeping statistics fresh, and connection pooling.
  30. 30.PostgreSQL Transactions: BEGIN, COMMIT, ROLLBACK & SAVEPOINTLearn PostgreSQL transactions — BEGIN/COMMIT/ROLLBACK, why they matter for correctness, savepoints for partial rollbacks, and the ACID properties they guarantee.
  31. 31.PostgreSQL Transaction Isolation Levels: Read Committed, Repeatable Read, SerializableLearn PostgreSQL's transaction isolation levels — Read Committed (the default), Repeatable Read, and Serializable — and the anomalies each one prevents.
  32. 32.Locking and Concurrency in PostgreSQL: FOR UPDATE, Deadlocks & pg_locksLearn how PostgreSQL locks rows with SELECT ... FOR UPDATE, how deadlocks happen and get resolved automatically, and how to inspect active locks with pg_locks.
  33. 33.Views in PostgreSQL: CREATE VIEW and Why They're UsefulLearn how to create and use PostgreSQL views — saved, reusable queries that behave like a virtual table — including updatable views and dropping them.
  34. 34.Materialized Views in PostgreSQL: Caching Expensive Query ResultsLearn how PostgreSQL materialized views differ from regular views by physically storing results, how REFRESH MATERIALIZED VIEW updates them, and when to use one.
  35. 35.PostgreSQL Functions and PL/pgSQL: Writing Stored ProceduresLearn how to write PostgreSQL functions in PL/pgSQL — with parameters, variables, IF/loops, and RETURN — plus the difference between a FUNCTION and a PROCEDURE.
  36. 36.PostgreSQL Triggers: Automatically Running Code on INSERT, UPDATE, DELETELearn how PostgreSQL triggers work — CREATE TRIGGER paired with a trigger function, BEFORE vs AFTER timing, and a real example that auto-updates an updated_at column.
  37. 37.PostgreSQL Users, Roles and Permissions: GRANT, REVOKE & CREATE ROLELearn PostgreSQL's role-based access system — creating roles/users, granting and revoking privileges on tables, and following the principle of least privilege.
  38. 38.PostgreSQL Backup & Restore: pg_dump, pg_restore and psqlLearn how to back up a PostgreSQL database with pg_dump in plain SQL or custom format, restore it with psql or pg_restore, and back up an entire server with pg_dumpall.
  39. 39.CASE Expressions in PostgreSQL: Conditional Logic in SQLLearn PostgreSQL's CASE expression — simple and searched forms — for building conditional logic directly inside SELECT, ORDER BY, and WHERE clauses.
  40. 40.NULL Semantics in PostgreSQL: COALESCE, NULLIF & Three-Valued LogicLearn how NULL actually behaves in PostgreSQL — three-valued logic, NULLs in joins and aggregates, and the COALESCE and NULLIF functions for handling them.
  41. 41.PostgreSQL String Functions: CONCAT, SUBSTRING, TRIM, UPPER/LOWER & MoreLearn PostgreSQL's essential string functions — concatenation with || and CONCAT, LENGTH, UPPER/LOWER, TRIM, SUBSTRING, REPLACE, SPLIT_PART and formatting with LPAD.
  42. 42.Regular Expressions in PostgreSQL: ~, ~*, regexp_replace & regexp_matchesLearn PostgreSQL's regex operators (~, ~*, !~) for pattern matching, plus regexp_replace() and regexp_matches() for extracting and transforming text with regular expressions.
  43. 43.PostgreSQL Date & Time Functions: EXTRACT, date_trunc, AGE & generate_seriesLearn PostgreSQL's date/time functions — now(), EXTRACT, date_trunc for grouping by period, AGE for calculating durations, and generate_series for building time series.
  44. 44.Advanced Aggregate Functions in PostgreSQL: FILTER, STRING_AGG & ARRAY_AGGLearn PostgreSQL's advanced aggregate features — the FILTER clause for conditional aggregation, STRING_AGG and ARRAY_AGG for collecting values, and ordered-set aggregates.
  45. 45.Anti-Joins, Semi-Joins & Non-Equi Joins in PostgreSQLLearn PostgreSQL anti-joins (NOT EXISTS/LEFT JOIN WHERE NULL) for 'rows with no match', semi-joins (EXISTS/IN) for 'rows with at least one match', and non-equi joins with inequality conditions.
  46. 46.LATERAL Joins in PostgreSQL: Correlated Subqueries in the FROM ClauseLearn PostgreSQL's LATERAL join — how it lets a subquery in FROM reference columns from an earlier table, and the classic 'top N per group' pattern it enables.
  47. 47.More PostgreSQL Window Functions: NTILE, FIRST_VALUE, LAST_VALUE & Frame ClausesLearn advanced PostgreSQL window function features — NTILE for bucketing rows, FIRST_VALUE/LAST_VALUE, and the ROWS/RANGE frame clause that controls moving averages.
  48. 48.GROUPING SETS, ROLLUP and CUBE in PostgreSQL: Multi-Level AggregationLearn PostgreSQL's GROUPING SETS, ROLLUP and CUBE for computing multiple levels of subtotals in a single query, plus how to pivot rows into columns with FILTER.
  49. 49.Range Types & Exclusion Constraints in PostgreSQLLearn PostgreSQL's range types (int4range, daterange, tstzrange), the containment and overlap operators, and EXCLUDE constraints for preventing overlapping bookings.
  50. 50.Sequences & Column Defaults in PostgreSQL: How SERIAL Really WorksLearn what a PostgreSQL sequence actually is, how SERIAL and IDENTITY columns are built on top of it, and how to reset, skip, or manually control one.
  51. 51.Database Normalization Explained: 1NF, 2NF, 3NF (With PostgreSQL Examples)Learn database normalization in PostgreSQL — 1NF, 2NF and 3NF explained with a real before/after table redesign, plus when denormalization is the right trade-off.
  52. 52.Data-Modifying CTEs, RETURNING and MERGE in PostgreSQLLearn how to chain INSERT/UPDATE/DELETE together using data-modifying CTEs, use RETURNING to get changed rows back, and PostgreSQL's MERGE statement for upsert-style logic.
  53. 53.Table Partitioning in PostgreSQL: Range, List & Hash PartitionsLearn PostgreSQL table partitioning — splitting one logical table into smaller physical pieces by range, list, or hash — and how partition pruning speeds up queries.
  54. 54.PostgreSQL Internals: MVCC, VACUUM and the Write-Ahead Log (WAL)Learn how PostgreSQL works under the hood — MVCC for concurrent access without locking readers, why VACUUM is necessary, autovacuum, and what the WAL provides.
  55. 55.Bulk Loading Data in PostgreSQL with COPYLearn PostgreSQL's COPY command for fast bulk loading and exporting of CSV data, why it's dramatically faster than individual INSERT statements, and \copy in psql.
  56. 56.Incremental Loads and Slowly Changing Dimensions (SCD Type 1 & 2) in PostgreSQLLearn data engineering patterns in PostgreSQL — incremental loading with a watermark column, and Slowly Changing Dimension Type 1 (overwrite) and Type 2 (history) with SQL examples.
  57. 57.Dimensional Modeling in PostgreSQL: Star Schema, Fact & Dimension TablesLearn dimensional modeling for analytics — the star schema pattern with fact and dimension tables in PostgreSQL, and how it differs from a normalized OLTP schema.
  58. 58.Data Reconciliation and Data Quality Checks in PostgreSQLLearn practical SQL patterns for data reconciliation in PostgreSQL — row-count checks, checksum comparisons, finding orphaned rows, and detecting duplicates.
  59. 59.Using PostgreSQL with Python (psycopg2) and Apache AirflowLearn how to connect to PostgreSQL from Python with psycopg2, safely parameterize queries to prevent SQL injection, and orchestrate PostgreSQL tasks in an Airflow DAG.
  60. 60.Classic PostgreSQL Interview Problems (With Solutions)Practice the most commonly asked PostgreSQL/SQL interview problems — second highest salary, duplicate rows, running totals, consecutive days, and top N per group — with full solutions.