CREATE TABLE AS()		 SQL Commands		     CREATE TABLE AS()



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  asso-
       ciated with the output columns of the SELECT (except that you can over-
       ride 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_ta-
	      ble(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  cre-
	      ated.

       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 infor-
	      mation. 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 [cre-
	      ate_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)] or VALUES [values(7)] command, or an EXE-
	      CUTE [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 iden-
       tical 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 excep-
       tions:

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

       o 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.

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



SQL - Language Statements	  2010-03-12		     CREATE TABLE AS()