1CREATE TABLE AS(7)       PostgreSQL 13.3 Documentation      CREATE TABLE AS(7)
2
3
4

NAME

6       CREATE_TABLE_AS - define a new table from the results of a query
7

SYNOPSIS

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

DESCRIPTION

19       CREATE TABLE AS creates a table and fills it with data computed by a
20       SELECT command. The table columns have the names and data types
21       associated with the output columns of the SELECT (except that you can
22       override the column names by giving an explicit list of new column
23       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. Use of these keywords is deprecated;
34           refer to CREATE TABLE (CREATE_TABLE(7)) for details.
35
36       TEMPORARY or TEMP
37           If specified, the table is created as a temporary table. Refer to
38           CREATE TABLE (CREATE_TABLE(7)) for details.
39
40       UNLOGGED
41           If specified, the table is created as an unlogged table. Refer to
42           CREATE TABLE (CREATE_TABLE(7)) for details.
43
44       IF NOT EXISTS
45           Do not throw an error if a relation with the same name already
46           exists. A notice is issued in this case. Refer to CREATE TABLE
47           (CREATE_TABLE(7)) for details.
48
49       table_name
50           The name (optionally schema-qualified) of the table to be created.
51
52       column_name
53           The name of a column in the new table. If column names are not
54           provided, they are taken from the output column names of the query.
55
56       USING method
57           This optional clause specifies the table access method to use to
58           store the contents for the new table; the method needs be an access
59           method of type TABLE. See Chapter 60 for more information. If this
60           option is not specified, the default table access method is chosen
61           for the new table. See default_table_access_method for more
62           information.
63
64       WITH ( storage_parameter [= value] [, ... ] )
65           This clause specifies optional storage parameters for the new
66           table; see Storage Parameters in the CREATE TABLE (CREATE_TABLE(7))
67           documentation for more information. For backward-compatibility the
68           WITH clause for a table can also include OIDS=FALSE to specify that
69           rows of the new table should contain no OIDs (object identifiers),
70           OIDS=TRUE is not supported anymore.
71
72       WITHOUT OIDS
73           This is backward-compatible syntax for declaring a table WITHOUT
74           OIDS, creating a table WITH OIDS is not supported anymore.
75
76       ON COMMIT
77           The behavior of temporary tables at the end of a transaction block
78           can be controlled using ON COMMIT. The three options are:
79
80           PRESERVE ROWS
81               No special action is taken at the ends of transactions. This is
82               the default behavior.
83
84           DELETE ROWS
85               All rows in the temporary table will be deleted at the end of
86               each transaction block. Essentially, an automatic TRUNCATE(7)
87               is done at each commit.
88
89           DROP
90               The temporary table will be dropped at the end of the current
91               transaction block.
92
93       TABLESPACE tablespace_name
94           The tablespace_name is the name of the tablespace in which the new
95           table is to be created. If not specified, default_tablespace is
96           consulted, or temp_tablespaces if the table is temporary.
97
98       query
99           A SELECT(7), TABLE, or VALUES(7) command, or an EXECUTE(7) command
100           that runs a prepared SELECT, TABLE, or VALUES query.
101
102       WITH [ NO ] DATA
103           This clause specifies whether or not the data produced by the query
104           should be copied into the new table. If not, only the table
105           structure is copied. The default is to copy the data.
106

NOTES

108       This command is functionally similar to SELECT INTO (SELECT_INTO(7)),
109       but it is preferred since it is less likely to be confused with other
110       uses of the SELECT INTO syntax. Furthermore, CREATE TABLE AS offers a
111       superset of the functionality offered by SELECT INTO.
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       To copy a table completely, the short form using the TABLE command can
121       also be used:
122
123           CREATE TABLE films2 AS
124             TABLE films;
125
126       Create a new temporary table films_recent, consisting of only recent
127       entries from the table films, using a prepared statement. The new table
128       will be dropped at commit:
129
130           PREPARE recentfilms(date) AS
131             SELECT * FROM films WHERE date_prod > $1;
132           CREATE TEMP TABLE films_recent ON COMMIT DROP AS
133             EXECUTE recentfilms('2002-01-01');
134

COMPATIBILITY

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

SEE ALSO

155       CREATE MATERIALIZED VIEW (CREATE_MATERIALIZED_VIEW(7)), CREATE TABLE
156       (CREATE_TABLE(7)), EXECUTE(7), SELECT(7), SELECT INTO (SELECT_INTO(7)),
157       VALUES(7)
158
159
160
161PostgreSQL 13.3                      2021                   CREATE TABLE AS(7)
Impressum