SELECT INTO

Write a query and insert its results into a new table.

For details about the syntax, see SELECT.

Syntax

[ WITH name AS (subquery) [, ...] ]
SELECT [ ALL | DISTINCT expression [ [ AS ] output_name ] [, ...] ]
    [ * | expression [ [ AS ] output_name ] [, ...] ] 
    INTO [ TEMPORARY | TEMP ] [ TABLE ] new_table
    [ FROM from_item [, ...] ]
    [ WHERE condition ]
    [ GROUP BY { expression [, ...] ]|
      GROUPING SETS (expression [, ...]) |
      ROLLUP (expression [, ...]) |
      CUBE (expression [, ...]) } ]
    [ HAVING condition [, ...] ]
    [ WINDOW window_name AS ( window_definition ) [, ...] ]
    [ { UNION | INTERSECT | { EXCEPT | MINUS } [ ALL | DISTINCT ] select ]
    [ ORDER BY expression [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] [ COLLATE collation ] ]
    [ LIMIT { count | ALL } ]
    [ OFFSET start [ ROW | ROWS ] ]

See SELECT for details about the clauses in the SELECT INTO statement. The table that you select into must be a new temporary or persistent table.

For example:

premdb=# select * into season10 from match where seasonid=10;
SELECT 380
premdb=# \d season10
              Table "public.season10"
  Column  |            Type             | Modifiers 
----------+-----------------------------+-----------
 seasonid | smallint                    | 
 matchday | timestamp without time zone | 
 htid     | smallint                    | 
 atid     | smallint                    | 
 ftscore  | character(3)                | 
 htscore  | character(3)                | 

Distribution: Hash (seasonid)

In the following example, note the use of aliases for the name column. If these columns do not have distinct names, the SELECT INTO statement returns an error.

premdb=# SELECT season_name, matchday, h.name htname, a.name atname, ftscore 
into results 
FROM season s JOIN match m ON(m.seasonid=s.seasonid) 
JOIN hometeam h ON(m.htid=h.htid) JOIN awayteam a ON(m.atid=a.atid) 
WHERE h.name='Chelsea' AND season_name='2011-2012' ORDER BY matchday;
SELECT 19