select_into(7)

1SELECT INTO()                    SQL Commands                    SELECT INTO()
2
3
4

NAME

6       SELECT INTO - define a new table from the results of a query
7
8

SYNOPSIS

10       SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
11           * | expression [ AS output_name ] [, ...]
12           INTO [ TEMPORARY | TEMP ] [ TABLE ] new_table
13           [ FROM from_item [, ...] ]
14           [ WHERE condition ]
15           [ GROUP BY expression [, ...] ]
16           [ HAVING condition [, ...] ]
17           [ { UNION | INTERSECT | EXCEPT } [ ALL ] select ]
18           [ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]
19           [ LIMIT { count | ALL } ]
20           [ OFFSET start ]
21           [ FOR { UPDATE | SHARE } [ OF table_name [, ...] ] [ NOWAIT ] [...] ]
22
23

DESCRIPTION

25       SELECT  INTO  creates  a new table and fills it with data computed by a
26       query. The data is not returned to the client, as it is with  a  normal
27       SELECT.  The  new table's columns have the names and data types associ‐
28       ated with the output columns of the SELECT.
29

PARAMETERS

31       TEMPORARY or TEMP
32              If specified, the table is created as a temporary  table.  Refer
33              to CREATE TABLE [create_table(7)] for details.
34
35       new_table
36              The  name  (optionally schema-qualified) of the table to be cre‐
37              ated.
38
39       All other parameters are described in detail under SELECT [select(7)].
40

NOTES

42       CREATE TABLE AS [create_table_as(7)] is functionally similar to  SELECT
43       INTO.  CREATE  TABLE  AS  is the recommended syntax, since this form of
44       SELECT INTO is not available in ECPG or PL/pgSQL, because  they  inter‐
45       pret the INTO clause differently. Furthermore, CREATE TABLE AS offers a
46       superset of the functionality provided by SELECT INTO.
47
48       Prior to PostgreSQL 8.1, the table created by SELECT INTO included OIDs
49       by  default.  In PostgreSQL 8.1, this is not the case — to include OIDs
50       in the new table, the default_with_oids configuration variable must  be
51       enabled.  Alternatively, CREATE TABLE AS can be used with the WITH OIDS
52       clause.
53

EXAMPLES

55       Create a new table films_recent consisting of only recent entries  from
56       the table films:
57
58       SELECT * INTO films_recent FROM films WHERE date_prod >= '2002-01-01';
59
60

COMPATIBILITY

62       The  SQL  standard  uses SELECT INTO to represent selecting values into
63       scalar variables of a host program, rather than creating a  new  table.
64       This  indeed  is the usage found in ECPG (see in the documentation) and
65       PL/pgSQL (see in the documentation).  The PostgreSQL  usage  of  SELECT
66       INTO  to represent table creation is historical. It is best to use CRE‐
67       ATE TABLE AS for this purpose in new code.
68