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
] [, ...] ]
[ 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 associated 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
for details.
-
new_table
-
The name (optionally schema-qualified) of the table to be created.
All other parameters are described in detail under
SELECT
.
Notes
CREATE TABLE AS
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 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 Chapter 30) and PL/pgSQL (see Chapter 36). The PostgreSQL usage of SELECT INTO to represent table creation is historical. It is best to use CREATE TABLE AS for this purpose in new code.
