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)