Creating Tables

Let's create our own table and call it friend. Figure  shows the psql statement to create this table.  

        test=> CREATE TABLE friend (
        test(>              firstname CHAR(15),
        test(>              lastname  CHAR(20),
        test(>              city      CHAR(15),
        test(>              state     CHAR(2),
        test(>              age       INTEGER
        test(> );
        CREATE  

You do not have to type the command exactly this way. You can use all lowercase, or you can write it in one long line, and it would work just the same.

Let's look at the statement from the top down. The words CREATE TABLE have special meaning to the database server. They indicate that the next request from the user is to create a table. You will find most SQL requests can be quickly identified by the first few words. The rest of the request has a specific format that is understood by the database server. While capitalization and spacing are optional, the format for a query must be followed exactly.  Otherwise, the database server will issue an error such as parser: parse error at or near "pencil", meaning that the database server became confused near the word pencil. In such a case, the manual page for the command should be consulted and the query reissued in the proper format. A copy of the POSTGRESQL manual pages appears in Appendix .

The CREATE TABLE command follows a specific format: first, the two words CREATE TABLE; then the table name; then an opening parenthesis; then a list of column names and their types; followed by a closing parenthesis. The important part of this query appears between the parentheses. You will notice five lines there in Figure . The first line, firstname CHAR(15), represents the first column of the table to create. This column is named firstname, and the text CHAR(15) indicates the column type and length. The CHAR(15) means the column holds a maximum of 15 characters. The second column is called lastname and holds a maximum of 20 characters. Columns of type CHAR() hold characters of a specified length. User-supplied character strings^8.2 that do not fill the entire length of the field are right-padded with blanks. The columns city and state are similar. The final column, age, is different, however. It is not a CHAR() column, but rather an INTEGER column. It holds whole numbers, not characters. Even if the table contained 5,000 friends, you could be certain that no names appeared in the age column, only whole numbers. This consistent structure helps databases to be fast and reliable.

POSTGRESQL supports more column types than just CHAR() and INTEGER. However, in this chapter we will use only these two. Sections  and  cover column types in more detail.

Create some tables yourself now. Use only letters for your table and column names. Do not use any numbers, punctuation, or spaces at this time.

The \d command allows you to see information about a specific table or to list all table names in the current database. To see information about a specific table, type \d followed by the name of the table. For example, to see the column names and types of your new friend table in psql, type \d friend (Figure ).  

        test=> \d friend
                 Table "friend"
         Attribute |   Type   | Modifier 
        -----------+----------+----------
         firstname | char(15) | 
         lastname  | char(20) | 
         city      | char(15) | 
         state     | char(2)  | 
         age       | integer  |   

If you use \d with no table name after it, you will see a list of all table names in the database.