create_table_as manpage

Search topic Section
Get manual page for the search topic
List all commands matching the search topic
List all topics in the manpage index


       CREATE TABLE AS - define a new table from the results of a query

       CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name
	   [ (column_name [, ...] ) ] [ [ WITH | WITHOUT ] OIDS ]
	   AS query

       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.

       GLOBAL or LOCAL
	      Ignored for compatibility. Refer	to  CREATE  TABLE  [create_ta-
	      ble(7)] for details.

	      If  specified, the table is created as a temporary table.	 Refer
	      to CREATE TABLE [create_table(7)] for details.

	      The name (optionally schema-qualified) of the table to  be  cre-

	      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

	      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.

       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.

       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 TABLE AS conforms to the SQL standard, with the following excep-

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

       ? The standard defines an ON  COMMIT  clause;  this  is	not  currently
	 implemented by PostgreSQL.

       ? 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/WITHOUT OIDS is a PostgreSQL extension.

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

       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()
YoLinux.com Home Page
YoLinux Tutorial Index
Privacy Policy | Advertise with us | Feedback Form |
Unauthorized copying or redistribution prohibited.
    Bookmark and Share