Provided by: postgresql-client-8.2_8.2.7-1_i386 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

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
              used, or the database’s default tablespace if default_tablespace
              is an empty string.

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

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’;

       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(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(l)],    SELECT
       [select(l)], SELECT INTO [select_into(l)], VALUES [values(l)]