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:
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.
In the above syntaxes, we have the following parameters:
| Parameters | Description |
|---|---|
| table_name | The table name parameter is used to define the original name, where we want to perform alias. |
| column_name | The column name is used to define the column name for a particular table. |
| alias_name | The alias name is used to describe the temporary name, which is given to the column. |
| AS | The AS is an optional keyword used by most developers while aliasing a column name, but not used when performing the table aliasing. |
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:
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:
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.
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.
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
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,
We can give the table long_table_name an alias like below:
And it refers to the column_name in the table Long_table_name with the help of the table alias:
- 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:
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.