select_into(7)

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

NAME

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

SYNOPSIS

10       [ WITH [ RECURSIVE ] with_query [, ...] ]
11       SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
12           * | expression [ [ AS ] output_name ] [, ...]
13           INTO [ TEMPORARY | TEMP ] [ TABLE ] new_table
14           [ FROM from_item [, ...] ]
15           [ WHERE condition ]
16           [ GROUP BY expression [, ...] ]
17           [ HAVING condition [, ...] ]
18           [ WINDOW window_name AS ( window_definition ) [, ...] ]
19           [ { UNION | INTERSECT | EXCEPT } [ ALL ] select ]
20           [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
21           [ LIMIT { count | ALL } ]
22           [ OFFSET start [ ROW | ROWS ] ]
23           [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
24           [ FOR { UPDATE | SHARE } [ OF table_name [, ...] ] [ NOWAIT ] [...] ]
25
26

DESCRIPTION

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

PARAMETERS

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

NOTES

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

EXAMPLES

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

COMPATIBILITY

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