Product Documentation

Appearance

SELECT List ​

Define a list of output columns for a query.

SELECT [ ALL | DISTINCT expression [ [ AS ] output_name ] [, ...] ]
[ * | expression [ [ AS ] output_name ] [, ...] ]
ALL

Return all rows for the expression, retaining duplicates (the default select list behavior).

DISTINCT

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.

output_name

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.

Examples ​

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)

Parent topic:SELECT