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 auto
matically dropped on session exit. Existing perma
nent tables with the same name are not visible (in
this session) while the temporary table exists.
Any indexes created on a temporary table are auto
matically 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.
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 func
tionally 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 cre
ating 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.)
Man(1) output converted with
man2html