PostgreSQL Column Alias

In PostgreSQL, a column alias is a temporary name given to a column in the result set of a query. It is useful for making the results more readable or for simplifying complex expressions.

1. Syntax for Column Alias

SELECT column_name AS alias_name
FROM table_name;

column_name: The original column name from the table.
alias_name: The new name for the column in the result set.
AS: The keyword used to define an alias (though it is optional in PostgreSQL).

2. Example 1: Basic Column Alias

Scenario:

Let's assume we have a employees table:

`employees` Table:

employee_id	first_name	last_name	salary
1	Alice	Smith	5000
2	Bob	Johnson	6000
3	Charlie	Brown	7000

Query: Rename the `salary` column for better readability

SELECT salary AS employee_salary
FROM employees;

Result:

employee_salary
5000
6000
7000

Explanation: The salary column is aliased as employee_salary, making the result set more understandable.

3. Example 2: Using Column Alias with Calculations

Scenario:

Consider the employees table again, and let’s calculate a bonus for each employee (e.g., 10% of their salary) and alias that calculation.

Query: Calculate Bonus and Alias It as `bonus_amount`

SELECT first_name, last_name, salary, salary * 0.10 AS bonus_amount
FROM employees;

Result:

first_name	last_name	salary	bonus_amount
Alice	Smith	5000	500
Bob	Johnson	6000	600
Charlie	Brown	7000	700

Explanation: The expression salary * 0.10 is calculated for each employee and is aliased as bonus_amount.

4. Example 3: Using Aliases in `ORDER BY`

You can also use column aliases in the ORDER BY clause to sort the results.

Scenario:

We can use the same bonus_amount alias in the ORDER BY clause to sort employees based on their bonus.

Query: Sort Employees by Bonus Amount

SELECT first_name, last_name, salary, salary * 0.10 AS bonus_amount
FROM employees
ORDER BY bonus_amount DESC;

Result:

first_name	last_name	salary	bonus_amount
Charlie	Brown	7000	700
Bob	Johnson	6000	600
Alice	Smith	5000	500

Explanation: Employees are sorted in descending order based on the alias bonus_amount.

5. Example 4: Using Column Aliases with `GROUP BY`

Column aliases can also be used in the GROUP BY clause when performing aggregation. However, you typically reference the original column in the GROUP BY clause (not the alias).

Query: Count Employees in Each Salary Range

SELECT CASE 
           WHEN salary < 6000 THEN 'Low'
           WHEN salary BETWEEN 6000 AND 7000 THEN 'Medium'
           ELSE 'High'
       END AS salary_range,
       COUNT(*) AS employee_count
FROM employees
GROUP BY salary_range;

Result:

salary_range	employee_count
Low	1
Medium	2

Explanation: We used a CASE statement to group employees based on their salary ranges, and we aliased the CASE result as salary_range. The result is then grouped by this alias.

6. Column Aliases Without `AS` Keyword

In PostgreSQL, the a AS keyword is optional when defining an alias. You can directly use a space to create an alias.

Query: Using Alias Without `AS`

SELECT first_name || ' ' || last_name full_name
FROM employees;

Result:

full_name
Alice Smith
Bob Johnson
Charlie Brown

Explanation: The concatenation of first_name and last_name is aliased as full_name, but we skipped the AS keyword.

7. Best Practices for Column Aliases

Clarity: Use aliases to make the column names in the result set more readable or more meaningful.
Avoid Ambiguity: Ensure that your alias names are descriptive and avoid confusion with existing column names or SQL keywords.
Consistency: Follow a consistent naming convention, especially when working with calculated fields or expressions.

8. Summary of Column Aliases

Operation	Description
Create Alias	Use `AS` (or space) to rename a column in the result set.
Use Case	Improves readability and simplifies complex expressions.
Example	`SELECT salary * 0.10 AS bonus_amount FROM employees;`

Would you like more examples or a deeper dive into column aliases in PostgreSQL? 🚀

Souy Soeng

PostgreSQL Column Alias