Provided by: postgresql-client-8.4_8.4.11-1_amd64 bug

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
           [ WITH [ NO ] DATA ]

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(7)] for details.

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

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

       WITH [ NO ] DATA
              This  clause  specifies  whether  or  not  the data produced by the query should be
              copied into the new table. If not, only the table structure is copied. The  default
              is to copy the data.

NOTES

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

       To copy a table completely, the short form using the TABLE command can also be used:

       CREATE TABLE films2 AS
         TABLE films;

       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. The following are nonstandard extensions:

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

       · In the standard, the WITH [ NO ] DATA clause is required; in PostgreSQL it is optional.

       · PostgreSQL  handles  temporary  tables  in a way rather different from the standard; see
         CREATE TABLE [create_table(7)] 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(7)], EXECUTE  [execute(7)],  SELECT  [select(7)],  SELECT  INTO
       [select_into(7)], VALUES [values(7)]