PostgreSQL 8.0.0rc4 Documentation
Prev | Fast Backward | Chapter 2. The SQL Language | Fast Forward | Next

# 2.5. Querying a Table

To retrieve data from a table, the table is queried. An SQL `SELECT` statement is used to do this. 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). For example, to retrieve all the rows of table `weather`, type:

    SELECT * FROM weather;

(here `*` means "all columns"). [1] The output should be:

         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)

You may specify any arbitrary expressions in the select list. For example, you can do:

    SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather;

This should give:

         city      | temp_avg |    date
    ---------------+----------+------------
     San Francisco |       48 | 1994-11-27
     San Francisco |       50 | 1994-11-29
     Hayward       |       45 | 1994-11-29
    (3 rows)

Notice how the `AS` clause is used to relabel the output column. (The `AS` clause is optional.)

Arbitrary Boolean operators (`AND`, `OR`, and `NOT`) are allowed in the qualification of a query. For example, the following retrieves the weather of San Francisco on rainy days:

    SELECT * FROM weather
        WHERE city = 'San Francisco'
        AND prcp > 0.0;

Result:

         city      | temp_lo | temp_hi | prcp |    date
    ---------------+---------+---------+------+------------
     San Francisco |      46 |      50 | 0.25 | 1994-11-27
    (1 row)

As a final note, you can request that the results of a query can be returned in sorted order or with duplicate rows removed:

    SELECT DISTINCT city
        FROM weather
        ORDER BY city;

         city
    ---------------
     Hayward
     San Francisco
    (2 rows)

`DISTINCT` and `ORDER BY` can be used separately, of course.

### Notes

[1] | While `SELECT *` is useful for off-the-cuff queries, it is widely considered bad style in production code, since adding a column to the table would change the results.

Prev | Home | Next
Populating a Table With Rows | Up | Joins Between Tables