Synopsis
SELECT [ ALL | DISTINCT [ ON (
distinct_expression
[, ...] ) ] ]
target_expression
[ AS
output_name
] [, ...]
[ INTO [ TEMPORARY | TEMP ] [ TABLE ]
new_table
]
[ FROM
from_item
[ { , | CROSS JOIN } ...] ]
[ WHERE
condition
]
[ GROUP BY
aggregate_expression
[, ...] ]
[ HAVING
aggregate_condition
[, ...] ]
[ { UNION | INTERSECT | EXCEPT [ALL] }
select
]
[ ORDER BY
order_expression
[ ASC | DESC | USING
operator
] [, ...] ]
[ FOR UPDATE [ OF
update_table
[, ...] ] ]
[ LIMIT { ALL |
count
} [ { OFFSET | , }
start
] ]
from_item
::= { [ ONLY ]
table_name
[ * ]
[ [ AS ]
from_alias
[ (
column_alias_list
) ] ] |
(
select
) [ [ AS ]
alias
[ (
column_alias_list
) ] ] |
from_item
[ NATURAL ]
join_type
from_item
[ ON (
join_condition
) | USING (
join_column_list
) ]
}
join_type
::= [ INNER |
LEFT [ OUTER ] |
RIGHT [ OUTER ] |
FULL [ OUTER ]
] JOIN
Parameters
Most SELECT INTO parameters are the same as for the SELECT command. The following two are the only parameters unique to SELECT INTO:
-
TEMPORARY, TEMP
-
The TEMPORARY (or TEMP) keyword indicates that the table is for temporary use; it will be destroyed when the session has ended.
-
new_table
-
The name of the new table created to hold the resulting rows of the query. This table will be created automatically and must not already exist before you execute this command.
Description
Use SELECT INTO to execute a query and use the resulting rows to populate a new (automatically created) table. Each column's names and data type for the new table are derived from the rows resulting from the original query. This command is effectively the same as 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.
Example
The following example will create a temporary employee table for employees with an identification number below 105:
booktown=#
SELECT * INTO TEMP TABLE old_emp
booktown-#
FROM employees
booktown-#
WHERE id < 105;
SELECT
