Use tables and column aliases for query

Contents

Preparing steps
Creating a table
Table alias
Column alias

In ADPG, an alias is a temporary alternative name in a query for columns and tables. Aliases are assigned during query execution and aren’t stored in the database or on disk. Using aliases means creating a virtual table only when executing a query. A new table isn’t created in this case.

This article describes the various ways how to use aliases.

Preparing steps

Get access to the ADPG server console by logging in with a trusted account.
Switch to the user postgres.
```
$ sudo su - postgres
```
Go to the ADPG interactive terminal.
```
$ psql
```

Creating a table

Create the ExamSheet table.

CREATE TABLE ExamSheet ( numb int, role text, name text, perm int, description text );

Result:

postgres=# CREATE TABLE ExamSheet ( numb int, role text, name text, perm int, description text );
CREATE TABLE

Add the data to the first row.

INSERT INTO ExamSheet VALUES (1, 'user', 'student01', 0, 'passed');

Result:

postgres=# INSERT INTO ExamSheet VALUES (1, 'user', 'student01', 0, 'passed');
INSERT 0 1

Add the data to the second row.

INSERT INTO ExamSheet VALUES (2, 'user', 'student02', 0, 'failed');

Result:

postgres=# INSERT INTO ExamSheet VALUES (2, 'user', 'student02', 0, 'failed');
INSERT 0 1

Add the data to the third row.

INSERT INTO ExamSheet VALUES (3, 'power user', 'teacher', 1, 'professor');

Result:

postgres=# INSERT INTO ExamSheet VALUES (3, 'power user', 'teacher', 1, 'professor');
INSERT 0 1

Output the data from the table.

SELECT * FROM ExamSheet;

Result:

postgres=# SELECT * FROM ExamSheet;
 numb |    role    |   name    | perm |        description
------+------------+-----------+------+----------------------------
    1 | user       | student01 |    0 | passed
    2 | user       | student02 |    0 | failed
    3 | power user | teacher   |    1 | professor
(3 rows)

Create the location table.

CREATE TABLE location ( numb int, name text, country text, telephone int );

Result:

postgres=# CREATE TABLE location ( numb int, name text, country text, telephone int );
CREATE TABLE

Add the data to the first row.

INSERT INTO location VALUES (1, 'student01', 'Austria', 43001);

Result:

postgres=# INSERT INTO location VALUES (1, 'student01', 'Austria', 43001);
INSERT 0 1

Add the data to the second row.

INSERT INTO location VALUES (2, 'student02', 'Norway', 470020);

Result:

postgres=# INSERT INTO location VALUES (2, 'student02', 'Norway', 470020);
INSERT 0 1

Add the data to the third row.

INSERT INTO location VALUES (3, 'teacher', 'Switzerland', 41003);

Result:

postgres=# INSERT INTO location VALUES (3, 'teacher', 'Switzerland', 41003);
INSERT 0 1

Output the data from the table.

SELECT * FROM location;

Result:

postgres=# SELECT * FROM location;
 numb |   name    |   country   | telephone
------+-----------+-------------+-----------
    1 | student01 | Austria     |     43001
    2 | student02 | Norway      |    470020
    3 | teacher   | Switzerland |     41003
(3 rows)

Table alias

Table alias is helpful for user convenience and ease of use for complex queries. You can use table aliases:

