SUMMARY: This article explains how to use table and column aliases in PostgreSQL to create more meaningful queries.
1. Column alias
2. Table alias
3. Examples
In PostgreSQL, an alias is a temporary alternative name for columns, tables, views, materialized views, etc. in a query. Aliases are assigned during query execution and aren't stored in the database or on disk. By using column aliases, the query output can become more meaningful. A table alias is helpful for user convenience and ease of use for complex queries.
The keyword AS can be used between the column or table name and the alias name, but this is optional. Also, we can use reserved keywords as a column alias, but not for tables. If we want the reserved keywords as alias then we need to keep those reserved words within double quotes.
A list of supported keywords can be found at the PostgreSQL documentation: https://www.postgresql.org/docs/current/sql-keywords-appendix.html.
Below are the various ways of using an alias.
Column Alias
- Column aliases can be used in the SELECT list of a SQL query in PostgreSQL.
- Like all objects, aliases will be in lowercase by default. If mixed-case letters or special symbols, or spaces are required, quotes must be used.
- Column aliases can be used for derived columns.
- Column aliases can be used with GROUP BY and ORDER BY clauses.
- We cannot use a column alias with WHERE and HAVING clauses.
Table Alias
- Table aliases can be used in SELECT lists and in the FROM clause to show the complete record or selective columns from a table.
- Table aliases can be used in WHERE, GROUP BY, HAVING, and ORDER BY clauses.
- When we need data from multiple tables, we need to join those tables by qualifying the columns using table name/table alias.
- The aliases are mandatory for inline queries (queries embedded within another statement) as a data source to qualify the columns in a select list.
Examples
We are going to use the dummy tables “employees” and “department” and some random data for demonstration.
CREATE TABLE employees (empno INT, ename TEXT, job TEXT, mgr INT, sal INT, comm INT, deptno INT);
INSERT INTO employees VALUES (7788, 'SCOTT', 'ANALYST',NULL, 3000, NULL, 20);
INSERT INTO employees VALUES (7369, 'SMITH', 'CLERK', 7788, 800, NULL, 20);
INSERT INTO employees VALUES (7499, 'ALLEN', 'SALESMAN', 7788, 1600, 300, 10);
CREATE TABLE department (DEPTNO INT, DNAME TEXT, LOC TEXT );
INSERT INTO department VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO department VALUES (20, 'RESEARCH', 'DALLAS');
Column Alias
- Column alias in the SELECT list. The AS keyword is optional.
postgres=# SELECT ename enm, empno AS eid
FROM employees;
enm | eid
-------+------
SCOTT | 7788
SMITH | 7369
ALLEN | 7499
(3 rows)
- Column alias in quotes when mixed-case letters are required.
- Column alias for derived columns.
postgres=# SELECT ename ||'_'|| empno AS "EmpDetails", sal+COALESCE(comm,0) AS TOTALSAL
FROM employees;
EmpDetails | totalsal
------------+----------
SCOTT_7788 | 3000
SMITH_7369 | 800
ALLEN_7499 | 1900
(3 rows)
Here, “totalsal” is the derived column name of sal+COALESCE(comm,0). “EmpDetails” is displayed in camel case because it was provided within a double quotes (""), whereas “totalsal,” despite using uppercase in the query, is displayed in lowercase, because the default casing for an alias is in lowercase.
- Column alias with GROUP BY and ORDER BY clauses.
postgres=# SELECT deptno AS "DeptID", SUM(sal) AS "DeptSal"
FROM employees
GROUP BY "DeptID";
DeptID | DeptSal
--------+---------
10 | 1600
20 | 3800
(2 rows)
postgres=# SELECT ename "ENM"
FROM employees
ORDER BY "ENM" DESC;
ENM
-------
SMITH
SCOTT
ALLEN
(3 rows)
- Column alias is not supported in WHERE or HAVING clauses.
postgres=# SELECT emp.ename as "Enm", emp.empno as "EmpID", emp.sal as "ESal"
FROM employees emp
WHERE "ESal" <=2000;
ERROR: column "ESal" does not exist
LINE 3: WHERE "ESal" <=2000;
^
HINT: Perhaps you meant to reference the column "emp.sal".
postgres=# SELECT emp.deptno as "DeptID", SUM(emp.sal) as "DeptSal"
FROM employees emp
GROUP BY emp.deptno
HAVING "DeptID" = 20;
ERROR: column "DeptID" does not exist
LINE 4: HAVING "DeptID" = 20;
^
HINT: Perhaps you meant to reference the column "emp.deptno".
Table Alias
- Table alias in the SELECT list and in the FROM clause to display RECORD type data. The optional AS keyword is not used.
postgres=# SELECT emp
FROM employees emp;
emp
----------------------------------------
(7788,SCOTT,ANALYST,,3000,,20)
(7369,SMITH,CLERK,7788,800,,20)
(7499,ALLEN,SALESMAN,7788,1600,300,10)
(3 rows)
- Table alias with an asterisk (*) in the SELECT list to select a complete record.
postgres=# SELECT emp.*
FROM employees emp;
empno | ename | job | mgr | sal | comm | deptno
-------+-------+----------+------+------+------+--------
7788 | SCOTT | ANALYST | | 3000 | | 20
7369 | SMITH | CLERK | 7788 | 800 | | 20
7499 | ALLEN | SALESMAN | 7788 | 1600 | 300 | 10
(3 rows)
- Table alias with specific columns to select specific column values.
postgres=# SELECT emp.ename as "Enm", emp.empno as "EmpID"
FROM employees emp;
Enm | EmpID
-------+-------
SCOTT | 7788
SMITH | 7369
ALLEN | 7499
(3 rows)
- Table alias in the WHERE clause to compare the column values:
postgres=# SELECT emp.ename as "Enm", emp.empno as "EmpID"
FROM employees emp
WHERE emp.sal <=2000;
Enm | EmpID
-------+-------
SMITH | 7369
ALLEN | 7499
(2 rows)
- Table alias in GROUP BY, HAVING, and ORDER BY clauses:
postgres=# SELECT emp.deptno as "DeptID", SUM(emp.sal) as "DeptSal"
FROM employees emp
GROUP BY emp.deptno
HAVING emp.deptno = 20;
DeptID | DeptSal
--------+---------
20 | 3800
(1 row)
postgres=# SELECT emp.ename AS "ENM"
FROM employees emp
ORDER BY emp.ename DESC;
ENM
-------
SMITH
SCOTT
ALLEN
(3 rows)
- Joining multiple tables by qualifying columns with table alias.
postgres=# SELECT emp.ename as "EmpName", manager.ename as "MgrName"
FROM employees as emp LEFT JOIN employees as manager
ON emp.mgr = manager.empno;
EmpName | MgrName
---------+---------
SCOTT |
SMITH | SCOTT
ALLEN | SCOTT
(3 rows)
postgres=# SELECT emp.ename ||' works at '|| dept.loc ||' in '||dept.dname||' department as '||emp.job AS "Employee status."
FROM employees as emp JOIN department as dept
ON emp.deptno = dept.deptno;
Employee status.
--------------------------------------------------------------
ALLEN works at NEW YORK in ACCOUNTING department as SALESMAN
SMITH works at DALLAS in RESEARCH department as CLERK
SCOTT works at DALLAS in RESEARCH department as ANALYST
(3 rows)
- Required use of alias for inline queries as a data source to qualify the columns in a select list.
postgres=# SELECT emp.deptno, dept.dname
FROM employees emp JOIN (select * from department) dept
ON emp.deptno = dept.deptno;
deptno | dname
--------+------------
10 | ACCOUNTING
20 | RESEARCH
20 | RESEARCH
(3 rows)
Here the alias “dept” is mandatory for the inline query “(select * from department),” to qualify the “dept.dname” column in the SELECT list.
In the same way, we can use the table and column alias names in other DMLs like INSERT, UPDATE, and DELETE.
Popular Links
Featured Links
Relevant Blogs
EDB Tutorial: How to run a TPC-E-like benchmark easily
The TPC Benchmark(TM) E (TPC-E) was released back in 2007 as the successor to the TPC Benchmark(TM) C (TPC-C) for an OLTP type workload. To briefly recap, the TPC-E models...EDB Tutorial: How to Use Privilege Analysis to Identify [Un]Used Privileges
Have you ever searched for unnecessary granted privileges in your database? To find these, you need to know 1. The granted privileges 2. How many of the granted privileges have...EDB Tutorial: How To Get the Most Out of EDB Query Advisor
Enterprise-class databases require DBAs who are responsible for maximizing query performance. Therefore, when it comes to large scale deployment of databases, minimizing the work of DBA is important. One of...More Blogs
EDB Tutorial: How to Configure Databases for EDB JDBC SSL Factory Classes
The fact is, SSL configuration required by EDB JDBC SSL factory classes is considered confusing and challenging by many EDB customers. This stems from the concepts behind the SSL handshake...