Ubuntu manuals

Provided by: postgresql-client-8.0_8.0.7-2build1_i386

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

       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
              CREATE  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
              command 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 produced. As of PostgresSQL 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. While this variable
       currently defaults to true, the default value may  be  changed  in  the
       future.  Therefore, applications that require OIDs in the table created
       by CREATE TABLE AS  should  explicitly  specify  WITH  OIDS  to  ensure
       compatibility with future versions of PostgreSQL.

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 is specified by the SQL:2003 standard. There  are  some
       small differences between the definition of the command in SQL:2003 and
       its implementation in PostgreSQL:

       · 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  DATA  clause;  this  is  not  currently
         implemented by PostgreSQL.

SEE ALSO

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