Retrieves rows from a table or view.
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
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 JOIN
ed 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 nongrouped 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 the reference entry titled “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-SELECT
s, 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 aliases for each column in the from_alias
source 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.
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
.
Use the SELECT
command to retrieve rows of data from a 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 on their use.
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)
3.133.128.145