SELECT

Name

SELECT -- Retrieves rows from a table or view.

Synopsis

SELECT [ ALL | DISTINCT [ ON ( distinct_expression [, ...] ) ] ]
    target_expression [ AS output_name ] [, ...]
    [ FROM from_item [ { , | CROSS JOIN } ...] ]
    [ WHERE condition ]
    [ GROUP BY aggregate_expression [, ...] ]
    [ HAVING aggregate_condition [, ...] ]
    [ { UNION | INTERSECT | EXCEPT [ALL] } select ]
    [ ORDER BY order_expression [ ASC | DESC | USING operator ] [, ...] ]
    [ FOR UPDATE [ OF update_table [, ...] ] ]
    [ LIMIT { ALL | count } [ { OFFSET | , } start ] ]

from_item ::= { [ ONLY ] table_name [ * ] 
                  [ [ AS ] from_alias [ ( column_alias_list ) ] ] |
                ( select ) [ [ AS ] alias [ ( column_alias_list ) ] ] |
                from_item [ NATURAL ] join_type from_item
                  [ ON ( join_condition ) | USING ( join_column_list ) ]
              }

join_type ::= [ INNER |
                LEFT  [ OUTER ] |
                RIGHT [ OUTER ] |
                FULL  [ OUTER ]
              ] JOIN

Parameters

ALL | DISTINCT

The DISTINCT keyword indicates that duplicate values found in two or more rows will not be shown after the first row. The ALL keyword explicitly reinforces the default to retrieve all rows regardless of uniqueness.

Note that the ORDER BY clause sorts rows before the DISTINCT clause removes non-unique rows. Use these clauses together to ensure that the row found is the row you intend to retrieve.

DISTINCT ON

The ON keyword, following the DISTINCT keyword, allows you to specify one or more distinct_expressions by which to judge uniqueness.

distinct_expression

A column name within a from_item, or a valid expression, whose value is used by the DISTINCT ON clause as a basis for removing duplicate values.

target_expression

A column name within a from_item, or a valid expression.

output_name

An alternate name for an output column, following the AS clause. This name will then be used during display of the output and can be used to reference the column within ORDER BY and GROUP BY clauses in the same SELECT statement. However, this name does not apply to the WHERE or HAVING clauses; you will need to use the correct column name for them.

FROM

The clause which is passed from_items, from which to retrieve rows.

from_item

The name of a table, a subselect, or a JOINed set of from_items that you wish to retrieve data from.

{ , | CROSS JOIN }

The comma (or formal CROSS JOIN clause) separates multiple from_items.

WHERE

The clause that is passed conditions by which to constraint a result set.

condition

An expression that yields either true or false, applied conditionally to non-grouped target expressions.

GROUP BY

The clause that is passed aggregate_expressions to aggregate (group) rows together.

aggregate_expression

A column name within a from_item, or a valid expression, to be used as a basis to aggregate (group) rows together.

HAVING

The clause to which is passed any aggregate_conditions by which to constrain a result set.

aggregate_condition

An expression that yields either true or false, applied conditionally to aggregated (grouped) target expressions.

UNION

The clause that combines two result sets with compatible column structure into a single combined result set.

INTERSECT

The clause that removes any rows from the initial result set not found in the following select statement's result set (resulting in the overlapping, or intersecting, set).

EXCEPT

The clause that removes any rows from the initial result set that are found in the following select statement's result set (resulting in the difference set).

select

A full select statement. The limitation on this form of subquery is that you cannot use any of the ORDER BY, FOR UPDATE, or LIMIT clauses unless the statement is enclosed in parentheses.

ORDER BY

The ORDER BY clause sorts the retrieved result set by each order_expression provided.

order_expression [ ASC | DESC | USING operator]

A column name in the retrieved result set by which the ORDER BY clause sorts the results. The use of the ASC keyword explicitly defines the default of ascending sorting, while the DESC implies descending sorting. The USING clause defines an operator (e.g., >) to compare subsequent order_expression values with.

FOR UPDATE

