How to use tables and column aliases when building a PostgreSQL query

link管理

链接快照平台

输入网页链接，自动生成快照
标签化管理网页链接

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 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)


   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;
-------
 SMITH
 SCOTT
 ALLEN
(3 rows)


   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;
----------------------------------------
 (7788,SCOTT,ANALYST,,3000,,20)
 (7369,SMITH,CLERK,7788,800,,20)
 (7499,ALLEN,SALESMAN,7788,1600,300,10)
(3 rows)

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)


   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)


   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.

Connecting PostgreSQL using psql and pgAdmin

How to use PostgreSQL with Django

10 Examples of PostgreSQL Stored Procedures

How to use PostgreSQL with Laravel

How to use tables and column aliases...

PostgreSQL vs. SQL Server (MSSQL)...

The Complete Oracle to PostgreSQL Migration...

PostgreSQL vs. MySQL: A 360-degree Comparison...

PostgreSQL Replication and Automatic Failover...

Postgres on Kubernetes or VMs: A Guide...