/usr/man2/cat.l/create_table_as.l.Z(/usr/man2/cat.l/create_table_as.l.Z)

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_ta-
              ble(l)] for details.

       TEMPORARY or TEMP
              If  specified, the table is created as a temporary table.  Refer
              to CREATE TABLE [create_table(l)] 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 OIDS

       WITHOUT 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(l)]   or   EXECUTE  [execute(l)],  respectively,  for  a
              description of the allowed syntax.

NOTES

       This command is functionally similar to SELECT  INTO  [select_into(l)],
       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';

COMPATIBILITY

       CREATE TABLE AS conforms to the SQL standard, with the following excep-
       tions:

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

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

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

       o WITH/WITHOUT OIDS is a PostgreSQL extension.

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

SEE ALSO

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

SQL - Language Statements         2005-11-05                 CREATE TABLE AS()