SELECT INTO
SQL - Language Statements
SELECT INTO
create a new table from the results of a query
SELECT INTO
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
* | expression [ AS output_name ] [, ...]
INTO [ TEMPORARY | TEMP ] [ TABLE ] new_table
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ GROUP BY expression [, ...] ]
[ HAVING condition [, ...] ]
[ { UNION | INTERSECT | EXCEPT } [ ALL ] select ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]
[ LIMIT { count | ALL } ]
[ OFFSET start ]
[ FOR UPDATE [ OF tablename [, ...] ] ]
Description
SELECT INTO 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 SELECT. The new
table's columns have the names and data types associated with the
output columns of the SELECT.
Parameters
TEMPORARY or TEMP
If specified, the table is created as a temporary table. Refer
to for details.
new_table
The name (optionally schema-qualified) of the table to be created.
All other parameters are described in detail under .
Notes
is functionally similar to
SELECT INTO. CREATE TABLE AS
is the recommended syntax, since this form of SELECT
INTO is not available in ECPG
or PL/pgSQL, because they interpret the
INTO clause differently. Furthermore,
CREATE TABLE AS offers a superset of the
functionality provided by SELECT INTO.
Prior to PostgreSQL> 7.5, the table created by
SELECT INTO always included OIDs. Furthermore,
these OIDs were newly generated: they were distinct from the OIDs
of any of the rows in the source tables of the SELECT
INTO statement. Therefore, if SELECT
INTO was frequently executed, the OID counter would be
rapidly incremented. As of PostgreSQL> 7.5, the
inclusion of OIDs in the table created by SELECT
INTO is controlled by the
default_with_oids configuration variable. This
variable currently defaults to true, but will likely default to
false in a future release of PostgreSQL>.
Compatibility
The SQL standard uses SELECT INTO 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 ) and
PL/pgSQL (see ).
The PostgreSQL usage of SELECT
INTO to represent table creation is historical. It's
best to use CREATE TABLE AS for this purpose in
new code. (CREATE TABLE AS isn't standard
either, but it's less likely to cause confusion.)