Contents / Previous / Next

Querying a Table: SELECT

The SELECT statement is used to retrieve data from a table.

For example, retrieve all the rows of a table "weather": SELECT * FROM weather; city | temp_lo | temp_hi | prcp | date ---------------+---------+---------+------+------------ San Francisco | 46 | 50 | 0.25 | 1994-11-27 San Francisco | 43 | 57 | 0 | 1994-11-29 Hayward | 37 | 54 | | 1994-11-29 (3 rows) The statement is divided into a
select list (the part that lists the columns to be returned),
a table list (the part that lists the tables from which to retrieve the data),
and an optional qualification (the part that specifies any restrictions).

You may specify any arbitrary expressions in the target list
and use the AS clause to relabel columns:

SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather; city | temp_avg | date ---------------+----------+------------ San Francisco | 48 | 1994-11-27 San Francisco | 50 | 1994-11-29 Hayward | 45 | 1994-11-29 (3 rows)

Restrict a Query with WHERE:

SELECT * FROM weather WHERE city = 'San Francisco' AND prcp > 0.0; city | temp_lo | temp_hi | prcp | date ---------------+---------+---------+------+------------ San Francisco | 46 | 50 | 0.25 | 1994-11-27 (1 row) Arbitrary Boolean operators (AND, OR, and NOT) are allowed after WHERE.

The results of a select can be returned in sorted order using ORDER BY or
with duplicate rows removed using DISTINCT. Both in one Example:

SELECT DISTINCT city FROM weather ORDER BY city; city --------------- Hayward San Francisco (2 rows)