1CREATE TABLE AS(7)               SQL Commands               CREATE TABLE AS(7)
2
3
4

NAME

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

SYNOPSIS

10       CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name
11           [ (column_name [, ...] ) ]
12           [ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
13           [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
14           [ TABLESPACE tablespace ]
15           AS query
16           [ WITH [ NO ] DATA ]
17
18

DESCRIPTION

20       CREATE  TABLE  AS  creates a table and fills it with data computed by a
21       SELECT command.  The table columns have the names and data types  asso‐
22       ciated with the output columns of the SELECT (except that you can over‐
23       ride the column names by giving an explicit list of new column names).
24
25       CREATE TABLE AS bears some resemblance to creating a view,  but  it  is
26       really  quite different: it creates a new table and evaluates the query
27       just once to fill the new table initially. The new table will not track
28       subsequent  changes  to  the source tables of the query. In contrast, a
29       view re-evaluates its defining SELECT statement whenever it is queried.
30

PARAMETERS

32       GLOBAL or LOCAL
33              Ignored for compatibility. Refer  to  CREATE  TABLE  [create_ta‐
34              ble(7)] for details.
35
36       TEMPORARY or TEMP
37              If  specified, the table is created as a temporary table.  Refer
38              to CREATE TABLE [create_table(7)] for details.
39
40       table_name
41              The name (optionally schema-qualified) of the table to  be  cre‐
42              ated.
43
44       column_name
45              The  name  of a column in the new table. If column names are not
46              provided, they are taken from the output  column  names  of  the
47              query. If the table is created from an EXECUTE command, a column
48              name list cannot be specified.
49
50       WITH ( storage_parameter [= value] [, ... ] )
51              This clause specifies optional storage parameters  for  the  new
52              table;  see Storage Parameters [create_table(7)] for more infor‐
53              mation. The WITH clause can  also  include  OIDS=TRUE  (or  just
54              OIDS)  to  specify  that  rows of the new table should have OIDs
55              (object identifiers) assigned to them, or OIDS=FALSE to  specify
56              that  the  rows  should  not  have OIDs.  See CREATE TABLE [cre‐
57              ate_table(7)] for more information.
58
59       WITH OIDS
60
61       WITHOUT OIDS
62              These are obsolescent syntaxes equivalent  to  WITH  (OIDS)  and
63              WITH  (OIDS=FALSE),  respectively.  If  you wish to give both an
64              OIDS setting and storage parameters, you must use the WITH ( ...
65              ) syntax; see above.
66
67       ON COMMIT
68              The  behavior  of  temporary  tables at the end of a transaction
69              block can be controlled using ON COMMIT.  The three options are:
70
71              PRESERVE ROWS
72                     No special action is taken at the ends  of  transactions.
73                     This is the default behavior.
74
75              DELETE ROWS
76                     All  rows  in  the temporary table will be deleted at the
77                     end of each transaction block. Essentially, an  automatic
78                     TRUNCATE [truncate(7)] is done at each commit.
79
80              DROP   The  temporary  table  will  be dropped at the end of the
81                     current transaction block.
82
83       TABLESPACE tablespace
84              The tablespace is the name of the tablespace in  which  the  new
85              table is to be created.  If not specified, default_tablespace is
86              consulted, or temp_tablespaces if the table is temporary.
87
88       query  A SELECT [select(7)], TABLE, or VALUES [values(7)]  command,  or
89              an EXECUTE [execute(7)] command that runs a prepared SELECT, TA‐
90              BLE, or VALUES query.
91
92       WITH [ NO ] DATA
93              This clause specifies whether or not the data  produced  by  the
94              query  should be copied into the new table. If not, only the ta‐
95              ble structure is copied. The default is to copy the data.
96

NOTES

98       This command is functionally similar to SELECT  INTO  [select_into(7)],
99       but  it  is preferred since it is less likely to be confused with other
100       uses of the SELECT INTO syntax. Furthermore, CREATE TABLE AS  offers  a
101       superset of the functionality offered by SELECT INTO.
102
103       Prior  to  PostgreSQL  8.0, CREATE TABLE AS always included OIDs in the
104       table it created. As of PostgreSQL 8.0, the  CREATE  TABLE  AS  command
105       allows  the user to explicitly specify whether OIDs should be included.
106       If  the  presence  of   OIDs   is   not   explicitly   specified,   the
107       default_with_oids configuration variable is used. As of PostgreSQL 8.1,
108       this variable is false by default, so the default behavior is not iden‐
109       tical  to pre-8.0 releases. Applications that require OIDs in the table
110       created by CREATE TABLE AS should explicitly  specify  WITH  (OIDS)  to
111       ensure proper behavior.
112

EXAMPLES

114       Create  a new table films_recent consisting of only recent entries from
115       the table films:
116
117       CREATE TABLE films_recent AS
118         SELECT * FROM films WHERE date_prod >= '2002-01-01';
119
120
121       To copy a table completely, the short form using the TABLE command  can
122       also be used:
123
124       CREATE TABLE films2 AS
125         TABLE films;
126
127
128       Create  a  new  temporary table films_recent, consisting of only recent
129       entries from the table films, using a prepared statement. The new table
130       has OIDs and will be dropped at commit:
131
132       PREPARE recentfilms(date) AS
133         SELECT * FROM films WHERE date_prod > $1;
134       CREATE TEMP TABLE films_recent WITH (OIDS) ON COMMIT DROP AS
135         EXECUTE recentfilms('2002-01-01');
136
137

COMPATIBILITY

139       CREATE  TABLE  AS  conforms to the SQL standard. The following are non‐
140       standard extensions:
141
142       · The standard requires parentheses  around  the  subquery  clause;  in
143         PostgreSQL, these parentheses are optional.
144
145       · In  the  standard,  the WITH [ NO ] DATA clause is required; in Post‐
146         greSQL it is optional.
147
148       · PostgreSQL handles temporary tables in a way  rather  different  from
149         the standard; see CREATE TABLE [create_table(7)] for details.
150
151       · The WITH clause is a PostgreSQL extension; neither storage parameters
152         nor OIDs are in the standard.
153
154       · The PostgreSQL concept of tablespaces is not part  of  the  standard.
155         Hence, the clause TABLESPACE is an extension.
156

SEE ALSO

158       CREATE    TABLE   [create_table(7)],   EXECUTE   [execute(7)],   SELECT
159       [select(7)], SELECT INTO [select_into(7)], VALUES [values(7)]
160
161
162
163SQL - Language Statements         2014-02-17                CREATE TABLE AS(7)
Impressum