Chapter 8: PostgreSQL Select Data
PostgreSQL SELECT Data ☕🔍
You’ve already:
- Created databases & tables (CREATE TABLE)
- Put data inside (INSERT)
- Now we learn how to ask questions and get data back — that’s what SELECT is all about.
This is the most used command in PostgreSQL (and in any relational database). Almost every app, report, dashboard, API — they all start with some form of SELECT.
1. What does SELECT actually do? (Teacher’s plain-English explanation)
SELECT tells PostgreSQL: “Go look in these table(s), apply my filters & calculations, and return me a result set — which is basically a temporary table of rows & columns that match what I asked for.”
- It never changes the data in the database (read-only).
- It can be super simple (SELECT * FROM students) or extremely complex (joins + windows + CTEs + JSON + aggregates).
- PostgreSQL processes it in a very specific logical order (FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT).
Official PostgreSQL 18 docs (right now) start with:
SELECT retrieves rows from zero or more tables.
But in real life: SELECT is how you read data.
2. Basic syntax skeleton (the shapes you’ll see every day)
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 |
SELECT [DISTINCT] column1, column2, expression AS alias, ... FROM table_name [AS alias] [WHERE condition] [ORDER BY column [ASC|DESC], ...] [LIMIT n] [OFFSET m] ; |
We’ll build from simplest → advanced.
3. Real example setup — using our college_db
Assume we have these tables with some data (from previous INSERT classes):
students
| id | first_name | last_name | gpa | date_of_birth | is_active | address (JSONB) | |
|---|---|---|---|---|---|---|---|
| 1 | Aarav | Patel | aarav.patel@hyduni.edu | 8.95 | 2003-07-14 | true | {“city”: “Hyderabad”, “pin”: “500081”} |
| 2 | Priya | Reddy | priya.reddy22@gmail.com | 9.10 | 2002-11-03 | true | {“city”: “Secunderabad”} |
| 3 | Rahul | Sharma | rahul.sharma.2004@yahoo.com | 7.85 | 2004-02-28 | true | null |
| 4 | Sneha | Kumar | sneha.kumar@outlook.com | 8.60 | 2003-05-19 | true | {“city”: “Hyderabad”, “area”: “Gachibowli”} |
| 5 | Meera | Iyer | meera.iyer@gmail.com | 9.75 | null | true | {“city”: “Hyderabad”} |
(We also have courses and enrollments — we’ll use them later.)
4. Level 1: Super basic SELECT (your first queries)
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
-- All columns, all rows (the classic) SELECT * FROM students; -- Specific columns only (better for performance & clarity) SELECT first_name, last_name, gpa FROM students; -- Rename columns with AS (very useful for reports) SELECT first_name AS "First Name", last_name AS "Last Name", gpa AS "Grade Point Average" FROM students; -- Simple calculations SELECT first_name || ' ' || last_name AS full_name, gpa, gpa * 10 AS percentage FROM students; |
5. Level 2: Filtering with WHERE (the real power starts here)
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
-- Students with GPA >= 9.0 SELECT first_name, last_name, gpa FROM students WHERE gpa >= 9.0; -- Exact match SELECT * FROM students WHERE email = 'priya.reddy22@gmail.com'; -- Multiple conditions (AND / OR) SELECT first_name, last_name, gpa FROM students WHERE gpa > 8.5 AND is_active = TRUE AND date_of_birth > '2003-01-01'; -- Pattern matching (LIKE % = wildcard) SELECT first_name, email FROM students WHERE email LIKE '%@gmail.com'; -- JSONB filtering (very common in 2026 apps) SELECT first_name, address->>'city' AS city FROM students WHERE address @> '{"city": "Hyderabad"}'; |
6. Level 3: Sorting & limiting (ORDER BY + LIMIT / OFFSET)
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
-- Top 3 students by GPA descending SELECT first_name, last_name, gpa FROM students ORDER BY gpa DESC LIMIT 3; -- Page 2 of 2 students per page (OFFSET skips first page) SELECT first_name, last_name, gpa FROM students ORDER BY first_name ASC LIMIT 2 OFFSET 2; |
7. Level 4: Removing duplicates (DISTINCT)
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 |
-- Unique cities from address JSONB SELECT DISTINCT address->>'city' AS city FROM students WHERE address IS NOT NULL; -- Unique GPA values SELECT DISTINCT gpa FROM students ORDER BY gpa; |
8. Level 5: Aggregates (COUNT, SUM, AVG, MIN, MAX)
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
-- How many students? SELECT COUNT(*) AS total_students FROM students; -- Average GPA SELECT AVG(gpa) AS average_gpa FROM students; -- Best & worst GPA SELECT MIN(gpa) AS lowest_gpa, MAX(gpa) AS highest_gpa FROM students; -- Group by city (with JSONB) SELECT address->>'city' AS city, COUNT(*) AS students_in_city, ROUND(AVG(gpa), 2) AS avg_gpa FROM students WHERE address IS NOT NULL GROUP BY address->>'city' HAVING COUNT(*) > 1; |
9. Level 6: JOINs — combining tables (the relational magic)
Assume we have some enrollments:
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
-- Which students enrolled in which courses with grades? SELECT s.first_name || ' ' || s.last_name AS student, c.name AS course, e.grade FROM students s JOIN enrollments e ON s.id = e.student_id JOIN courses c ON c.id = e.course_id ORDER BY student, course; |
Variants:
- INNER JOIN = only matching rows (default JOIN)
- LEFT JOIN = all students, even if no enrollment (shows NULL for course/grade)
- RIGHT JOIN / FULL JOIN = less common but useful
10. Level 7: Quick look at advanced SELECT features in PostgreSQL 18 (2026)
- Window functions (running totals, ranks)
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 |
SELECT first_name, gpa, RANK() OVER (ORDER BY gpa DESC) AS rank, AVG(gpa) OVER () AS class_average FROM students; |
- Common Table Expressions (CTE / WITH)
SQL
|
0 1 2 3 4 5 6 7 8 9 |
WITH top_students AS ( SELECT * FROM students WHERE gpa >= 9.0 ) SELECT * FROM top_students ORDER BY gpa DESC; |
- LATERAL subqueries, JSON aggregation, etc. — we can go deeper later.
Summary cheat-sheet — SELECT building blocks
| Clause | Purpose | Example feeling |
|---|---|---|
| SELECT | What columns / calculations | first_name, gpa * 10 AS percent |
| FROM | Which table(s) | students s JOIN enrollments e … |
| WHERE | Filter rows | gpa > 8.5 AND city = ‘Hyderabad’ |
| GROUP BY | Aggregate groups | GROUP BY city |
| HAVING | Filter after GROUP BY | HAVING COUNT(*) > 1 |
| ORDER BY | Sort result | ORDER BY gpa DESC |
| LIMIT / OFFSET | Pagination / top-N | LIMIT 10 OFFSET 20 |
| DISTINCT | Remove duplicates | DISTINCT city |
Your mini practice right now
- Run SELECT first_name, last_name, gpa FROM students WHERE gpa > 8.8 ORDER BY gpa DESC;
- Try the JSONB city filter
- Count how many students per city (use GROUP BY)
- In pgAdmin: use the Query Tool, highlight & F5 — see the nice grid!
Next class?
Tell me:
- Want deeper JOINs (LEFT, self-join, many-to-many)?
- Aggregates + GROUP BY + HAVING in detail?
- Window functions (RANK, ROW_NUMBER, running total)?
- Or UPDATE / DELETE to change/remove data?
Your guru is still here — what’s the next topic? 🚀
