Define a list of output columns for a query.

SELECT [ ALL | DISTINCT expression [ [ AS ] output_name ] [, ...] ]
[ * | expression [ [ AS ] output_name ] [, ...] ]
Return all rows for the expression, retaining duplicates (the default select list behavior).
Discard duplicate rows for the expression. When DISTINCT is used, the ORDER BY clause must only include expressions from the SELECT list. For example, the following query returns an error:
select distinct numteams, winners from season order by seasonid;
ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list

DISTINCT ON syntax is not supported.

Return all rows and columns from the table. See also TABLE, which provides equivalent syntax for a SELECT * query from a single table.
Define an alias for each resulting column. SQL functions return default names if you do not provide aliases. Column aliases can be referenced later in the select list itself and later in the query, such as in the WHERE and HAVING clauses.


For example, find distinct combinations of numteams and winners values in the season table:
premdb=# select distinct numteams, winners from season 
where winners is not null 
order by 2;
 numteams |      winners      
       20 | Arsenal
       22 | Blackburn Rovers
       20 | Chelsea
       20 | Leicester City
       20 | Manchester City
       20 | Manchester United
       22 | Manchester United
(7 rows)
Whole row references are not allowed. For example:
premdb=# select distinct team from team order by 1;
ERROR:  Whole row references are not allowed: team
LINE 1: select distinct team from team order by 1;

Column Aliases

In the select list of a query, you can define aliases for expressions and reuse them immediately in subsequent select list expressions or later in the query. For example, in this query the cap alias is defined in the first select list expression, then used in a calculation in the second expression:

premdb=# select avg_att/capacity as cap, cap*1000 from team where avg_att>0;
       cap        |     ?column?     
 0.00099475605708 | 0.99475605708000
 0.00078742549959 | 0.78742549959000
 0.00097601186322 | 0.97601186322000
 0.00099608765571 | 0.99608765571000
 0.00093833555513 | 0.93833555513000
 0.00094786305661 | 0.94786305661000
 0.00099810923067 | 0.99810923067000
In the following example, the alias c is reused in the WHERE clause, and the alias sa is reused in the ORDER BY clause:
premdb=# select city c, sum(avg_att) sa from team 
where c in('London','Manchester') group by c order by sa desc;
     c      |   sa    
 London     | 196.766
 Manchester | 129.327
(2 rows)

Multiple expressions may have the same alias in the same select list; if this kind of duplication occurs, the left-most alias is used to resolve later references.

When a volatile function, such as the RANDOM() function, is the target of an alias, the function is computed each time; its initial result does not persist. For example, in this query, r1, r2, and r3 all produce different results:
premdb=# select random() r1, r1 r2, r1 r3 from sys.const;
        r1        |        r2         |        r3         
 0.49950508915522 | 0.918498391599146 | 0.750150247759709
(1 row)