Introduction to PostgreSQL Alias

In simple terms, ALIAS means temporarily giving another name to a table or a column. To give the temporary name for tables or columns, we generally use PostgreSQL Alias. The PostgreSQL Aliases are used to create a temporary column or table name. The existence of aliasing is limited to the PostgreSQL statement’s execution means the PostgreSQL aliases are used to rename a column or a table in a specific PostgreSQL query. Hence the actual table name or column name does not change in the database. We generally use the temporary names while performing self join on the table to create a temporary table.

Syntax

We can use PostgreSQL Aliases to create a temporary name for columns, tables, or expressions. Let’s understand the syntax of Alias as below :

1. PostgreSQL Aliases for column

SELECT column [AS] alias_name
FROM table;

2. PostgreSQL Aliases for expression

SELECT expression [AS] alias_name
FROM table;

3. PostgreSQL Aliases for table

SELECT column
FROM table [AS] alias_name;

Explanation:

Column: The actual column name to which we want to specify an alias.
table_name: The actual table name to which we want to specify an alias.
expression: An expression to which we want to specify an alias.
AS: It is the Optional keyword. AS keyword will not affect the Alias in the PostgreSQL statement, even if it is defined or not. In PostgreSQL, defining AS keyword or not is a programmer’s choice.
alias_name: The temporary name for expressions, columns, or tables. The PostgreSQL Alias name can have spaces. But it is not a best practice to have an alias name with spaces in case of aliasing a table.

How does Alias Work in PostgreSQL?

The PostgreSQL Aliases are used to remove the ambiguity for self-joins. The self join means the same table is getting scanned multiple times to retrieve the data. The PostgreSQL Alias is used with the optional ‘alias’ keyword, but if provided, it hides the actual name of the columns or tables. If we have specified the Alias in the PostgreSQL statement, we need to define the column names, and the Alias defined, and its scope is limited to the same statement only.

Example:

Consider a statement ‘FROM MyTable AS MT’, which we are using with a SELECT statement; then it uses the ‘MT’ and not the ‘MyTable’.
Consider the following statements to understand how PostgreSQL works for long table names:

SELECT long_table_name.column
FROM long_table_name;

We can use the Alias for the long table name as follows:

SELECT ltn.column
FROM long_table_name ltn;

Here we have specified the alias ‘ltn’ for a table ‘long_table_name’.

Examples to Implement Alias in PostgreSQL

Let’s create a table of name ‘student’ and ‘teacher’ to understand the PostgreSQL alias examples in detail:

Example #1

Create a table of name ‘student.’

Code:

CREATE TABLE student(
rollno int PRIMARY KEY,
firstname VARCHAR (50) NOT NULL,
lastname VARCHAR (50) NOT NULL,
branch VARCHAR (50) NOT NULL,
result boolean,
joining_date DATE NOT NULL
);

Now, insert some data into thestudent’ table.

INSERT INTO student (rollno, firstname, lastname, branch, result, joining_date)
values
('101', 'Oliver','Jake', 'Civil', false, '06-01-2020'),
('102', 'Jack','Connor', 'Computer', false, '06-01-2020'),
('103', 'Harry','Callum', 'Civil', false, '06-01-2020'),
('104', 'Jacob','John', 'Computer', false, '06-01-2020'),
('105', 'Thomas','David', 'Civil', false, '06-01-2020');

Now, illustrate the data inserted into the ‘student’ table with the following SQL statement’s help.

select * from student;

Example #2

Create a table of name ‘teacher.’

Code:

CREATE TABLE teacher (
teacher_id INT NOT NULL PRIMARY KEY,
firstname VARCHAR (50) NOT NULL,
lastname VARCHAR (50) NOT NULL,
branch VARCHAR (50) NOT null,
salary numeric
);

Now, insert some data into the ‘teacher’ table.

INSERT INTO teacher (teacher_id, firstname, lastname, branch,salary)
values
('1', 'Hugo','Smith', 'Computer',20000),
('2', 'Brayden','Johnson', 'Computer',30000),
('3', 'Ronan','Williams', 'Civil',35000),
('4', 'Antonio','Brown', 'Civil',40000),
('5', 'Marco','Davis', 'Civil',25000);

Now, illustrate the data inserted into the ‘teacher’ table with the following SQL statement’s help.

select * from teacher;

1. PostgreSQL Aliases for column

We specify the alias names to make the column headers more readable in the final result set. Like whenever we use functions like MAX, we can alias the result of the MAX function to make it easier to read.

SELECT firstname, MAX(salary) AS high_salary
FROM teacher
GROUP BY firstname;