Creating New Tables Using CREATE TABLE...AS

Let's turn our attention to something completely different. Earlier in this chapter, you learned how to use the INSERT statement to store data in a table. Sometimes, you want to create a new table based on the results of a SELECT command. That's exactly what the CREATE TABLE...AS command is designed to do.

The format of CREATE TABLE...AS is

CREATE [ TEMPORARY | TEMP ] TABLE table [ (column [, ...] ) ]

  AS select_clause

When you execute a CREATE TABLE...AS command, PostgreSQL automatically creates a new table. Each column in the new table corresponds to a column returned by the SELECT clause. If you include the TEMPORARY (or TEMP) keyword, PostgreSQL will create a temporary table. This table is invisible to other users and is destroyed when you end your PostgreSQL session. A temporary table is useful because you don't have to remember to remove the table later?PostgreSQL takes care of that detail for you.

Let's look at an example. A few pages earlier in the chapter, you created a complex join between the customers, rentals, and tapes tables. Let's create a new table based on that query so you don't have to keep entering the same complex query^[13]:

^[13] Some readers are probably thinking, "Hey, you should use a view to do that!" You're right, you'll soon see that I just needed a bad example.

movies=# CREATE TABLE info AS

movies-#   SELECT customers.customer_name, rentals.tape_id, tapes.title

movies-#     FROM customers FULL OUTER JOIN rentals

movies-#       ON customers.id = rentals.customer_id

movies-#     FULL OUTER JOIN tapes

movies-#       ON tapes.tape_id = rentals.tape_id;

SELECT

movies=# SELECT * FROM info;

    customer_name     | tape_id  |        title

----------------------+----------+----------------------

 Jones, Henry         | AB-12345 | The Godfather

 Panky, Henry         | AB-67472 | The Godfather

                      |          | Rear Window

                      |          | American Citizen, An

 Panky, Henry         | MC-68873 | Casablanca

 Jones, Henry         | OW-41221 | Citizen Kane

 Rubin, William       |          |

 Wonderland, Alice N. |          |

 Funkmaster, Freddy   |          |

 Gull, Jonathon LC    |          |

 Grumby, Jonas        |          |

                      |          | Sly

                      |          | Stone Cold

(13 rows)

This is the same complex query that you saw earlier. I'll point out a few things about this example. First, notice that the SELECT command selected three columns (customer_name, tape_id, title)?the result table has three columns. Next, you can create a table using an arbitrarily complex SELECT command. Finally, notice that the TEMPORARY keyword is not included; therefore, info is a permanent table and is visible to other users.

What happens if you try to create the info table again?

movies=# CREATE TABLE info AS

movies-#   SELECT customers.customer_name, rentals.tape_id, tapes.title

movies-#     FROM customers FULL OUTER JOIN rentals

movies-#       ON customers.id = rentals.customer_id

movies-#     FULL OUTER JOIN tapes

movies-#       ON tapes.tape_id = rentals.tape_id;

ERROR:  Relation 'info' already exists

As you might expect, you receive an error message because the info table already exists. CREATE TABLE...AS will not automatically drop an existing table. Now let's see what happens if you include the TEMPORARY keyword:

movies=# CREATE TEMPORARY TABLE info AS

movies-#   SELECT * FROM tapes;

SELECT

movies=# SELECT * FROM info;

 tape_id  |        title         | duration

----------+----------------------+----------

 AB-12345 | The Godfather        |

 AB-67472 | The Godfather        |

 MC-68873 | Casablanca           |

 OW-41221 | Citizen Kane         |

 AH-54706 | Rear Window          |

 OW-42200 | Sly                  | 01:36

 KJ-03335 | American Citizen, An |

 OW-42201 | Stone Cold           | 01:52

(8 rows)

This time, the CREATE TABLE...AS command succeeded. When I SELECT from info, I see a copy of the tapes table. Doesn't this violate the rule that I mentioned earlier ("CREATE TABLE…AS will not automatically drop an existing table")? Not really. When you create a temporary table, you are hiding any permanent table of the same name?the original (permanent) table still exists. Other users will still see the permanent table. If you DROP the temporary table, the permanent table will reappear:

movies=# SELECT * FROM info;

 tape_id  |        title         | duration

----------+----------------------+----------

 AB-12345 | The Godfather        |

 AB-67472 | The Godfather        |

 MC-68873 | Casablanca           |

 OW-41221 | Citizen Kane         |

 AH-54706 | Rear Window          |

 OW-42200 | Sly                  | 01:36

 KJ-03335 | American Citizen, An |

 OW-42201 | Stone Cold           | 01:52

(8 rows)



movies=# DROP TABLE info;

DROP

movies=# SELECT * FROM info;

    customer_name     | tape_id  |        title

----------------------+----------+----------------------

 Jones, Henry         | AB-12345 | The Godfather

 Panky, Henry         | AB-67472 | The Godfather

                      |          | Rear Window

                      |          | American Citizen, An

 Panky, Henry         | MC-68873 | Casablanca

 Jones, Henry         | OW-41221 | Citizen Kane

 Rubin, William       |          |

 Wonderland, Alice N. |          |

 Funkmaster, Freddy   |          |

 Gull, Jonathon LC    |          |

 Grumby, Jonas        |          |

                      |          | Sly

                      |          | Stone Cold

(13 rows)