in SELECT and FROM queries to display a complete record from a table.
in the so-called conditions for queries: WHERE, GROUP BY, HAVING, and `ORDER BY'.
to get data from multiple tables.

Table aliases are required for embedded queries as a data source that defines columns in the selection list.

The keyword AS can be used between the table name and the alias name.

The common syntax for the table alias:

SELECT column1, column2....
FROM table_name AS alias_name
WHERE [condition];

Table alias with SELECT and FROM in the query to display the RECORD type data.

SELECT exam FROM ExamSheet exam;

Result:

postgres=# SELECT exam FROM ExamSheet exam;
                       exam
---------------------------------------------------
 (1,user,student01,0,"passed")
 (2,user,student02,0,"failed")
 (3,"power user",teacher,1,professor)
(3 rows)

Table alias with * symbol in the SELECT query to select a complete record.

SELECT exa.* FROM ExamSheet exa;

Result:

postgres=# SELECT exa.* FROM ExamSheet exa;
 numb |    role    |   name    | perm |        description
------+------------+-----------+------+----------------------------
    1 | user       | student01 |    0 | passed
    2 | user       | student02 |    0 | failed
    3 | power user | teacher   |    1 | professor
(3 rows)

Table alias with specific columns to select.

SELECT exam.role AS "Privilegies", exam.perm as "Permissions" FROM ExamSheet exam;

Result:

postgres=# SELECT exam.role AS "Privilegies", exam.perm as "Permissions" FROM ExamSheet exam;
 Privileges | Permissions
-------------+-------------
 user        |           0
 user        |           0
 power user  |           1
(3 rows)

Joining multiple tables by qualifying columns with table alias.

SELECT exam.numb, exam.name, loc.country, loc.telephone FROM examsheet AS exam, location AS loc WHERE exam.numb = loc.numb;

Result:

postgres=# SELECT exam.numb, exam.name, loc.country, loc.telephone FROM examsheet AS exam, location AS loc WHERE exam.numb = loc.numb;
 numb |   name    |   country   | telephone
------+-----------+-------------+-----------
    1 | student01 | Austria     |     43001
    2 | student02 | Norway      |    470020
    3 | teacher   | Switzerland |     41003
(3 rows)

Table alias with WHERE in the query to compare the column values:

SELECT exa.description AS "Status", exa.perm AS "Permissions", loc.name AS "Name" FROM ExamSheet exa, location loc WHERE perm = 1  AND exa.numb = loc.numb;

Result:

postgres=# SELECT exa.description AS "Status", exa.perm AS "Permissions", loc.name AS "Name" FROM ExamSheet exa, location loc WHERE perm = 1  AND exa.numb = loc.numb;
  Status   | Permissions |  Name
-----------+-------------+---------
 professor |           1 | teacher
(1 row)

As a result of the query, a new virtual table was created. This table contains columns from two different tables. Column names have been changed. The request contains an indication of the output of columns that meet the specified condition.

Table alias in GROUP BY and ORDER BY query. Reverse sorting will be applied in the query.

SELECT loc.name AS "Structure", loc.country, loc.telephone FROM location loc ORDER BY loc.telephone DESC;

Result:

postgres=# SELECT loc.name AS "Structure", loc.country, loc.telephone FROM location loc ORDER BY loc.telephone DESC;
 Structure |   country   | telephone
-----------+-------------+-----------
 student02 | Norway      |    470020
 student01 | Austria     |     43001
 teacher   | Switzerland |     41003
(3 rows)

Column alias

The column alias cannot be used in conjunction with the WHERE and HAVING operators. By default, only lowercase words are used for aliases. If the alias name must contain mixed-case letters, special characters, or spaces, you need to use the " quotation marks.

Reserved keywords are allowed for the column alias.

TIP

To use reserved keywords as aliases, you must use double quotes "".

Check supported keywords in the PostgreSQL documentation: SQL Key Words.

Column alias in the SELECT query.

SELECT telephone mob, country AS code FROM location;

Result:

postgres=# SELECT telephone mob, country AS code FROM location;
  mob   |    code
--------+-------------
  43001 | Austria
 470020 | Norway
  41003 | Switzerland
(3 rows)

Column alias in joining multiple tables including mixed-case letters.

SELECT country ||'_'|| telephone AS "LocationMobile", description ||'/'|| perm AS DescriptionPermissions FROM location, examsheet WHERE examsheet.numb = location.numb;

Result:

postgres=# SELECT country ||'_'|| telephone AS "LocationMobile",
description ||'/'|| perm AS DescriptionPermissions FROM location,
examsheet WHERE examsheet.numb = location.numb;
  LocationMobile   |    descriptionpermissions
-------------------+------------------------------
 Austria_43001     | passed/0
 Norway_470020     | failed/0
 Switzerland_41003 | professor/1
(3 rows)

As a result, several columns from different tables were joined into one table. As you can see from the result, the LocationMobile column contains upper and lower-case letters/symbols. The descriptionpermissions column doesn’t have uppercase symbols in the name, even though the alias uses a combination of uppercase and lowercase symbols in the query.

Column alias doesn’t support the WHERE and HAVING operators.

SELECT loc.telephone AS "Mobile", loc.numb AS "ID" FROM location loc WHERE "Mobile" >= 45000;

Result:

postgres=# SELECT loc.telephone AS "Mobile", loc.numb AS "ID" FROM location loc WHERE "Mobile" >= 45000;
ERROR:  column "Mobile" does not exist
LINE 1: ...Mobile", loc.numb AS "ID" FROM location loc WHERE "Mobile" >...

As result, a query failed with an error.

Found a mistake? Seleсt text and press Ctrl+Enter to report it