Полезная информация

next up previous contents index
Next: Adding Data with INSERT Up: Five Basic SQL Commands Previous: Relational Databases

Creating Tables

Let's create our own table and call it friends. The psql query to create the table is shown in figure [*].

  
Figure: Create table
\begin{figure}\begin{list}{}{
\setlength{\rightmargin}{\leftmargin}
\raggedrigh...
...~~~INTEGER
\par test->~)\textbackslash{}g
\par CREATE\end{list}\par
\end{figure}

You don't have to type it exactly like that. You could have used all lowercase, or you could have written it in one long line, and it would have worked just the same.

Let's look at it 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 is optional, the format for a query must be followed, or the database server will issue an error such as parser: parse error at or near "pencil", meaning the database got 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 appear in appendix [*].

The CREATE TABLE command follows a specific format. First, the two words CREATE TABLE, then an open parenthesis, then a list of column names and their types, followed by a close parenthesis. The important part of this query is between the parentheses. You will notice there are five lines there. The first line, firstname CHAR(15), represents the first column of the table to create. The word firstname is the name of the first column, and the text CHAR(15) tells the column type. The first column of every row holds up to 15 characters. That's what CHAR(15) means. The second column is called lastname and holds up to 20 characters. Columns of type char hold characters with a specified limit to their length. Columns city and state are similar. The final column, age, is different. It is not a CHAR() column. It is an INTEGER column. It holds whole numbers, not characters. Even if there were 5,000 friends in the table, you can be certain that there are no names appearing in the age column, only whole numbers. It is this structure that helps databases be fast and reliable.

Create some tables yourself now. Only use letters for your table and column names. Don't use any numbers, punctuation, or spaces at this time.

You can use the \d command to see the tables you created. If you type \d and press Enter, you will see the names of all the tables in the current database. To see information about a specific table, type \d followed by the name of the table. To see the column names and types of your new friends table in psql, type \d friends. As you create tables, view your new tables with \d.


next up previous contents index
Next: Adding Data with INSERT Up: Five Basic SQL Commands Previous: Relational Databases
Bruce Momjian
1999-11-21