PostgreSQL, Second Edition

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 ...