Expressions, Constants, and Aliases

In addition to plain column names, targets in the SELECT statement may be arbitrary expressions (e.g., involving functions, or operators acting upon identifiers), or constants. The syntax is simple, and only requires that each identifier, expression, or constant be separated by commas. Conveniently, different types of targets may be arbitrarily mixed in the target list.

In fact, the SELECT command may be used to retrieve expressions and constants without the use of a FROM clause or specified columns, as in Example 4-25.

testdb=# 
SELECT 2 + 2,

testdb-# 
       pi(),

testdb-# 
       'PostgreSQL is more than a calculator!';

 ?column? |        pi        |               ?column?
----------+------------------+---------------------------------------
        4 | 3.14159265358979 | PostgreSQL is more than a calculator!
(1 row)

The target list allows the use of an optional AS clause for each specified target, which re-names a column in the returned result set to an arbitrary name specified in the clause. The rules and limitations for the specified name are the same as for normal identifiers (e.g., they may be quoted to contain spaces, may not be keywords unless quoted, and so on).

Using AS has no lasting effect on the column itself, but only on the result set which is returned by the query. AS can be particularly useful when selecting expressions or constants, rather than plain columns. Naming result set columns with AS can clarify the meaning of an otherwise ambiguous expression or constant. This technique is demonstrated in Example 4-26, which shows the same results as Example 4-25, but with different column headings.

booktown=# 
SELECT 2 + 2 AS "2 plus 2",

booktown-# 
       pi() AS "the pi function",

booktown-# 
       'PostgreSQL is more than a calculator!' AS comments;

 2 plus 2 | the pi function  |               comments
----------+------------------+---------------------------------------
        4 | 3.14159265358979 | PostgreSQL is more than a calculator!
(1 row)