PostgreSQL Alias

In this section, we are going to understand the working of PostgreSQL Aliasing, and PostgreSQL Table aliasing, which is used to give a temporary name to the table in the particular command.

What is PostgreSQL Alias?

PostgreSQL alias is used to give a short name to a table or an expression in the select list of a SELECT command in a particular statement.

Advantages of PostgreSQL Aliases

The advantages of PostgreSQL aliasing are given below:

• The PostgreSQL aliasing makes the column or table name more understandable.
• It is preferred in the case when more than one table is going to use in a query.
• It provides a handy and flexible feature that allows us to achieve complex tasks quickly.
• The PostgreSQL aliasing is useful when we use the function in the statement.
• In PostgreSQL Aliasing, we can combine two or more columns.
• When the column names are significant or not readable, the PostgreSQL aliasing is very helpful.

A PostgreSQL alias can be defined in two ways:

• PostgreSQL table alias
• PostgreSQL column alias

In this section, we will understand the PostgreSQL table Aliasing and some examples of it.

What is PostgreSQL Table Alias?

The Table aliasing is used to abbreviate our command to make it easier for reading or when we are implementing a Self-Join, it is listing a similar table more than once in the FROM clause.

The table aliasing works as a nickname for expressing the table names, which make the table name more readable and shorter.

It exists momentarily throughout the implementation of the command. The table aliasing is very useful when the table name is not user-friendly in real-time.

Syntax of PostgreSQL Table Alias

The syntax of the PostgreSQL table alias is given below:

SELECT column1, column2....,columnN
FROM table_name AS alias_name
WHERE [condition];

OR

In the below syntax, we ignore the AS keyword because it is optional, and the table _name is given to an alias alias_name.

SELECT column_name
FROM table_name alias_name;
WHERE [condition];

In the above syntaxes, we have the following parameters:

Examples of PostgreSQL Table aliasing

Let see some examples for our better understanding of PostgreSQL Table aliasing.

• Performing table aliases in join clauses

Generally, we use a join clause to get records from one or more tables, which contains a similar column name.

We will get an error if we use a similar column name, which comes from one or more tables without fully qualifying them.

To omit this error, we need to qualify these columns with the help of below syntax:

table_name.column_name

The table aliases were used for one or more tables and linked them with the JOIN clause's help. Here, we are using the table aliases for the table names specified in the FROM clause, and the INNER JOIN clauses that make the command more readable.

In the below example, we have a table named Employee, which contains the below data:

This statement will return the records using table aliases:

SELECT employee_details.emp_id, employee_details.emp_fname, 
employee_details.emp_lname
FROM employee AS employee_details; 

Output

On executing the above command, we will get the following result:

Let us assume that our database has one more table named department, which has the following data as we can see in the below screenshot:

As we can observe that both the tables contain one similar column emp_fname.

SELECT e.emp_id, emp_fname, emp_lname,location,dept_name  
FROM Employee  e  
INNER JOIN department  d   
ON e.emp_id= d.dept_id;

Output

On executing the above command, we will get the following output, which displays the below error: The column 'emp_fname' in one clause is ambiguous if we use it without table aliases.

Therefore, if we want to avoid the above error, we will use table aliases.

In the below command, we will use the PostgreSQL Inner Join clause to combine the Employee and Department table; and perform the Table aliasing on it.

SELECT e.emp_id, e.emp_fname, emp_lname,
location, dept_name  
FROM Employee  e  
INNER JOIN department  d   
ON e.emp_id= d.dept_id
ORDER BY location desc;    

Output

After executing the above command, we will get the following output:

And if we do not want to use aliases concept with a command, then PostgreSQL uses the table name to find the column name, which makes the statement lengthier and less readable, as we can see in the following example

SELECT emp_fname, emp_lname,location 
FROM employee    
INNER JOIN employee_details 
ON employee.emp_id= employee_details.emp_id;

Output

After executing the above statement, we will get the below output:

• Example of performing the table aliasing for the lengthy table name

To make our commands more readable and save some typing time to write a lengthy table name, we can use the table aliasing.

For example, Instead of using the below expression in a command,

Long_table_name.column_name

We can give the table long_table_name an alias like below:

Long_table_name AS alias

And it refers to the column_name in the table Long_table_name with the help of the table alias:

alias.column_name

• Performing table aliases in PostgreSQL self-join clause

Suppose we want to use table aliases in the Self-join clause. And the self-join clause is used to combine a table to itself, which is referencing a similar table several times within a statement.

In the below example, we will display how to reference the Customer table twice in a similar command with the help of the table aliases:

SELECT
c.first_name Customer,
o.first_name Orders
FROM Customer c
INNER JOIN Customer o ON o.order_id = c.order_id
ORDER BY Orders;

Output

After implementing the above command, we will get the following results:

Overview

In the PostgreSQL table Alias section, we have learned the following topics:

• We will use to provide a PostgreSQL table aliases to give a new name to tables temporarily at the time of execution for a command.
• We will implement the table aliasing in the PostgreSQL Join clauses.