As shown in the previous section, the table expression in the
SELECT command constructs an
intermediate virtual table by possibly combining tables, views,
eliminating rows, grouping, etc. This table is finally passed on to
processing by the select list. The
select list determines which columns of the intermediate table are
The simplest kind of select list is
* which emits all columns that the table
expression produces. Otherwise, a select list is a comma-separated
list of value expressions (as defined in Section 4.2). For instance,
it could be a list of column names:
SELECT a, b, c FROM ...
The columns names
c are either
the actual names of the columns of tables referenced in the
FROM clause, or the aliases given to
them as explained in Section 126.96.36.199.
The name space available in the select list is the same as in the
WHERE clause, unless grouping is used,
in which case it is the same as in the
If more than one table has a column of the same name, the table name must also be given, as in:
SELECT tbl1.a, tbl2.a, tbl1.b FROM ...
When working with multiple tables, it can also be useful to ask for all the columns of a particular table:
SELECT tbl1.*, tbl2.a FROM ...
See Section 8.16.5
for more about the
If an arbitrary value expression is used in the select list, it
conceptually adds a new virtual column to the returned table. The
value expression is evaluated once for each result row, with the
row's values substituted for any column references. But the
expressions in the select list do not have to reference any columns
in the table expression of the
clause; they can be constant arithmetic expressions, for
The entries in the select list can be assigned names for
subsequent processing, such as for use in an
ORDER BY clause or for display by the client
application. For example:
SELECT a AS value, b + c AS sum FROM ...
If no output column name is specified using
AS, the system assigns a default column name. For
simple column references, this is the name of the referenced
column. For function calls, this is the name of the function. For
complex expressions, the system will generate a generic name.
AS keyword is optional, but
only if the new column name does not match any PostgreSQL keyword (see Appendix C). To
avoid an accidental match to a keyword, you can double-quote the
column name. For example,
VALUE is a
keyword, so this does not work:
SELECT a value, b + c AS sum FROM ...
but this does:
SELECT a "value", b + c AS sum FROM ...
For protection against possible future keyword additions, it is
recommended that you always either write
AS or double-quote the output column name.
The naming of output columns here is different from that done in
FROM clause (see Section 188.8.131.52).
It is possible to rename the same column twice, but the name
assigned in the select list is the one that will be passed on.
After the select list has been processed, the result table can
optionally be subject to the elimination of duplicate rows. The
DISTINCT key word is written directly
SELECT to specify this:
DISTINCT the key word
ALL can be used to specify the default
behavior of retaining all rows.)
Obviously, two rows are considered distinct if they differ in at least one column value. Null values are considered equal in this comparison.
Alternatively, an arbitrary expression can determine what rows are to be considered distinct:
SELECT DISTINCT ON (
expression is an
arbitrary value expression that is evaluated for all rows. A set of
rows for which all the expressions are equal are considered
duplicates, and only the first row of the set is kept in the
output. Note that the “first row” of a set is unpredictable unless
the query is sorted on enough columns to guarantee a unique
ordering of the rows arriving at the
DISTINCT filter. (
ON processing occurs after
DISTINCT ON clause is not part
of the SQL standard and is sometimes considered bad style because
of the potentially indeterminate nature of its results. With
judicious use of
GROUP BY and
FROM, this construct can
be avoided, but it is often the most convenient alternative.
If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.