create_table_as man page on UnixWare

Printed from http://www.polarhome.com/service/man/?qf=create_table_as&af=0&tf=2&of=UnixWare

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(5)] for details.

       TEMPORARY or TEMP
	      If  specified, the table is created as a temporary table.	 Refer
	      to CREATE TABLE [create_table(5)] 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(5)] 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(5)] 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(5)] 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
	      used, or the database's default tablespace if default_tablespace
	      is an empty string.

       query  A	 SELECT	 [select(5)] or VALUES [values(5)] command, or an EXE‐
	      CUTE [execute(5)] command that runs a prepared SELECT or	VALUES
	      query.

NOTES
       This  command  is functionally similar to SELECT INTO [select_into(5)],
       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:

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

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

       · PostgreSQL handles temporary tables in a way  rather  different  from
	 the standard; see CREATE TABLE [create_table(5)] 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(5)],   EXECUTE	[execute(l)],	SELECT
       [select(l)], SELECT INTO [select_into(l)], VALUES [values(l)]

SQL - Language Statements	  2008-01-03		     CREATE TABLE AS()
[top]
                             _         _         _ 
                            | |       | |       | |     
                            | |       | |       | |     
                         __ | | __ __ | | __ __ | | __  
                         \ \| |/ / \ \| |/ / \ \| |/ /  
                          \ \ / /   \ \ / /   \ \ / /   
                           \   /     \   /     \   /    
                            \_/       \_/       \_/ 
More information is available in HTML format for server UnixWare

List of man pages available for UnixWare

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