By the end of this lesson you can give result columns and tables clear aliases, choose between plain and quoted names, and explain (and fix) the 'column does not exist' error caused by using an alias in WHERE.
Why it matters: Every report you hand over has column headings someone else must understand, and every join you'll write uses short table aliases. Knowing when PostgreSQL can and cannot see an alias saves you from one of the most common errors in SQL.
Dataset: Wadi Market online store. Examples run in the editor on the right.
An alias is a temporary name. A column alias renames a column in the result; a table alias gives a table a short nickname inside one query. Neither changes anything in the database: the next query sees the original names again.
You write a column alias with AS after the column or expression. Aliases matter most for calculated columns, which otherwise get a meaningless name, and for raw column names that are fine for developers but unclear for readers (unit_price becomes list_price).
This lesson switches to Wadi Market, an online store with real-sized tables. Its products table has a unit_price (today's list price) and a unit_cost (what Wadi Market pays its supplier), which is NULL for two products whose cost was never recorded.
SELECT column_or_expression AS alias_name,
other_column AS "Name With Spaces"
FROM table_name AS t;SELECT product_name AS product,
unit_price AS list_price,
unit_cost AS cost,
unit_price - unit_cost AS unit_margin
FROM products;Each of the 61 products appears once with four clearly named columns. Look for the Kaftan Dress Eid Edition and the Bakhoor Gift Box: their cost is NULL, so their unit_margin is NULL too, because arithmetic with an unknown value is unknown.
Plain names versus quoted names
A plain alias must start with a letter or underscore and contain only letters, digits and underscores. PostgreSQL folds plain names to lowercase: AS ListPrice becomes listprice. If you need capitals, spaces or symbols in a heading (for a spreadsheet export, say), put the alias in double quotes: AS "List price (SAR)". Double quotes are for names; single quotes are for text values. AS 'List price' is a syntax error in PostgreSQL.
The word AS is optional (unit_price list_price works), but write it anyway. Without it, a forgotten comma silently turns the next column into an alias: SELECT product_name unit_price returns one column of product names called unit_price.
SELECT category_name AS CategoryName,
category_name AS "Category name",
parent_category_id AS "Parent ID"
FROM categories;Run it and read the headings: the first column is called categoryname (folded to lowercase) while the quoted aliases keep their capitals and spaces exactly. All 18 categories are listed; departments have a NULL parent.
Why WHERE cannot use a column alias
You write a query in the order SELECT … FROM … WHERE …, but PostgreSQL works through it in a different, logical order: first FROM (which table), then WHERE (which rows to keep), then SELECT (which columns to compute and name), and finally ORDER BY (how to sort). When WHERE runs, the aliases from SELECT don't exist yet. That is why this fails:
SELECT product_name, unit_price - unit_cost AS unit_margin
FROM products
WHERE unit_margin > 400;
-- ERROR: column "unit_margin" does not existSELECT city, COUNT(*) AS orders, SUM(amount) AS revenue
FROM orders
WHERE status = 'delivered'
GROUP BY city
HAVING COUNT(*) >= 2
ORDER BY revenue DESC
LIMIT 1;FROM: Read the table: all rows.
| city | status | amount |
|---|---|---|
| Riyadh | delivered | 120 |
| Jeddah | cancelled | 90 |
| Riyadh | delivered | 60 |
| Dammam | delivered | 200 |
| Jeddah | delivered | 75 |
| Riyadh | delivered | 40 |
| Dammam | delivered | 30 |
You write SELECT first, but PostgreSQL logically evaluates it after FROM, WHERE, GROUP BY and HAVING. That's why WHERE can't use a SELECT alias, while ORDER BY can.
SELECT product_name,
unit_price - unit_cost AS unit_margin
FROM products
WHERE unit_price - unit_cost > 400
ORDER BY unit_margin DESC;WHERE repeats the calculation because the alias does not exist yet when rows are filtered. ORDER BY runs after SELECT, so it may use unit_margin. The result lists only the high-margin products, largest margin first. (You'll meet WHERE and ORDER BY properly in the coming lessons.)
Table aliases
A table alias goes after the table name in FROM: FROM products AS p. You can then write p.unit_price, which reads as the unit_price column of p. With one table this is optional, but once you join tables that share column names (both products and order_items have unit_price), the prefix is how you tell PostgreSQL which one you mean. Keep table aliases short and meaningful: p for products, o for orders, c for customers.
SELECT p.sku, p.product_name, p.unit_price
FROM products AS p
WHERE p.discontinued;p stands for products throughout the query. The result lists the discontinued products (discontinued is a boolean column, so WHERE p.discontinued keeps the rows where it is true).