CREATE TABLE AS

Red Hat Database: SQL Guide and Reference
Prev		Next

Synopsis

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

Inputs

TEMPORARY or TEMP: If specified, the table is created only within this session, and is automatically dropped on session exit. Existing permanent tables with the same name are not visible (in this session) while the temporary table exists. Any indexes created on a temporary table are automatically temporary as well.
table: The name of the new table to be created. This table must not already exist. However, a temporary table can be created that has the same name as an existing permanent table.
column: The name of a column. Multiple column names can be specified using a comma-delimited list of column names. If column names are not provided, they are taken from the output column names of the SELECT query.
select_clause: A valid query statement. Refer to SELECT for a description of the allowed syntax.

Outputs

Refer to CREATE TABLE and SELECT for a summary of possible output messages.

Description

CREATE TABLE AS creates a table and fills it with data computed by a SELECT command. The table columns have the names and datatypes associated with the output columns of the SELECT (except that you can override the SELECT column names by giving an explicit list of column names).

Usage

To create a new table from the results of a SELECT statement:
create table cta1 (a int4, b int4); insert into cta1 values (1, 11); insert into cta1 values (2, 12); create temp table cta4 as select * from cta1 where a > 1;