PostgreSQL Table Aliases

Summary: in this tutorial, you will learn about the PostgreSQL table aliases and their pactical applications.

Introduction to the PostgreSQL table aliases

Table aliases temporarily assign tables new names during the execution of a query.

The following illustrates the syntax of a table alias:

table_name AS alias_name;
Code language: SQL (Structured Query Language) (sql)

In this syntax, the table_name is assigned an alias as alias_name. Similar to column aliases, the AS keyword is optional. It means that you omit the AS keyword like this:

table_name AS alias_name;
Code language: PHP (php)

Practical applications of table aliases

Table aliases have several practical applications.

1) Using table aliases for the long table name to make queries more readable

If you must qualify a column name with a long table name, you can use a table alias to save some typing and make your query more readable.

For example, instead of using the following expression in a query:

a_very_long_table_name.column_name
Code language: CSS (css)

you can assign the table a_very_long_table_name an alias like this:

a_very_long_table_name AS alias
Code language: PHP (php)

And reference the column_name in the table a_very_long_table_name using the table alias:

alias.column_name
Code language: CSS (css)

2) Using table aliases in join clauses

Typically, you often use a join clause to query data from multiple tables that have the same column name.

If you use the same column name that comes from multiple tables without fully qualifying them, you will get an error.

To avoid this error, you need to qualify these columns using the following syntax:

table_name.column_name
Code language: CSS (css)

To make the query shorter, you can use the table aliases for the table names listed on FROM and INNER JOIN clauses. For example:

SELECT c.customer_id, first_name, amount, payment_date FROM customer c INNER JOIN payment p ON p.customer_id = c.customer_id ORDER BY payment_date DESC;
Code language: SQL (Structured Query Language) (sql)

3) Using table aliases in self-join

When you join a table to itself (a.k.a self-join), you need to use table aliases. This is because referencing the same table multiple times within a query results in an error.

The following example shows how to reference the employee table twice in the same query using the table aliases:

SELECT e.first_name employee, m .first_name manager FROM employee e INNER JOIN employee m ON m.employee_id = e.manager_id ORDER BY manager;
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use PostgreSQL table aliases to temporarily assign new names to tables during the execution of a query.

Was this tutorial helpful ?