Index Index for
Section TABLE
Index Alphabetical
listing for C
Bottom of page Bottom of
page		 

CREATE


NAME

  CREATE TABLE AS - define a new table from the results of a query


SYNOPSIS

  CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name
      [ (column_name [, ...] ) ]
      [ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
      [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
      [ TABLESPACE tablespace ]
      AS query


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

  GLOBAL or LOCAL
       Ignored for compatibility. Refer to CREATE TABLE [create_table(5)] for
       details.

  TEMPORARY or TEMP
       If specified, the table is created as a temporary table.	 Refer to
       CREATE TABLE [create_table(5)] 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 [create_table(5)] 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 [create_table(5)] for more information.

  WITH OIDS

  WITHOUT 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 [truncate(5)]
	 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 used, or the
       database's default tablespace if default_tablespace is an empty
       string.

  query
       A SELECT [select(5)] or VALUES [values(5)] command, or an EXECUTE
       [execute(5)] command that runs a prepared SELECT or VALUES query.


NOTES

  This command is functionally similar to SELECT INTO [select_into(5)], 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:

  CREATE TABLE films_recent AS
    SELECT * FROM films WHERE date_prod >= '2002-01-01';

  Create a new temporary table films_recent, consisting of only recent
  entries from the table films, using a prepared statement. The new table has
  OIDs and will be dropped at commit:

  PREPARE recentfilms(date) AS
    SELECT * FROM films WHERE date_prod > $1;
  CREATE TEMP TABLE films_recent WITH (OIDS) ON COMMIT DROP AS
    EXECUTE recentfilms('2002-01-01');


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 [create_table(5)] 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.


SEE ALSO

  CREATE TABLE [create_table(5)], EXECUTE [execute(l)], SELECT [select(l)],
  SELECT INTO [select_into(l)], VALUES [values(l)]

Index Index for
Section TABLE
Index Alphabetical
listing for C
Top of page Top of
page