Use tables and column aliases in queries

Eugenia Kuzina

You can specify a temporary name for a table or a column. This name is called an alias. PostgreSQL assigns aliases during query execution and does not store them in a database or on a disk.

Create a table for examples:

CREATE TABLE employees (
employee_id serial PRIMARY KEY,
full_name VARCHAR NOT NULL,
hours INT,
rate  INT,
manager_id INT
);

Insert data to the table:

INSERT INTO employees (employee_id, full_name, hours, rate, manager_id)
    VALUES
    (1, 'James Wilson', 8, 500, NULL),
    (2, 'Mary Burton', 6, 450, 1),
    (3, 'Patricia Robinson', 7, 430, 1),
    (4, 'Robert Gray', 8, 480, 1),
    (5, 'Elizabeth Tucker', 7, 470, 2),
    (6, 'Joseph Lewis', 7, 470, 2),
    (7, 'William Ferguson', 7, 470, 2),
    (8, 'Linda Black', 6, 320, 3),
    (9, 'David Green', 6, 310, 3),
    (10, 'Daniel Gray', 5, 390, 5),
    (11, 'Mark Armstrong', 6, 320, 4),
    (12, 'Donald Carter', 9, 290, 7),
    (13, 'Elizabeth Collins', 8, 300, 7),
    (14, 'Paul Brown', 8, 280, 8),
    (15, 'Andrew Clarke', 8, 280, 8);

The result:

SELECT * FROM employees;

 employee_id |     full_name     | hours | rate | manager_id
-------------+-------------------+-------+------+------------
           1 | James Wilson      |     8 |  500 |
           2 | Mary Burton       |     6 |  450 |          1
           3 | Patricia Robinson |     7 |  430 |          1
           4 | Robert Gray       |     8 |  480 |          1
           5 | Elizabeth Tucker  |     7 |  470 |          2
           6 | Joseph Lewis      |     7 |  470 |          2
           7 | William Ferguson  |     7 |  470 |          2
           8 | Linda Black       |     6 |  320 |          3
           9 | David Green       |     6 |  310 |          3
          10 | Daniel Gray       |     5 |  390 |          5
          11 | Mark Armstrong    |     6 |  320 |          4
          12 | Donald Carter     |     9 |  290 |          7
          13 | Elizabeth Collins |     8 |  300 |          7
          14 | Paul Brown        |     8 |  280 |          8
          15 | Andrew Clarke     |     8 |  280 |          8

Table aliases

You can assign an alias to a table or complex table reference and use this alias in the current query. A table reference can be a derived table, for example, a subquery, JOIN construct, or complex combination of these options.

Use the following syntax to assign an alias:

FROM <table_reference> AS <alias>

or

FROM <table_reference> <alias>

Where:

  • table_reference is a table name or complex table reference;

  • alias is a unique identifier in the current query.

The AS keyword is optional.

Aliases are often used to assign short identifiers to long table names to improve the readability of queries. For example:

SELECT * FROM very_long_table_name1 a JOIN very_long_table_name2 b ON a.id = b.num_id;

The alias becomes a new table name in the current query. If the alias is assigned, the original table name cannot be used in the query. For example, the following query is invalid:

-- an incorrect query

SELECT * FROM table1 AS t WHERE table1.id > 276;

Although aliases are mostly used for convenience, they are necessary when a table is joined to itself, for example:

SELECT manager.employee_id, manager.full_name,
    subordinate.employee_id, subordinate.full_name, subordinate.manager_id
FROM employees AS manager
JOIN employees AS subordinate
ON manager.employee_id = subordinate.manager_id;

The result:

employee_id |     full_name     | employee_id |     full_name     | manager_id
------------+-------------------+-------------+-------------------+------------
          1 | James Wilson      |           2 | Mary Burton       |          1
          1 | James Wilson      |           3 | Patricia Robinson |          1
          1 | James Wilson      |           4 | Robert Gray       |          1
          2 | Mary Burton       |           5 | Elizabeth Tucker  |          2
          2 | Mary Burton       |           6 | Joseph Lewis      |          2
          2 | Mary Burton       |           7 | William Ferguson  |          2
          3 | Patricia Robinson |           8 | Linda Black       |          3
          3 | Patricia Robinson |           9 | David Green       |          3
          5 | Elizabeth Tucker  |          10 | Daniel Gray       |          5
          4 | Robert Gray       |          11 | Mark Armstrong    |          4
          7 | William Ferguson  |          12 | Donald Carter     |          7
          7 | William Ferguson  |          13 | Elizabeth Collins |          7
          8 | Linda Black       |          14 | Paul Brown        |          8
          8 | Linda Black       |          15 | Andrew Clarke     |          8

An alias is also required if the table reference is a subquery. See Subquery in the FROM clause for details.

You can use parentheses to resolve ambiguities. In the following example, the first statement assigns the staff alias to the second employees instance, but the second statement assigns the staff alias to the result of CROSS JOIN:

SELECT * FROM employees AS managers CROSS JOIN employees AS staff ...
SELECT * FROM (employees AS managers CROSS JOIN employees) AS staff ...

Also, aliases are not available outside parentheses. For example, the following query is valid:

SELECT a.* FROM table1 AS a JOIN table2 AS b ON a.id = b.num_id;

However, the a alias is not visible outside parentheses in the query below:

-- an incorrect query

SELECT a.* FROM (table1 AS a JOIN table2 AS b ON a.id = b.num_id) AS c;

Column aliases

A column alias allows you to assign a temporary name to a column or an expression in the SELECT statement list. The column alias exists only during the query execution.

Aliases usually consist of lowercase letters. If you need to use uppercase letters, special characters, spaces, or keywords, enclose an alias in double quotes ".

Use the following syntax to assign an alias:

SELECT <column_name> AS <alias> FROM <table>;

or

SELECT <column_name> <alias> FROM <table>;

Where:

  • column_name is a name of a column;

  • alias is a unique identifier for a column;

  • table is a table name.

The AS keyword is optional.

Example:

SELECT employee_id AS id, full_name AS "full name" FROM employees LIMIT 3;

The result:

 id |     full name
----+-------------------
  1 | James Wilson
  2 | Mary Burton
  3 | Patricia Robinson

Aliases are displayed in the column headers.

You can set an alias for an expression:

SELECT full_name "full name", hours*rate sum FROM employees LIMIT 3;

The result:

     full name     | sum
-------------------+------
 James Wilson      | 4000
 Mary Burton       | 2700
 Patricia Robinson | 3010

Alternatively, you can use the following syntax to assign aliases to columns and a table simultaneously:

FROM <table_reference> [AS] <alias> ( <column1_alias> [, <column2_alias> [, ...]] )

Where:

  • table_reference is a table name or complex table reference;

  • alias is a table alias;

  • column1..n_alias are column aliases.

If you specify fewer column aliases than the number of columns in the table, the remaining columns retain their original names.

Example:

SELECT * FROM employees AS managers ( id, "full name") LIMIT 3;

The result:

 id |     full name     | hours | rate | manager_id
----+-------------------+-------+------+------------
  1 | James Wilson      |     8 |  500 |
  2 | Mary Burton       |     6 |  450 |          1
  3 | Patricia Robinson |     7 |  430 |          1
Found a mistake? Seleсt text and press Ctrl+Enter to report it