1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
|
'\" t
.\" Title: SELECT INTO
.\" Author: The PostgreSQL Global Development Group
.\" Generator: DocBook XSL Stylesheets vsnapshot <http://docbook.sf.net/>
.\" Date: 2024
.\" Manual: PostgreSQL 16.3 Documentation
.\" Source: PostgreSQL 16.3
.\" Language: English
.\"
.TH "SELECT INTO" "7" "2024" "PostgreSQL 16.3" "PostgreSQL 16.3 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"
SELECT_INTO \- define a new table from the results of a query
.SH "SYNOPSIS"
.sp
.nf
[ WITH [ RECURSIVE ] \fIwith_query\fR [, \&.\&.\&.] ]
SELECT [ ALL | DISTINCT [ ON ( \fIexpression\fR [, \&.\&.\&.] ) ] ]
* | \fIexpression\fR [ [ AS ] \fIoutput_name\fR ] [, \&.\&.\&.]
INTO [ TEMPORARY | TEMP | UNLOGGED ] [ TABLE ] \fInew_table\fR
[ FROM \fIfrom_item\fR [, \&.\&.\&.] ]
[ WHERE \fIcondition\fR ]
[ GROUP BY \fIexpression\fR [, \&.\&.\&.] ]
[ HAVING \fIcondition\fR ]
[ WINDOW \fIwindow_name\fR AS ( \fIwindow_definition\fR ) [, \&.\&.\&.] ]
[ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] \fIselect\fR ]
[ ORDER BY \fIexpression\fR [ ASC | DESC | USING \fIoperator\fR ] [ NULLS { FIRST | LAST } ] [, \&.\&.\&.] ]
[ LIMIT { \fIcount\fR | ALL } ]
[ OFFSET \fIstart\fR [ ROW | ROWS ] ]
[ FETCH { FIRST | NEXT } [ \fIcount\fR ] { ROW | ROWS } ONLY ]
[ FOR { UPDATE | SHARE } [ OF \fItable_name\fR [, \&.\&.\&.] ] [ NOWAIT ] [\&.\&.\&.] ]
.fi
.SH "DESCRIPTION"
.PP
\fBSELECT INTO\fR
creates a new table and fills it with data computed by a query\&. The data is not returned to the client, as it is with a normal
\fBSELECT\fR\&. The new table\*(Aqs columns have the names and data types associated with the output columns of the
\fBSELECT\fR\&.
.SH "PARAMETERS"
.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
\fInew_table\fR
.RS 4
The name (optionally schema\-qualified) of the table to be created\&.
.RE
.PP
All other parameters are described in detail under
\fBSELECT\fR(7)\&.
.SH "NOTES"
.PP
\fBCREATE TABLE AS\fR
is functionally similar to
\fBSELECT INTO\fR\&.
\fBCREATE TABLE AS\fR
is the recommended syntax, since this form of
\fBSELECT INTO\fR
is not available in
ECPG
or
PL/pgSQL, because they interpret the
INTO
clause differently\&. Furthermore,
\fBCREATE TABLE AS\fR
offers a superset of the functionality provided by
\fBSELECT INTO\fR\&.
.PP
In contrast to
\fBCREATE TABLE AS\fR,
\fBSELECT INTO\fR
does not allow specifying properties like a table\*(Aqs access method with
USING \fImethod\fR
or the table\*(Aqs tablespace with
TABLESPACE \fItablespace_name\fR\&. Use
\fBCREATE TABLE AS\fR
if necessary\&. Therefore, the default table access method is chosen for the new table\&. See
default_table_access_method
for more information\&.
.SH "EXAMPLES"
.PP
Create a new table
films_recent
consisting of only recent entries from the table
films:
.sp
.if n \{\
.RS 4
.\}
.nf
SELECT * INTO films_recent FROM films WHERE date_prod >= \*(Aq2002\-01\-01\*(Aq;
.fi
.if n \{\
.RE
.\}
.SH "COMPATIBILITY"
.PP
The SQL standard uses
\fBSELECT INTO\fR
to represent selecting values into scalar variables of a host program, rather than creating a new table\&. This indeed is the usage found in
ECPG
(see
Chapter\ \&36) and
PL/pgSQL
(see
Chapter\ \&43)\&. The
PostgreSQL
usage of
\fBSELECT INTO\fR
to represent table creation is historical\&. Some other SQL implementations also use
\fBSELECT INTO\fR
in this way (but most SQL implementations support
\fBCREATE TABLE AS\fR
instead)\&. Apart from such compatibility considerations, it is best to use
\fBCREATE TABLE AS\fR
for this purpose in new code\&.
.SH "SEE ALSO"
CREATE TABLE AS (\fBCREATE_TABLE_AS\fR(7))
|
