PostgreSQL - ALIAS Syntax

PostgreSQL - Using Aliases

Aliases are used to give a table or a column a temporary, alternative name within a specific query. This is incredibly useful for improving the readability of complex queries and for renaming columns in the final result-set.

Column Aliases

Column aliases are used to provide a more descriptive name for a column in the output. The AS keyword is used to define the alias.

The syntax is:

SELECT column_name AS alias_name
FROM table_name;

If your alias contains spaces or is a reserved keyword, you must enclose it in double quotes.

SELECT annual_salary / 12 AS "Monthly Salary"
FROM employees;

Table Aliases and Modern JOIN Syntax

Table aliases are essential when performing a JOIN on multiple tables, especially when tables have columns with the same name. They create a shorthand name for the table, making the query shorter and more readable.

The modern, recommended syntax uses the explicit JOIN keyword, which is clearer and less error-prone than the old-style comma-separated FROM clause.

SELECT ...
FROM table1 AS t1
JOIN table2 AS t2 ON t1.common_column = t2.common_column;

Note: The AS keyword is optional for table aliases in PostgreSQL, but it’s a good practice to include it for clarity.

Example: Combining Table and Column Aliases

Let’s consider two tables: employees and departments.

employees table:

 id | name  | age | dept_id | salary
----+-------+-----+---------+--------
  1 | Alice |  32 |       1 |  90000
  2 | Bob   |  25 |       2 |  65000
  3 | Charlie| 28 |       1 |  85000

departments table:

 id | dept_name   | location
----+-------------+----------
  1 | Engineering | Building A
  2 | Marketing   | Building B

Now, let’s write a query to list each employee’s name, age, and department name. We will use table aliases (e for employees, d for departments) and column aliases for the output.

SELECT
    e.name AS employee_name,
    e.age,
    d.dept_name AS department
FROM
    employees AS e
JOIN
    departments AS d ON e.dept_id = d.id;

This is much clearer than the old syntax (FROM employees e, departments d WHERE e.dept_id = d.id) because it separates the join condition (ON ...) from filtering conditions (which would go in a WHERE clause).

The result of the query would be:

 employee_name | age | department
---------------+-----+-------------
 Alice         |  32 | Engineering
 Bob           |  25 | Marketing
 Charlie       |  28 | Engineering
(3 rows)

Why Use Explicit JOIN Syntax?

• Readability: It clearly states the intent to join tables and separates the relationship logic (ON) from the data filtering logic (WHERE).
• Maintainability: It’s easier to add, remove, or change joins without accidentally creating a Cartesian product (cross join), which is a common mistake with the old comma-based syntax.
• Power: It’s the standard way to perform more complex OUTER JOINs (LEFT, RIGHT, FULL), which are difficult or impossible with the old syntax.