Viewing Data with SELECT

You have just seen how to store data in the database. Now, let's retrieve that data. Surprisingly, only one command is provided to get data out of the database--SELECT. You have already used SELECT in your first database query (see Figure  on page ). We will now use it to show the rows in the table friend. As shown in Figure , the entire query appears on one line.  

        test=> SELECT * FROM friend;
            firstname    |       lastname       |      city       | state | age 
        -----------------+----------------------+-----------------+-------+-----
         Mike            | Nichols              | Tampa           | FL    |  19
         Cindy           | Anderson             | Denver          | CO    |  23
         Sam             | Jackson              | Allentown       | PA    |  22
        (3 rows)  

As queries become longer, breaking them into multiple lines helps make things clearer.

Let's look at this example in detail. First, we have the word SELECT, followed by an asterisk (*), the word FROM, our table name friend, and a semicolon to execute the query. The SELECT starts our command, telling the database server what is coming next. The * tells the server we want all the columns from the table. The FROM friend indicates which table we want to see. Thus, we have said we want all (*) columns from our table friend. Indeed, that is what is displayed--the same data as shown in Table  on page .

SELECT has a large number of variations, and we will look at a few of them now. Suppose you want to retrieve only one of the columns from the friend table. You might already suspect that the asterisk (*) must be changed in the query. If you replace it with one of the column names, you will see only that column. Try SELECT city FROM friend. You can choose any of the columns. You can even choose multiple columns, by separating the names with a comma. For example, to see first and last names only, use SELECT firstname, lastname FROM friend. Try a few more SELECT commands until you become comfortable.

If you specify a name that is not a valid column name, you will get an error message like ERROR: attribute 'mycolname' not found.  If you try selecting from a table that does not exist, you will get an error message like ERROR: Relation 'mytablename' does not exist.  POSTGRESQL uses the formal relational database  terms relation and attribute in these error messages.