Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com
Answertopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions
Privacy Policy

  




 

 

Databases - Practical PostgreSQL
Previous Page Home Next Page

Aliasing FROM Sources

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)
Databases - Practical PostgreSQL
Previous Page Home Next Page

 
 
  Published under the terms of the Open Publication License Design by Interspire