Description

CREATE TABLE AS creates a table and
fills it with data computed by a SELECT
command. The table columns have the names and data types
associated with the output columns of the SELECT (except that you can override the column
names by giving an explicit list of new 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 query just once to fill
the new table initially. The new table will not track subsequent
changes to the source tables of the query. In contrast, a view
re-evaluates its defining SELECT
statement whenever it is queried.

Parameters

If specified, the table is created as a temporary table.
Refer to CREATE
TABLE for details.

table_name

The name (optionally schema-qualified) of the table to
be created.

column_name

The name of a column in the new table. If column names
are not provided, they are taken from the output column
names of the query. If the table is created from an
EXECUTE command, a column name
list cannot be specified.

WITH ( storage_parameter [= value] [, ... ] )

This clause specifies optional storage parameters for
the new table; see Storage
Parameters for more information. The WITH clause can also include OIDS=TRUE (or just OIDS) to specify that rows of the new table
should have OIDs (object identifiers) assigned to them, or
OIDS=FALSE to specify that the
rows should not have OIDs. See CREATE TABLE for more
information.

WITH OIDSWITHOUT OIDS

These are obsolescent syntaxes equivalent to WITH (OIDS) and WITH
(OIDS=FALSE), respectively. If you wish to give both
an OIDS setting and storage
parameters, you must use the WITH ( ...
) syntax; see above.

ON COMMIT

The behavior of temporary tables at the end of a
transaction block can be controlled using ON COMMIT. The three options are:

PRESERVE ROWS

No special action is taken at the ends of
transactions. This is the default behavior.

DELETE ROWS

All rows in the temporary table will be deleted at
the end of each transaction block. Essentially, an
automatic TRUNCATE is done at each
commit.

DROP

The temporary table will be dropped at the end of
the current transaction block.

TABLESPACE tablespace

The tablespace is the
name of the tablespace in which the new table is to be
created. If not specified, default_tablespace
is consulted, or temp_tablespaces
if the table is temporary.

Notes

This command is functionally similar to SELECT INTO, but it is preferred since
it is less likely to be confused with other uses of the
SELECT INTO syntax. Furthermore,
CREATE TABLE AS offers a superset of the
functionality offered by SELECT
INTO.

Prior to PostgreSQL 8.0,
CREATE TABLE AS always included OIDs in
the table it created. As of PostgreSQL 8.0, the CREATE TABLE AS command allows the user to
explicitly specify whether OIDs should be included. If the
presence of OIDs is not explicitly specified, the default_with_oids
configuration variable is used. As of PostgreSQL 8.1, this variable is false by
default, so the default behavior is not identical to pre-8.0
releases. Applications that require OIDs in the table created by
CREATE TABLE AS should explicitly
specify WITH (OIDS) to ensure proper
behavior.

Examples

Create a new table films_recent
consisting of only recent entries from the table films:

Compatibility

CREATE TABLE AS conforms to the
SQL standard, with the
following exceptions:

The standard requires parentheses around the subquery
clause; in PostgreSQL, these
parentheses are optional.

The standard defines a WITH [ NO ]
DATA clause; this is not currently implemented by
PostgreSQL. The behavior
provided by PostgreSQL is
equivalent to the standard's WITH
DATA case. WITH NO DATA can be
simulated by appending LIMIT 0 to
the query.

PostgreSQL handles
temporary tables in a way rather different from the standard;
see CREATE
TABLE for details.

The WITH clause is a PostgreSQL extension; neither storage
parameters nor OIDs are in the standard.

The PostgreSQL concept of
tablespaces is not part of the standard. Hence, the clause
TABLESPACE is an extension.