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 laterPostgreSQL 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.customer_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.customer_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 namethe 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)


Part I: General PostgreSQL Use

Introduction to PostgreSQL and SQL

Working with Data in PostgreSQL

PostgreSQL SQL Syntax and Use

Performance

Part II: Programming with PostgreSQL

Introduction to PostgreSQL Programming

Extending PostgreSQL

PL/pgSQL

The PostgreSQL C APIlibpq

A Simpler C APIlibpgeasy

The New PostgreSQL C++ APIlibpqxx

Embedding SQL Commands in C Programsecpg

Using PostgreSQL from an ODBC Client Application

Using PostgreSQL from a Java Client Application

Using PostgreSQL with Perl

Using PostgreSQL with PHP

Using PostgreSQL with Tcl and Tcl/Tk

Using PostgreSQL with Python

Npgsql: The .NET Data Provider

Other Useful Programming Tools

Part III: PostgreSQL Administration

Introduction to PostgreSQL Administration

PostgreSQL Administration

Internationalization and Localization

Security

Replicating PostgreSQL Data with Slony

Contributed Modules

Index



PostgreSQL(c) The comprehensive guide to building, programming, and administering PostgreSQL databases
PostgreSQL(c) The comprehensive guide to building, programming, and administering PostgreSQL databases
ISBN: 735712573
EAN: N/A
Year: 2004
Pages: 261

Flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net