The locking clause that places an implicit ROW SHARE MODE lock (see LOCK") on the from_item table selected in the current transaction.

OF update_table

A specific table to which to apply ROW SHARE MODE locking when multiple tables are selected in the FROM clause.

LIMIT

The LIMIT clause constrains only a specified portion of the retrieved results.

ALL | count

The ALL keyword explicitly specifies the default, which is to not limit the number of rows returned. The use of a numeric count value limits the number of rows in the retrieved result set to count.

{ OFFSET | , } start

The OFFSET keyword (or informal comma, following the LIMIT clause) allows a result set to ignore the first start rows.

The following clauses and parameters are available within each from_item:

[ ONLY ] table_name [ * ]

The name of an existing table or view from which you wish to retrieve rows. If you do not specify ONLY, all descendant tables will be searched as well. You can also add an asterisk (*) to indicate a wildcard after the table name to have descendant tables searched.

sub_select

A sub-SELECT statement within the FROM clause of a SELECT statement; this creates a temporary table from which rows can be pulled for the duration of the command. Aliases must be provided for sub-SELECTs, and they must also be surrounded in parentheses.

[ AS ] from_alias

The from_alias is a substitute name for a referenced table in the FROM clause.

column_alias_list

A comma-delimited list of alias names for each column in the from_alias source immediately preceding it. There may be fewer aliases listed in column_alias_list than there are columns in the from_alias source to which it applies.

join_type

The join type, where the type is one of the following:

  • [ INNER ] JOIN

  • LEFT [ OUTER ] JOIN

  • RIGHT [ OUTER ] JOIN

  • FULL [ OUTER ] JOIN

NATURAL

The optional NATURAL keyword indicates that the join will join the two from_items based on any identically-named columns they share. The use of this keyword precludes the use of explicit join_conditions or a join_column_list.

join_condition

A join qualification condition following the ON clause. Functionally, this clause is the same as a WHERE clause, except that the condition will only be applied to the two objects being joined.

join_column_list

A list of columns following the USING clause. This list is a shortened way of specifying the ON clause; it implies equivalence of columns within the FROM sources of a join that have the same names in the two joined sources.

Results

The primary result of a SELECT statement is a list of rows contained in the selected result set, followed by the number of rows retrieved. One of the following error messages may alternatively be encountered:

ERROR: Relation 'from_item' does not exist

The error returned if a specified from_item table or view cannot be found in the connected database.

ERROR: Table name "from_item" specified more than once

The error returned if a from_item database table or view is specified twice without an alias. You can avoid this error by applying an alias to one of the named from_item sources with the AS clause.

ERROR: Attribute 'column' not found

The error returned if a specified column cannot be found in any specified from_item.

Description

Use the SELECT command to retrieve rows of data from table, view, subquery, or any joined result set. Use the WHERE clause to set a condition that rows must meet in order to be retrieved; rows will not be retrieved if they don't meet the condition. If you do not specify any conditions using WHERE, all rows in the data source will be retrieved.

There are many clauses available within a SELECT statement. See the "Parameters" section of this reference entry for a listing of these clauses and their descriptions. See Chapter 4 for more detailed instructions for their use.

Examples

The following example selects all rows from the books table:

booktown=# SELECT * FROM books;
  id   |            title            | author_id | subject_id
-------+-----------------------------+-----------+------------
  7808 | The Shining                 |      4156 |          9
  4513 | Dune                        |      1866 |         15
  4267 | 2001: A Space Odyssey       |      2001 |         15
  1608 | The Cat in the Hat          |      1809 |          2
  1590 | Bartholomew and the Oobleck |      1809 |          2
 25908 | Franklin in the Dark        |     15990 |          2
  1501 | Goodnight Moon              |      2031 |          2
   190 | Little Women                |        16 |          6
  1234 | The Velveteen Rabbit        |     25041 |          3
  2038 | Dynamic Anatomy             |      1644 |          0
   156 | The Tell-Tale Heart         |       115 |          9
 41472 | Practical PostgreSQL        |      1212 |          4
 41473 | Programming Python          |      7805 |          4
 41477 | Learning Python             |      7805 |          4
 41478 | Perl Cookbook               |      7806 |          4
(15 rows)

The next example selects only rows with an ID number higher than 5000 will be retrieved:

booktown=# SELECT * FROM books WHERE id > 5000;
  id   |         title         | author_id | subject_id
-------+-----------------------+-----------+------------
  7808 | The Shining           |      4156 |          9
 25908 | Franklin in the Dark  |     15990 |          2
 41472 | Practical PostgreSQL  |      1212 |          4
 41473 | Programming Python    |      7805 |          4
 41477 | Learning Python       |      7805 |          4
 41478 | Perl Cookbook         |      7806 |          4
(6 rows)