PostgreSQL Tutorial

PostgreSQL (often called Postgres) is a free, open-source relational database known for being reliable, strict about correct data, and packed with advanced features.

Many modern web apps, startups and big companies run on PostgreSQL. Popular platforms such as Supabase, Neon and Heroku are built around it, and frameworks like Django, Laravel, Rails, Prisma and Next.js work with it out of the box.

Why Learn PostgreSQL?

• It follows the SQL standard closely, so your SQL skills carry over
• It has extra power: JSONB, arrays, full-text search, window functions
• It is free for any use, with no paid "enterprise" edition needed
• It is in high demand for back-end and full-stack jobs

An Example

This tutorial uses an institute database with students and courses tables.

SELECT name, city, marks
FROM students
WHERE city ILIKE 'lahore'
ORDER BY marks DESC;

       name      |  city  | marks
-----------------+--------+-------
 Sana Iqbal      | Lahore |    88
 Ayesha Siddiqui | Lahore |    85
(2 rows)

ILIKE is a PostgreSQL extra: it matches text without caring about capital letters.

What You Will Learn

• Installing PostgreSQL and using the psql command line
• PostgreSQL data types, including TEXT, NUMERIC, BOOLEAN, arrays and JSONB
• Creating tables with identity columns and constraints
• Inserting, updating and deleting with RETURNING and ON CONFLICT
• Queries and joins, plus PostgreSQL features such as ILIKE and STRING_AGG
• JSONB, views, indexes, transactions and common table expressions

Exercises and Quiz

What to Learn After PostgreSQL

Use PostgreSQL from a back-end with Node.js or PHP. For a different style of database, compare it with MongoDB, or with MySQL.