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.

UNLOGGED

If specified, the table is created as an unlogged table.
Refer to CREATE TABLE
for details.

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.