PostgreSQL Column Alias (With Examples)

Sometimes you need to display a different name for the column of a table than the original one. In this case, you can use the alias for that column. In this tutorial, we will learn how we can use the column aliases in PostgreSQL. So, let’s get started!

Introduction to Column Alias in PostgreSQL

A column alias is a temporary name assigned to the column or an expression in the select statement.

You might be thinking, why do I need an alias for the column when there is already a meaningful name present? You are absolutely correct, however, there might be chances that you need to perform some operations on table records before fetching them, which changes the column name.

For example, let’s say you have two columns in the table: firstName and lastName. Now, you need to concatenate the first name and the last name and display it in a single column. In this case, PostgreSQL will assign the new column name as ‘?column?’ which doesn’t make any sense, right?

You need to give some meaningful name for the newly generated column such as Full Name or anything else. In this case, we use the alias for the new column which is generated by concatenating two columns.

Now let’s see syntax and examples of column alias in PostgreSQL.

Using Column Alias in PostgreSQL

The basic syntax of assigning the alias to the column is as below:

SELECT colName AS aliasName
FROM tableName;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here, the AS keyword is optional, hence you can omit you. Therefore, the below query is exactly the same as the above.

SELECT colName aliasName
FROM tableName;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

You can assign aliases to as many as columns you want. Aliases can be duplicated and one column can have a maximum of one alias name.

Note that, the aliases assigned to the column are case-insensitive by default. Write the alias name inside the double quotes to make it case-sensitive.

To assign a space-separated alias to the column, mention the alias name inside the double quotes, exactly the same as the case-sensitive thing. Look below:

SELECT colName "Alias Name"
FROM tableName;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Examples of Column Alias in PostgreSQL

We already have a ‘students’ table with some data in it as shown below.

Check: How to Create a Table in PostgreSQL?

Simple Column Alias

In the above table, we will use the column alias for the last_name column.

SELECT first_name, last_name AS surname 
FROM students;

SELECT first_name, last_name surname 
FROM students;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here, even if you don’t use the AS keyword, the result will be the same.

As you can see, both queries produce the same result.

Column Alias For Expression

Let’s now concatenate the first name and last name of students and assign the alias name to the new result. But first, let’s see what happens if we do not use the alias for the concatenated column.

SELECT first_name || ' ' || last_name, age, class  
FROM students;

SELECT first_name || ' ' || last_name AS full_name,age,class
FROM students;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

As you can see, the first query generates the same output but the column header becomes ‘?column?’ which doesn’t make any sense. Whereas, the second query assigns a meaningful name to the column header.

Column Alias For Case-Sensitive Names And Space-Contained Names

Let’s say you want to display the header with the first letter capital. If you use the alias just like the previous examples, you will end up getting the header name in all lowercase letters.

In this case, you have to mention the alias name inside the double quotes. Let’s see by writing both cases.

SELECT first_name AS FirstName, last_name AS LastName
FROM students;

SELECT first_name AS "FirstName", last_name AS "LastName"
FROM students;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here, we have mentioned the aliases in upper camel case format in both queries. The first query ignores the case and turns everything into lowercase. The second query prints the aliases as they are mentioned because we have written them inside the double quotes.

The same thing goes with the space-separated aliases. If you want to display the space-separated aliases for the columns, write them inside the double quotes as shown below:

SELECT first_name AS "First Name", last_name AS "Last Name"
FROM students;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

As you can see, the space-separated name is perfectly assigned to the column header.

Conclusion

It is one of the simplest and most useful things in the database world to use aliases. However, due to some negligence or hurry, mistakes happen and you get stuck thinking everything is perfect, what’s wrong here!! There might be chances you forget the double quotes, or use the wrong casing when retrieving the column values from the programming languages such as Java, PHP, GO etc. Remember these small things!!

Reference

https://www.postgresql.org/docs/7.3/queries-table-expressions.html