SELECT INTO
SELECT INTO — define a new table from the results of a query
Synopsis
[ WITH [ RECURSIVE ]with_query
[, ...] ] SELECT [ ALL | DISTINCT [ ON (expression
[, ...] ) ] ] * |expression
[ [ AS ]output_name
] [, ...] INTO [ TEMPORARY | TEMP | UNLOGGED ] [ TABLE ]new_table
[ FROMfrom_item
[, ...] ] [ WHEREcondition
] [ GROUP BYexpression
[, ...] ] [ HAVINGcondition
] [ WINDOWwindow_name
AS (window_definition
) [, ...] ] [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ]select
] [ ORDER BYexpression
[ ASC | DESC | USINGoperator
] [ NULLS { FIRST | LAST } ] [, ...] ] [ LIMIT {count
| ALL } ] [ OFFSETstart
[ ROW | ROWS ] ] [ FETCH { FIRST | NEXT } [count
] { ROW | ROWS } ONLY ] [ FOR { UPDATE | SHARE } [ OFtable_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
orTEMP
If specified, the table is created as a temporary table. Refer to CREATE TABLE for details.
UNLOGGED
If specified, the table is created as an unlogged 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
.
To add OIDs to the table created by SELECT INTO
, enable the default_with_oids configuration variable. 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 33) and PL/pgSQL (see Chapter 40). 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.