Chapter 30: PostgreSQL AS
the AS keyword (and its very frequent optional version — no AS at all).
Many beginners think “AS is just for renaming columns”, but it is actually much more powerful and appears in several different places in a query.
Today I’m going to explain every realistic use of AS in PostgreSQL (version 18 reality in 2026), step-by-step, like we’re writing queries together on your laptop.
1. The main job of AS — column aliasing (90% of its daily usage)
AS gives a temporary new name to a column or an expression in the result set.
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 |
SELECT first_name AS "First Name", -- with AS last_name surname, -- without AS (very common) gpa * 10 AS percentage, -- calculated column email AS student_email_address -- longer descriptive name FROM students; |
Very important rules in PostgreSQL:
- If the alias contains spaces, uppercase letters you want to preserve, or special characters → you must use double quotes ” “
- Without quotes → alias becomes lowercase (PostgreSQL folds unquoted identifiers)
- AS is optional when the alias is a simple word (most developers skip it)
Examples side-by-side — both are correct:
SQL
|
0 1 2 3 4 5 6 7 8 |
SELECT gpa * 9.5 AS "Percentage Score" FROM students; -- with AS and quotes SELECT gpa * 9.5 "Percentage Score" FROM students; -- AS optional SELECT gpa * 9.5 percentage_score FROM students; -- lowercase, no quotes, no AS |
2. Table aliasing (extremely common — especially with JOINs)
When you join multiple tables, you give short temporary names to each table so you don’t have to write the full table name every time.
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT s.first_name || ' ' || s.last_name AS full_name, c.name AS course_name, e.grade FROM students AS s -- table alias JOIN enrollments AS e ON e.student_id = s.id JOIN courses AS c ON c.id = e.course_id WHERE s.gpa > 8.5 ORDER BY full_name; |
- AS is optional here too → most people write students s (without AS)
- Short aliases (s, e, c, u, o…) make queries much easier to read
Without aliases (painful):
SQL
|
0 1 2 3 4 5 6 7 8 9 |
SELECT students.first_name || ' ' || students.last_name FROM students JOIN enrollments ON enrollments.student_id = students.id ... |
3. AS in CTEs (Common Table Expressions) — very modern & powerful
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
WITH active_students AS ( -- CTE name with AS SELECT * FROM students WHERE is_active = TRUE AND gpa >= 8.0 ), high_gpa AS ( SELECT AVG(gpa) AS avg_high_gpa FROM active_students ) SELECT first_name, gpa, (SELECT avg_high_gpa FROM high_gpa) AS class_high_avg FROM active_students ORDER BY gpa DESC; |
Here AS is required after the CTE name.
4. AS in subqueries (derived tables)
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
SELECT city, student_count FROM ( SELECT address->>'city' AS city, COUNT(*) AS student_count FROM students WHERE address IS NOT NULL GROUP BY address->>'city' ) AS city_stats -- subquery alias with AS WHERE student_count >= 2 ORDER BY student_count DESC; |
Again — AS is required when you give a name to a subquery (derived table).
5. AS with functions & expressions (very clean)
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 |
SELECT NOW() AS current_time_ist, CURRENT_DATE AS today_date, EXTRACT(YEAR FROM NOW()) AS current_year, ROUND(AVG(gpa), 2) AS average_gpa FROM students; |
6. Quick cheat-sheet — when AS is required vs optional
| Place | AS required? | Example (preferred style 2026) | Notes |
|---|---|---|---|
| Column / expression alias | Optional | gpa * 10 AS percentage or gpa * 10 percentage | Use AS when alias has spaces |
| Table alias in FROM/JOIN | Optional | students s or students AS s | Most people skip AS |
| CTE name | Required | WITH active AS (…) | Must have name |
| Subquery / derived table | Required | FROM (SELECT …) AS sub | Must have name |
| CAST / type conversion | Optional | gpa::numeric(4,2) AS gpa_formatted | — |
7. Common beginner mistakes & fixes
| Mistake | Error / Problem | Fix |
|---|---|---|
| SELECT name as “Full Name” (single quotes) | Syntax error | Use double quotes “Full Name” |
| SELECT name AS full name (no quotes) | Becomes lowercase full name (two words) | “Full Name” or full_name |
| Forget alias on subquery | ERROR: subquery must have alias | Add AS tmp or any name |
| Use same alias name twice in same SELECT | ERROR: column reference ambiguous | Use different names or qualify |
| AS in WHERE / GROUP BY / HAVING | Syntax error | AS only in SELECT / FROM / WITH |
Your mini practice right now (try in your database)
- Select first_name || ‘ ‘ || last_name as “Full Name”
- Join students and enrollments using short aliases s and e
- Create a CTE called good_students with GPA > 8.5, then select from it
- Use a subquery to count students per city and alias it city_summary
Next topic?
Tell me:
- Want to compare AS alias vs no alias performance / readability?
- Deep dive into CTE (WITH clause) with many real examples?
- How AS works with JOIN USING / JOIN ON?
- Or move to another small-but-important keyword (DISTINCT, ALL, CAST)?
Your teacher is ready — what’s next? 🚀
