WikiGalaxy
PostgreSQL Column Alias
Understanding Column Aliases
In PostgreSQL, a column alias is a temporary name given to a column or expression in a query. It is often used to make column names more readable or to rename a column for the duration of a query.
Basic Syntax
The basic syntax for creating a column alias is as follows: SELECT column_name AS alias_name FROM table_name;
Example with Single Column
This example demonstrates how to use a column alias with a single column.
SELECT first_name AS "First Name" FROM employees;
Using Aliases in Calculations
Column aliases can also be used in calculations to provide meaningful names to the resultant columns.
SELECT salary, salary * 0.1 AS "Bonus" FROM employees;
Aliases with Functions
When using functions in your SELECT statement, aliases can help clarify the output.
SELECT COUNT(*) AS "Total Employees" FROM employees;
Combining Aliases with Joins
Aliases can be particularly useful when working with joins, helping to distinguish between columns from different tables.
SELECT e.first_name AS "Employee Name", d.name AS "Department"
FROM employees e
JOIN departments d ON e.department_id = d.id;
Aliases in Subqueries
Using aliases in subqueries can simplify complex queries and make them more understandable.
SELECT subquery.avg_salary AS "Average Salary"
FROM (SELECT AVG(salary) AS avg_salary FROM employees) AS subquery;
Aliases with Aggregate Functions
Aliases are useful when using aggregate functions in your queries, providing a clear label for the output.
SELECT department_id, SUM(salary) AS "Total Salary"
FROM employees
GROUP BY department_id;
Complex Expressions with Aliases
For complex expressions, aliases simplify the result set by providing a clear and concise column name.
SELECT first_name || ' ' || last_name AS "Full Name"
FROM employees;
Aliases in ORDER BY Clause
You can use aliases in the ORDER BY clause to sort the results based on the alias name.
SELECT first_name AS "Name", salary AS "Salary"
FROM employees
ORDER BY "Salary" DESC;
Aliases in GROUP BY Clause
While aliases are typically not used in the GROUP BY clause, they can be referenced in the SELECT statement to provide clarity.
SELECT department_id AS "Dept ID", COUNT(*) AS "Employee Count"
FROM employees
GROUP BY department_id;
