PostgreSQL AS

AS Alias Types

SELECT name AS customer_name
FROM customers;

SELECT price * 0.9 
AS discounted_price;

SELECT c.name 
FROM customers AS c;

SELECT COUNT(*) AS total_orders
FROM orders;

🔹 Basic Column Alias

Column aliases rename columns in query results, making output more user-friendly and descriptive. The original column name in the database remains unchanged. Aliases are temporary and only exist for the duration of the query. Use them to clarify column meanings or replace technical names with business terms.

-- Sample data: employees table
-- emp_id | first_name | last_name | annual_salary
-- 1      | John       | Smith     | 75000
-- 2      | Jane       | Doe       | 82000

-- Use aliases for clearer column names
SELECT 
    first_name AS "First Name",
    last_name AS "Last Name",
    annual_salary AS "Yearly Salary"
FROM employees;

🔹 Alias for Calculations

When performing calculations in SELECT statements, the result column gets a generic name. Use aliases to give calculated columns meaningful names. This makes your results self-documenting and easier to understand. Essential for reports, dashboards, and any output that will be read by humans or processed by applications.

-- Calculate total price with meaningful column name
SELECT 
    product_name,
    price,
    quantity,
    price * quantity AS total_cost,
    price * quantity * 0.1 AS tax_amount
FROM order_items;

🔹 Alias with Aggregate Functions

Aggregate functions like COUNT, SUM, AVG produce results with generic or complex names. Always use aliases with aggregates to create clear, descriptive column headers. This is crucial for reports and analytics where the meaning of each metric must be immediately obvious to readers and stakeholders.

-- Use aliases for aggregate function results
SELECT 
    COUNT(*) AS total_orders,
    SUM(amount) AS total_revenue,
    AVG(amount) AS average_order_value,
    MAX(amount) AS largest_order
FROM orders;

🔹 Table Aliases

Table aliases shorten long table names, making queries more concise and readable. They're essential when working with joins where you need to specify which table a column comes from. Use short, meaningful aliases like 'c' for customers or 'o' for orders. Once defined, use the alias throughout the query.

-- Use table aliases to shorten queries
SELECT 
    c.name AS customer_name,
    c.email,
    o.order_date,
    o.total_amount
FROM customers AS c
JOIN orders AS o ON c.customer_id = o.customer_id;

🔹 AS Keyword is Optional

The AS keyword is optional in PostgreSQL - you can create aliases by simply placing the alias name after the column or table. However, using AS makes your code more explicit and easier to read. It's considered best practice to include AS for clarity, especially in complex queries or team environments.

-- Both syntaxes work the same way

-- With AS (recommended for clarity)
SELECT name AS customer_name FROM customers AS c;

-- Without AS (works but less clear)
SELECT name customer_name FROM customers c;

🔹 Aliases with Spaces

If your alias contains spaces or special characters, enclose it in double quotes. This allows you to create more natural, human-readable column names. Without quotes, PostgreSQL treats spaces as syntax errors. Use this feature for reports and exports where column headers need to be business-friendly and professional-looking.

-- Use quotes for aliases with spaces
SELECT 
    first_name AS "First Name",
    last_name AS "Last Name",
    salary * 12 AS "Annual Salary",
    department AS "Department Name"
FROM employees;

🔹 Using Aliases in ORDER BY

You can reference column aliases in ORDER BY and GROUP BY clauses, but not in WHERE clauses. This is because WHERE is evaluated before SELECT, so aliases don't exist yet. However, ORDER BY and GROUP BY run after SELECT, making aliases available. This simplifies sorting and grouping by calculated columns.

-- Use alias in ORDER BY
SELECT 
    product_name,
    price * quantity AS total_value
FROM order_items
ORDER BY total_value DESC;