In Postgres, an Alias is a temporary replacement name for a column, table, view, etc. Column Aliases in PostgreSQL are temporary alternative names assigned to columns. Since the aliases are temporary alternatives, so they exist temporarily during the query’s execution.

We will discuss the below-mentioned concepts of the Column Aliases through practical examples:

• What is a Column Alias, and How Does it Work in Postgres?
• Rules For Column Alias.
• Practical Demonstration of Column Alias.

So, let’s discuss all the concepts one by one.

What is a Column Alias, and How Does it Work in Postgres?

The concept of Aliases is used during the query execution to assign a temporary alternative name to any table, column, view, etc. Using the column alias, we can assign a temporary name to a column or expression within the SELECT statement.

Syntax

Column Alias has a pretty straightforward syntax, as shown in the below-given snippet:

SELECT col_name AS alias_name
FROM tab_name;

In the above syntax, alias_name is a column alias to be assigned to the col_name. The “AS” keyword is optional and can be skipped. The syntax of column alias without the “AS” keyword will be as follows:

SELECT col_name alias_name
FROM tab_name;

Rules For Column Alias

• Use column aliases in Postgres select lists.
• In PostgreSQL, a column alias can be used along with the ORDER BY, or GROUP BY clause; however, it can't be used with the WHERE or HAVING clause.
• By default, an alias will be in small letters. However, to specify special symbols, white spaces, mixed-case letters, etc., quotes must be used.

Practical Demonstration of Column Alias

Let’s consider a couple of practical examples to demonstrate the working of the column aliases in PostgreSQL. But first, we will create a sample table:

Creating Sample Table

Let’s create a table named emp_record with the following three columns: emp_id, emp_name, and emp_salary:

CREATE TABLE emp_record(
emp_id SERIAL PRIMARY KEY,
emp_name TEXT,
emp_salary INT);

Now, let’s insert some records into the emp_record table:

INSERT INTO emp_record(emp_name, emp_salary)
VALUES('JOE', 25000),
('JOHN', 35000),
('AMBROSE', 25000),
('ALEX', 45000),
('MIKE', 45000),
('SETH', 55000),
('DEAN', 50000),
('JONES', 50000),
('PAUL', 25000),
('KEVIN', 30000);

Ten records have been inserted into the emp_record table.

Example #1: How to Assign Column Alias to a Column in Postgres?

Let’s run the below query to fetch the data of the emp_record column:

SELECT * FROM emp_record;

We can utilize the column alias on any column of the above-given table to assign a temporary name to that column. For instance, we want to assign a new temporary name to the emp_name column. To do so, we will use the column alias as follows:

SELECT emp_name AS name,
emp_salary
FROM emp_record;

This is how the column alias can be assigned to a column in Postgres. You can skip the AS keyword from the column alias:

SELECT emp_name name,
emp_salary
FROM emp_record;

The output verified that skipping the AS keyword from the column alias doesn’t affect the performance/functionality of the column alias.

Example #2: How do I Assign Column Aliases to an Expression in PostgreSQL?

On successful execution, the Postgres queries return a result set. Within the result set, sometimes the columns contain a meaningless name. In such cases, we can use the column aliases to provide a meaningful name to those columns:

SELECT emp_name || ': ' || emp_salary 
FROM emp_record;

In this query, we utilized the concatenation operator “||” to concatenate the emp_name and emp_salary columns of the emp_record table:

From the resultant table, you can observe that the column’s name (i.e., ?column?) is meaningless. To make it meaningful, we will utilize the column alias as follows:

The output shows that a meaningful name has been assigned to the column using the column alias.

Conclusion

Column alias allows us to assign a temporary name to a column or expression within the SELECT statement. The concept of Aliases is used during the query execution to assign a temporary alternative name to any table, column, view, etc. Since the aliases are temporary alternatives, so they exist temporarily during the query’s execution. This post went through several practical examples to explain the working of the column aliases.