diff options
Diffstat (limited to 'doc/src/sgml/man7/CREATE_TABLE_AS.7')
| -rw-r--r-- | doc/src/sgml/man7/CREATE_TABLE_AS.7 | 320 |
1 files changed, 320 insertions, 0 deletions
diff --git a/doc/src/sgml/man7/CREATE_TABLE_AS.7 b/doc/src/sgml/man7/CREATE_TABLE_AS.7 new file mode 100644 index 0000000..f808702 --- /dev/null +++ b/doc/src/sgml/man7/CREATE_TABLE_AS.7 @@ -0,0 +1,320 @@ +'\" t +.\" Title: CREATE TABLE AS +.\" Author: The PostgreSQL Global Development Group +.\" Generator: DocBook XSL Stylesheets vsnapshot <http://docbook.sf.net/> +.\" Date: 2023 +.\" Manual: PostgreSQL 15.5 Documentation +.\" Source: PostgreSQL 15.5 +.\" Language: English +.\" +.TH "CREATE TABLE AS" "7" "2023" "PostgreSQL 15.5" "PostgreSQL 15.5 Documentation" +.\" ----------------------------------------------------------------- +.\" * Define some portability stuff +.\" ----------------------------------------------------------------- +.\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ +.\" http://bugs.debian.org/507673 +.\" http://lists.gnu.org/archive/html/groff/2009-02/msg00013.html +.\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ +.ie \n(.g .ds Aq \(aq +.el .ds Aq ' +.\" ----------------------------------------------------------------- +.\" * set default formatting +.\" ----------------------------------------------------------------- +.\" disable hyphenation +.nh +.\" disable justification (adjust text to left margin only) +.ad l +.\" ----------------------------------------------------------------- +.\" * MAIN CONTENT STARTS HERE * +.\" ----------------------------------------------------------------- +.SH "NAME" +CREATE_TABLE_AS \- define a new table from the results of a query +.SH "SYNOPSIS" +.sp +.nf +CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] \fItable_name\fR + [ (\fIcolumn_name\fR [, \&.\&.\&.] ) ] + [ USING \fImethod\fR ] + [ WITH ( \fIstorage_parameter\fR [= \fIvalue\fR] [, \&.\&.\&. ] ) | WITHOUT OIDS ] + [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ] + [ TABLESPACE \fItablespace_name\fR ] + AS \fIquery\fR + [ WITH [ NO ] DATA ] +.fi +.SH "DESCRIPTION" +.PP +\fBCREATE TABLE AS\fR +creates a table and fills it with data computed by a +\fBSELECT\fR +command\&. The table columns have the names and data types associated with the output columns of the +\fBSELECT\fR +(except that you can override the column names by giving an explicit list of new column names)\&. +.PP +\fBCREATE TABLE AS\fR +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 +\fBSELECT\fR +statement whenever it is queried\&. +.PP +\fBCREATE TABLE AS\fR +requires +CREATE +privilege on the schema used for the table\&. +.SH "PARAMETERS" +.PP +GLOBAL or LOCAL +.RS 4 +Ignored for compatibility\&. Use of these keywords is deprecated; refer to +CREATE TABLE (\fBCREATE_TABLE\fR(7)) +for details\&. +.RE +.PP +TEMPORARY or TEMP +.RS 4 +If specified, the table is created as a temporary table\&. Refer to +CREATE TABLE (\fBCREATE_TABLE\fR(7)) +for details\&. +.RE +.PP +UNLOGGED +.RS 4 +If specified, the table is created as an unlogged table\&. Refer to +CREATE TABLE (\fBCREATE_TABLE\fR(7)) +for details\&. +.RE +.PP +IF NOT EXISTS +.RS 4 +Do not throw an error if a relation with the same name already exists; simply issue a notice and leave the table unmodified\&. +.RE +.PP +\fItable_name\fR +.RS 4 +The name (optionally schema\-qualified) of the table to be created\&. +.RE +.PP +\fIcolumn_name\fR +.RS 4 +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\&. +.RE +.PP +USING \fImethod\fR +.RS 4 +This optional clause specifies the table access method to use to store the contents for the new table; the method needs be an access method of type +TABLE\&. See +Chapter\ \&63 +for more information\&. If this option is not specified, the default table access method is chosen for the new table\&. See +default_table_access_method +for more information\&. +.RE +.PP +WITH ( \fIstorage_parameter\fR [= \fIvalue\fR] [, \&.\&.\&. ] ) +.RS 4 +This clause specifies optional storage parameters for the new table; see +Storage Parameters +in the +CREATE TABLE (\fBCREATE_TABLE\fR(7)) +documentation for more information\&. For backward\-compatibility the +WITH +clause for a table can also include +OIDS=FALSE +to specify that rows of the new table should contain no OIDs (object identifiers), +OIDS=TRUE +is not supported anymore\&. +.RE +.PP +WITHOUT OIDS +.RS 4 +This is backward\-compatible syntax for declaring a table +WITHOUT OIDS, creating a table +WITH OIDS +is not supported anymore\&. +.RE +.PP +ON COMMIT +.RS 4 +The behavior of temporary tables at the end of a transaction block can be controlled using +ON COMMIT\&. The three options are: +.PP +PRESERVE ROWS +.RS 4 +No special action is taken at the ends of transactions\&. This is the default behavior\&. +.RE +.PP +DELETE ROWS +.RS 4 +All rows in the temporary table will be deleted at the end of each transaction block\&. Essentially, an automatic +\fBTRUNCATE\fR +is done at each commit\&. +.RE +.PP +DROP +.RS 4 +The temporary table will be dropped at the end of the current transaction block\&. +.RE +.RE +.PP +TABLESPACE \fItablespace_name\fR +.RS 4 +The +\fItablespace_name\fR +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\&. +.RE +.PP +\fIquery\fR +.RS 4 +A +\fBSELECT\fR, +\fBTABLE\fR, or +\fBVALUES\fR +command, or an +\fBEXECUTE\fR +command that runs a prepared +\fBSELECT\fR, +\fBTABLE\fR, or +\fBVALUES\fR +query\&. +.RE +.PP +WITH [ NO ] DATA +.RS 4 +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\&. +.RE +.SH "NOTES" +.PP +This command is functionally similar to +SELECT INTO (\fBSELECT_INTO\fR(7)), but it is preferred since it is less likely to be confused with other uses of the +\fBSELECT INTO\fR +syntax\&. Furthermore, +\fBCREATE TABLE AS\fR +offers a superset of the functionality offered by +\fBSELECT INTO\fR\&. +.SH "EXAMPLES" +.PP +Create a new table +films_recent +consisting of only recent entries from the table +films: +.sp +.if n \{\ +.RS 4 +.\} +.nf +CREATE TABLE films_recent AS + SELECT * FROM films WHERE date_prod >= \*(Aq2002\-01\-01\*(Aq; +.fi +.if n \{\ +.RE +.\} +.PP +To copy a table completely, the short form using the +TABLE +command can also be used: +.sp +.if n \{\ +.RS 4 +.\} +.nf +CREATE TABLE films2 AS + TABLE films; +.fi +.if n \{\ +.RE +.\} +.PP +Create a new temporary table +films_recent, consisting of only recent entries from the table +films, using a prepared statement\&. The new table will be dropped at commit: +.sp +.if n \{\ +.RS 4 +.\} +.nf +PREPARE recentfilms(date) AS + SELECT * FROM films WHERE date_prod > $1; +CREATE TEMP TABLE films_recent ON COMMIT DROP AS + EXECUTE recentfilms(\*(Aq2002\-01\-01\*(Aq); +.fi +.if n \{\ +.RE +.\} +.SH "COMPATIBILITY" +.PP +\fBCREATE TABLE AS\fR +conforms to the +SQL +standard\&. The following are nonstandard extensions: +.sp +.RS 4 +.ie n \{\ +\h'-04'\(bu\h'+03'\c +.\} +.el \{\ +.sp -1 +.IP \(bu 2.3 +.\} +The standard requires parentheses around the subquery clause; in +PostgreSQL, these parentheses are optional\&. +.RE +.sp +.RS 4 +.ie n \{\ +\h'-04'\(bu\h'+03'\c +.\} +.el \{\ +.sp -1 +.IP \(bu 2.3 +.\} +In the standard, the +WITH [ NO ] DATA +clause is required; in PostgreSQL it is optional\&. +.RE +.sp +.RS 4 +.ie n \{\ +\h'-04'\(bu\h'+03'\c +.\} +.el \{\ +.sp -1 +.IP \(bu 2.3 +.\} +PostgreSQL +handles temporary tables in a way rather different from the standard; see +CREATE TABLE (\fBCREATE_TABLE\fR(7)) +for details\&. +.RE +.sp +.RS 4 +.ie n \{\ +\h'-04'\(bu\h'+03'\c +.\} +.el \{\ +.sp -1 +.IP \(bu 2.3 +.\} +The +WITH +clause is a +PostgreSQL +extension; storage parameters are not in the standard\&. +.RE +.sp +.RS 4 +.ie n \{\ +\h'-04'\(bu\h'+03'\c +.\} +.el \{\ +.sp -1 +.IP \(bu 2.3 +.\} +The +PostgreSQL +concept of tablespaces is not part of the standard\&. Hence, the clause +TABLESPACE +is an extension\&. +.RE +.SH "SEE ALSO" +CREATE MATERIALIZED VIEW (\fBCREATE_MATERIALIZED_VIEW\fR(7)), CREATE TABLE (\fBCREATE_TABLE\fR(7)), \fBEXECUTE\fR(7), \fBSELECT\fR(7), SELECT INTO (\fBSELECT_INTO\fR(7)), \fBVALUES\fR(7) |