PostgreSQL Alias

PostgreSQL Alias can be used to create a temporary name for tables or columns. Column aliases are used to make it easier to read column headers in a set of results.

Table aliases are used to shorten your SQL code to make it easier to read or when you connect yourself (i.e. enumerate the same table more than once in the FROM statement).

Column alias syntax in PostgreSQL

column_name [ AS ] alias_name

or:

The syntax for table alias in PostgreSQL

table_name [ AS ] alias_name

Parameters or alias arguments

• column_name – the original name of the column to which you want to define an alias.
• table_name – the original name of the table you want to define an alias for.
• AS – Optional. Most programmers will specify AS when determining the column name alias, but not when determining the table name alias. Whether you specify an AS or not does affect an alias in PostgreSQL. This is a personal choice in PostgreSQL, in contrast to other databases. (Our examples will use AS when you specify a column name alias, but omit AS when you specify a table name alias).
• alias_name – A temporary name that can be given to a column or table.

Note:

• If alias_name contains spaces, you must enclose alias_name in quotes.
• It is acceptable to use spaces when creating a column name alias. However, it is usually not recommended to use spaces when creating a table name alias.
• alias_name is only allowed within an SQL statement.

Alias column

Usually, aliases are used to facilitate the reading of column headers in the resulting set. For example, when using the function MAX, you can use the result alias MAX.
For example, you can use an alias of the result of a function MAX:

SELECT sales_person, MAX(commission) AS highest
FROM sales
GROUP BY sales_person;

In this example, we called the MAX(commission) field as high. The result is that the highest will be displayed as a header for the second column when returning the result set. Since there were no spaces in our alias_name, we do not have to quote alias_name.

It would however be quite acceptable to write this example using quotes as follows:

SELECT sales_person, MAX(commission) AS "highest"
FROM sales
GROUP BY sales_person;

Next, let’s look at an example where we need to enclose alias_name in quotes.
For example:

SELECT sales_person, MAX(commission) AS "high commission"
FROM sales
GROUP BY sales_person;

In this example, we called the MAX(commission) field as “high commission”. Since there are spaces in this alias_name, “high commission” must be enclosed in quotes.

Alias table

When you create an alias for a table, this happens either because you plan to specify the same table name more than once in the FROM statement (i.e. Self join), or you want to shorten the table name to make the SQL statement shorter and more readable.

Let’s take a look at an example of how to create a table alias.
For example:

SELECT p.product_id, p.product_name, inventory.quantity
FROM products p
INNER JOIN inventory
ON p.product_id = inventory.product_id
ORDER BY p.product_name ASC, inventory.quantity DESC;

In this example, we created an alias for the products table called p. Within this SQL statement, we can now refer to the products table as p.

When creating table aliases, there is no need to create aliases for all tables listed in the FROM statement. You can create aliases for any or all tables in the following way.

For example, we could change our example above and create an alias for the table.

SELECT p.product_id, p.product_name, i.quantity
FROM products p
INNER JOIN inventory i
ON p.product_id = i.product_id
ORDER BY p.product_name ASC, i.quantity DESC;

We now have an alias for the inventory table with the name i, and an alias for the products table with the name p.

Tutorial – Alias In PostgreSQL