SELECT INTO(l)SQL - Language Statements (2002-11-22SELECT INTO(l)
NAME
SELECT INTO - create a new table from the results of a query
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 ] [, ...] ]
[ LIMIT { count | ALL } ]
[ OFFSET start ]
[ FOR UPDATE [ OF tablename [, ...] ] ]
INPUTS
TEMPORARY
TEMP If specified, the table is created as a temporary
table. Refer to CREATE TABLE [create_table(l)] for
details.
new_table
The name (optionally schema-qualified) of the table to
be created.
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 data types 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(1), because they
interpret the INTO clause differently.
Page 1 (printed 3/24/03)
SELECT INTO(l)SQL - Language Statements (2002-11-22SELECT INTO(l)
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(1). 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.)
Page 2 (printed 3/24/03)
