PostgreSQL Reference Manual - Volume 1 - SQL Language Reference by The PostgreSQL Global Development Group Paperback (6"x9"), 716 pages ISBN 0954612027 RRP £32.00 ($49.95) Sales of this book support the PostgreSQL project! Get a printed copy>>>

PostgreSQL Reference Manual Volume 1 Buy the book here! >>> learn more

5.2.1.2 Table and Column Aliases

A temporary name can be given to tables and complex table references to be used for references to the derived table in the rest of the query. This is called a table alias.

To create a table alias, write

FROM table_reference AS alias

or

FROM table_reference alias

The AS key word is noise. alias can be any identifier.

A typical application of table aliases is to assign short identifiers to long table names to keep the join clauses readable. For example:

SELECT * FROM some_very_long_table_name s JOIN
 another_fairly_long_name a ON s.id = a.num;

The alias becomes the new name of the table reference for the current query--it is no longer possible to refer to the table by the original name. Thus

SELECT * FROM my_table AS m WHERE my_table.a > 5;

is not valid according to the SQL standard. In PostgreSQL this will draw an error if the add_missing_from configuration variable is off (as it is by default). If it is on, an implicit table reference will be added to the FROM clause, so the query is processed as if it were written as

SELECT * FROM my_table AS m, my_table AS my_table WHERE
 my_table.a > 5;

That will result in a cross join, which is usually not what you want.

Table aliases are mainly for notational convenience, but it is necessary to use them when joining a table to itself, e.g.,

SELECT * FROM people AS mother JOIN people AS child ON
 mother.id = child.mother_id;

Additionally, an alias is required if the table reference is a subquery (see section 5.2.1.3 Subqueries).

Parentheses are used to resolve ambiguities. In the following example, the first statement assigns the alias b to the second instance of my_table, but the second statement assigns the alias to the result of the join:

SELECT * FROM my_table AS a CROSS JOIN my_table AS b ...
SELECT * FROM (my_table AS a CROSS JOIN my_table) AS b ...

Another form of table aliasing gives temporary names to the columns of the table, as well as the table itself:

FROM table_reference [AS] alias ( column1 [, column2 [, ...]] )

If fewer column aliases are specified than the actual table has columns, the remaining columns are not renamed. This syntax is especially useful for self-joins or subqueries.

When an alias is applied to the output of a JOIN clause, using any of these forms, the alias hides the original names within the JOIN. For example,

SELECT a.* FROM my_table AS a JOIN your_table AS b ON ...

is valid SQL, but

SELECT a.* FROM (my_table AS a JOIN your_table AS b ON ...) AS c

is not valid: the table alias a is not visible outside the alias c.