create_table_as man page on DragonFly

Man page or keyword search:  
man Server   44335 pages
apropos Keyword Search (all sections)
Output format
DragonFly logo
[printable version]

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

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	 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)], TABLE, or VALUES [values(7)]  command,  or
	      an EXECUTE [execute(7)] command that runs a prepared SELECT, TA‐
	      BLE, 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 ta‐
	      ble 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 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';

       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 non‐
       standard extensions:

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

       · In  the  standard,  the WITH [ NO ] DATA clause is required; in Post‐
	 greSQL it is optional.

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

SQL - Language Statements	  2014-07-21		    CREATE TABLE AS(7)
[top]

List of man pages available for DragonFly

Copyright (c) for man pages and the logo by the respective OS vendor.

For those who want to learn more, the polarhome community provides shell access and support.

[legal] [privacy] [GNU] [policy] [cookies] [netiquette] [sponsors] [FAQ]
Tweet
Polarhome, production since 1999.
Member of Polarhome portal.
Based on Fawad Halim's script.
....................................................................
Vote for polarhome
Free Shell Accounts :: the biggest list on the net