Like columns, FROM sources (e.g., tables, or sub-queries) may be aliased with the
AS clause. This is usually applied as a convenient shorthand for the dot-notation
described in the preceding section. Aliasing a data set allows you to refer to it via dot-notation, which provides a more succinct
and readable SQL statement. Example 4-29 demonstrates the same query used in Example 4-27, however you can see that it simplifies the dot-notation with the
AS clause.
Example 4-29. Aliasing FROM sources
booktown=#
SELECT b.id, title, a.id, last_name
booktown-#
FROM books AS b, authors AS a
booktown-#
WHERE b.author_id = a.id;
id | title | id | last_name
-------+-----------------------------+-------+--------------
190 | Little Women | 16 | Alcott
156 | The Tell-Tale Heart | 115 | Poe
41472 | Practical PostgreSQL | 1212 | Worsley
2038 | Dynamic Anatomy | 1644 | Hogarth
1608 | The Cat in the Hat | 1809 | Geisel
1590 | Bartholomew and the Oobleck | 1809 | Geisel
4513 | Dune | 1866 | Herbert
4267 | 2001: A Space Odyssey | 2001 | Clarke
1501 | Goodnight Moon | 2031 | Brown
7808 | The Shining | 4156 | King
41473 | Programming Python | 7805 | Lutz
41477 | Learning Python | 7805 | Lutz
41478 | Perl Cookbook | 7806 | Christiansen
25908 | Franklin in the Dark | 15990 | Bourgeois
1234 | The Velveteen Rabbit | 25041 | Bianco
(15 rows)
In addition to placing aliases on the FROM clause's data sources, you can place
aliases on the
columns
within that source. This is done by following a valid data source's alias
with a list of
column aliases
, grouped in parentheses and separated by commas. A column alias list
therefore consists of a sequence of identifier aliases for each column, which correspond to the literal columns in the
order that the table is defined with (from left to right).
When describing a column alias list, you do not need to specify each column; any column that is left unspecified
is accessible via its normal name within such a query. If the only column you wish to alias is to the right of any
other columns that you do not necessarily wish to alias, you will need to explicitly list the preceding columns (it is
valid to list the same name for an existing column as its "alias"). Otherwise, PostgreSQL will have no way of knowing
which column you were attempting to alias and will assume you were addressing the first column from the left.
Note: The AS keyword is technically considered noise, and may be omitted in practice;
PostgreSQL determines that any stray identifiers following a FROM source may be used as aliases.
Example 4-30 illustrates the same query that is used in Example 4-29 but aliases the id columns in each table to
unique identifiers in order to reference them directly (i.e., without dot-notation). The syntax is functionally
identical, aliasing only the books table's id column,
thus making the authors table's id column
non-ambiguous:
Example 4-30. Aliasing columns
booktown=#
SELECT the_book_id, title, id, last_name
booktown-#
FROM books AS b (the_book_id), authors
booktown-#
WHERE author_id = id;
the_book_id | title | id | last_name
-------------+-----------------------------+-------+--------------
190 | Little Women | 16 | Alcott
156 | The Tell-Tale Heart | 115 | Poe
41472 | Practical PostgreSQL | 1212 | Worsley
2038 | Dynamic Anatomy | 1644 | Hogarth
1608 | The Cat in the Hat | 1809 | Geisel
1590 | Bartholomew and the Oobleck | 1809 | Geisel
4513 | Dune | 1866 | Herbert
4267 | 2001: A Space Odyssey | 2001 | Clarke
1501 | Goodnight Moon | 2031 | Brown
7808 | The Shining | 4156 | King
41473 | Programming Python | 7805 | Lutz
41477 | Learning Python | 7805 | Lutz
41478 | Perl Cookbook | 7806 | Christiansen
25908 | Franklin in the Dark | 15990 | Bourgeois
1234 | The Velveteen Rabbit | 25041 | Bianco
(15 rows)
