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.