Supported Versions: Current (16) / 15 / 14 / 13 / 12
Development Versions: devel
Unsupported versions: 11 / 10 / 9.6 / 9.5 / 9.4 / 9.3 / 9.2 / 9.1 / 9.0 / 8.4 / 8.3 / 8.2 / 8.1 / 8.0 / 7.4 / 7.3 / 7.2 / 7.1
This documentation is for an unsupported version of PostgreSQL.
You may want to view the same page for the current version, or one of the other supported versions listed above instead.

CREATE TABLE AS

Name

CREATE TABLE AS  --  Creates a new table from the results of a SELECT

Synopsis

CREATE [ TEMPORARY | 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).

CREATE TABLE AS bears some resemblance to creating a view, but it is really quite different: it creates a new table and evaluates the SELECT just once to fill the new table initially. The new table will not track subsequent changes to the source tables of the SELECT. In contrast, a view re-evaluates the given SELECT whenever queried.

This command is functionally equivalent to SELECT INTO, but it is preferred since it is less likely to be confused with other uses of the SELECT ... INTO syntax.