Postgres Alias
Introduction
When you’re working with data in PostgreSQL, you may find that the given names of columns and tables don’t always lend themselves to clear, readable results. Using a Postgres alias, or temporary name, for a column or table in your query can make your results more meaningful and easier to understand. In this article, we’ll take a closer look at the use of aliases in PostgreSQL and provide some examples to illustrate their use.
Prerequisite
You’ll need to have PostgreSQL installed and configured in order to test out the examples in this tutorial. If you haven’t already installed Postgres, you can download it here.
It’s also helpful to have some basic PostgreSQL knowledge before attempting to follow along with the article.
What is a Postgres Alias?
As we mentioned earlier, a Postgres alias is a temporary name given to a table, column or materialized view. We only use aliases during the execution of queries– aliases are not saved in the database or on disk. Aliases can help provide meaningful query results that are easier to read and understand.
Let’s look at the basic syntax used for an alias:
Column Alias
1 2 | SELECT <column_name> AS alias_name FROM TABLE; |
In the above syntax, we use the optional clause AS to assign an alias name for the target column ‘column_name’.
A few common uses of column aliases include:
- They can be used with the ORDER BY and GROUP BY clauses.
- They can be used with derived columns.
- They can be used in the SELECT list of a SQL query.
Keep in mind that aliases should be written in all lowercase letters. If you need to include capital letters or special symbols in your alias, the name must be enclosed in quotes.
Table Alias
1 2 3 4 | SELECT list_of_column FROM <table_name> AS alias_name; |
The above syntax for a table alias is similar to the syntax for a column alias with its use of the optional clause ‘AS’.
Some common uses of table aliases include:
- They can be used to qualify columns in a query that gathers data from multiple tables.
- They’re necessary to qualify columns in a SELECT list for queries that include subqueries.
- They can be used in WHERE, GROUP BY and ORDER BY clauses.
Creating Sample Dataset
In this section, we’ll create a sample dataset that we can use for demo purposes in this article.
First, we need to log in to the Postgres shell.
- We can create a database using the command shown below:
1 | create database employeedb |
- We can then create tables for the database with the following SQL statement:
1 2 3 4 5 6 7 8 | CREATE TABLE IF NOT EXISTS employee ( id SERIAL PRIMARY KEY, emp_id CHARACTER VARYING(100), emp_name CHARACTER VARYING(100), emp_age INTEGER, emp_gender CHARACTER VARYING(100), emp_department CHARACTER VARYING(100) ); |
Once we have our table ready, we’ll insert our sample records:
1 2 3 4 5 6 7 | INSERT INTO employee (id,emp_id, emp_name, emp_age, emp_gender,emp_department) VALUES (1,'EMP211','James Stacey',24, 'male', 'ict'), (2,'EMP212','William Rogers',25, 'male', 'ict'), (3,'EMP213','Rose Stanley',24, 'female', 'marketing'), (4,'EMP214','Bernard Dimsey',22, 'male', 'training'), (5,'EMP215','John McNeil',21, 'male', 'marketing'); |
Our employee table should look something like this:
1 2 3 4 5 6 7 8 | id | emp_id | emp_name | emp_age | emp_gender | emp_department ----+--------+----------------+---------+------------+---------------- 1 | MN023 | Rod Hendricks | 43 | male | finance 2 | MN069 | Willie Tulliao | 33 | male | training 3 | MN073 | Kelvin Victa | 32 | male | ict 4 | MN045 | Raymond Forma | 36 | male | training 5 | MN011 | Romulo Despi | 40 | male | ict (5 rows) |
Column Alias Example
In this section, we’ll show you how to use aliases for columns. Here’s an example of a query that contains a column alias:
1 2 | SELECT emp_id, emp_name AS employee_name FROM employee; |
The syntax shown above will assign an alias called ’employee_name’ to the column ’emp_name’.
The output should look like the following:
1 2 3 4 5 6 7 8 | emp_id | employee_name --------+---------------- MN023 | Rod Hendricks MN069 | Willie Tulliao MN073 | Kelvin Victa MN045 | Raymond Forma MN011 | Romulo Despi (5 rows) |
Table Alias Example
Next, we’ll show you how to use a table alias in a query:
1 2 | SELECT emp_name AS "Employee Name", emp_id AS "Employee ID" FROM employee emp; |
The output should look like this:
1 2 3 4 5 6 7 8 | Employee Name | Employee ID ----------------+------------- Rod Hendricks | MN023 Willie Tulliao | MN069 Kelvin Victa | MN073 Raymond Forma | MN045 Romulo Despi | MN011 (5 rows) |
Conclusion
When you’re querying for data in PostgreSQL, it’s important that your results are returned in a format that’s easy to read and understand. You can help make your results clearer and more readable by assigning aliases to columns and tables in your query. In this article, we explained how to use a Postgres alias and walked through examples of both column aliases and table aliases. If you’ve been following along with this tutorial, you’ll be ready to make use of aliases in your own PostgreSQL queries.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started