Provided by: postgresql-client-8.4_8.4.8-2_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
           [ 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:

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

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

       o PostgreSQL  handles  temporary  tables in a way rather different from
         the standard; see CREATE TABLE [create_table(7)] for details.

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

       o 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)]