SELECT INTO() SQL Commands SELECT INTO()
NAME
SELECT INTO - define 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 ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ LIMIT { count | ALL } ]
[ OFFSET start ]
[ FOR { UPDATE | SHARE } [ OF table_name [, ...] ] [ NOWAIT ] [...] ]
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 associ-
ated with the output columns of the SELECT.
PARAMETERS
TEMPORARY or 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 cre-
ated.
All other parameters are described in detail under SELECT [select(l)].
NOTES
CREATE TABLE AS [create_table_as(l)] 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 inter-
pret the INTO clause differently. Furthermore, CREATE TABLE AS offers a
superset of the functionality provided by SELECT INTO.
Prior to PostgreSQL 8.1, the table created by SELECT INTO included OIDs
by default. In PostgreSQL 8.1, this is not the case -- to include OIDs
in the new table, the default_with_oids configuration variable must be
enabled. Alternatively, CREATE TABLE AS can be used with the WITH OIDS
clause.
EXAMPLES
Create a new table films_recent consisting of only recent entries from
the table films:
SELECT * INTO films_recent FROM films WHERE date_prod >= '2002-01-01';
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 in the documentation) and
PL/pgSQL (see in the documentation). The PostgreSQL usage of SELECT
INTO to represent table creation is historical. It is best to use CRE-
ATE TABLE AS for this purpose in new code.
SEE ALSO
CREATE TABLE AS [create_table_as(l)]
SQL - Language Statements 2008-09-19 SELECT INTO()
postgresql 8.3.4 - Generated Sun Oct 5 06:29:49 CDT 2008