SELECT INTO() SQL Commands SELECT INTO()NAME
SELECT INTO - Creates a new table from the results of a SELECT
SYNOPSIS
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 ] [, ...] ]
[ FOR UPDATE [ OF tablename [, ...] ] ]
[ LIMIT { count | ALL } [ { OFFSET | , } start ]]
where from_item can be:
[ ONLY ] table_name [ * ]
[ [ AS ] alias [ ( column_alias_list ) ] ]
|
( select )
[ AS ] alias [ ( column_alias_list ) ]
|
from_item [ NATURAL ] join_type from_item
[ ON join_condition | USING ( join_column_list ) ]
INPUTS
TEMPORARY
TEMP If TEMPORARY or TEMP is specified, the output table is created
only within this session, and is automatically dropped on ses‐
sion exit. Existing permanent tables with the same name are not
visible (in this session) while the temporary table exists. Any
indexes created on a temporary table are automatically temporary
as well.
new_table
The name of the new table to be created. This table must not
already exist. However, a temporary table can be created that
has the same name as an existing permanent table.
All other inputs are described in detail for SELECT [select(l)].
OUTPUTS
Refer to CREATE TABLE [create_table(l)] and SELECT [select(l)] for a
summary of possible output messages.
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 datatypes associated
with the output columns of the SELECT.
Note: CREATE TABLE AS [create_table_as(l)] is functionally
equivalent to SELECT INTO. CREATE TABLE AS is the recommended
syntax, since SELECT INTO is not standard. In fact, this form of
SELECT INTO is not available in PL/pgSQL or ecpg, because they
interpret the INTO clause differently.
COMPATIBILITY
SQL92 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 PL/pgSQL and ecpg. The Postgres 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.)
SQL - Language Statements 29 March 2001 SELECT INTO()