Creating Tables Using SELECT

In addition to inserting into existing tables, SELECT can use an INTO clause to create a table and place all of its output into the new table. For example, suppose we want to create a new table called newfriend that is just like our friend table but lacks an age column. This task is easily done with the query shown in Figure .  

        test=> SELECT firstname, lastname, city, state 
        test-> INTO   newfriend
        test-> FROM   friend;
        SELECT 
         
        test=> \d newfriend
              Table "newfriend"
         Attribute |   Type   | Extra 
        -----------+----------+-------
         firstname | char(15) | 
         lastname  | char(20) | 
         city      | char(15) | 
         state     | char(2)  |  
         
        test=> SELECT * FROM newfriend ORDER BY firstname;
            firstname    |       lastname       |      city       | state 
        -----------------+----------------------+-----------------+-------
         Dean            | Yeager               | Plymouth        | MA
         Dick            | Gleason              | Ocean City      | NJ
         Ned             | Millstone            | Cedar Creek     | MD
         Sandy           | Gleason              | Ocean City      | NJ
         Sandy           | Weber                | Boston          | MA
         Victor          | Tabor                | Williamsport    | PA
        (6 rows)  

The SELECT...INTO query performs three operations:

1.

It creates a table called newfriend.

2.

It uses SELECT's column labels to name the columns of the new table.

3.

It uses SELECT's column types as the column types of the new table.

SELECT...INTO essentially combines CREATE TABLE and SELECT in a single statement. The AS clause can be used to change the column labels and thus control the column names in the new table. The other commands in the figure show the new table's structure and contents.

SELECT...INTO tablename can also be written as CREATE TABLE tablename AS SELECT.... The preceding query can then be rewritten as CREATE TABLE newfriend AS SELECT firstname, lastname, city, state FROM friend.