Man Linux: Main Page and Category List


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


       CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name
           [ (column_name [, ...] ) ]
           [ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
           [ TABLESPACE tablespace ]
           AS query
           [ WITH [ NO ] DATA ]


       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
       associated  with  the output columns of the SELECT (except that you can
       override the column names by giving an  explicit  list  of  new  column

       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_table(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

              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
              information. 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
              [create_table(7)] for more information.

       WITH 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,
              TABLE, 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
              table structure is copied. The default is to copy the data.


       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
       identical 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’;

       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;
         EXECUTE recentfilms(’2002-01-01’);


       CREATE TABLE AS  conforms  to  the  SQL  standard.  The  following  are
       nonstandard 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
         PostgreSQL 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.


       CREATE   TABLE   [create_table(7)],   EXECUTE   [execute(7)],    SELECT
       [select(7)], SELECT INTO [select_into(7)], VALUES [values(7)]