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 | WITHOUT ] OIDS ]
	   AS query


DESCRIPTION
       CREATE  TABLE  AS  creates a table and fills it with data computed by a
       SELECT command or an EXECUTE that runs a prepared 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_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 OIDS

       WITHOUT OIDS
	      This optional clause specifies whether the table created by CRE-
	      ATE TABLE AS should include OIDs. If neither form of this clause
	      is  specified,  the value of the default_with_oids configuration
	      parameter is used.

       query  A query statement (that is, a SELECT command or an EXECUTE  com-
	      mand  that  runs	a  prepared  SELECT  command). Refer to SELECT
	      [select(7)]  or  EXECUTE	[execute(l)],  respectively,   for   a
	      description of the allowed syntax.

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


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	 an  ON	 COMMIT	 clause; this is not currently
	 implemented by PostgreSQL.

       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.

       o WITH/WITHOUT OIDS is a PostgreSQL extension.

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

SEE ALSO
       CREATE	 TABLE	 [create_table(7)],   EXECUTE	[execute(l)],	SELECT
       [select(l)], SELECT INTO [select_into(l)]



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