SELECT INTO

Practical PostgreSQL
Prev		Next

Synopsis

SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
    expression [ AS output_name ] [, ...]
    [ INTO [ TEMPORARY | TEMP ] [ TABLE ] new_table ]
    [ FROM from_item [, ...] ]
    [ WHERE condition ]
    [ GROUP BY expression [, ...] ]
    [ HAVING condition [, ...] ]
    [ { UNION | INTERSECT | EXCEPT [ALL] } select ]
    [ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]
    [ FOR UPDATE [ OF tablename [, ...] ] ]
    LIMIT { count | ALL } [ { OFFSET | , } start ]
  
   where from_item can be:
  
   [ONLY] table_name[*]
   [[AS] alias[(column_alias_list]] |
   select
   [[AS] alias[(column_alias_list] |
   from_item
   [NATURAL] join_typefrom_item
   [ ON join_condition | USING (join_column_list

Parameters

TEMPORARY, TEMP: Pass either of these keywords to indicate that the table to be created is temporary and will be destroyed when the session has ended.
new_table: Use this parameter to supply the name of the new table that will be created to hold the resulting rows of the query. This table will be created automatically and must not already exist before this command is executed.

All parameters are described in detail for SELECT.

Results

Refer to the CREATE TABLE and SELECT for a list of possible outputs.

Description

Use SELECT INTO to execute a query and use the resulting rows to populate a new (automatically created) table. All column names and data types are inserted into the new table with the rows resulting from the original query. This command is effectively the same as using the CREATE TABLE AS command, and it is recommended that you use that syntax, due to the fact that SELECT INTO is non-standard and is also not interpreted correctly by PL/pgSQL or ecpg.

Examples

The following example will create a temporary employee table for employees with an identification number below 200.

SELECT * INTO TEMP TABLE old_emp 
   FROM employees 
   WHERE emp_id < 200;