Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions
Privacy Policy

  




 

 

Databases - Practical PostgreSQL
Previous Page Home Next Page

SELECT INTO

Name

SELECT INTO -- Construct a new table from the results of a SELECT.

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.

Results

Refer to CREATE TABLE" and SELECT" for a list of possible results.

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
Databases - Practical PostgreSQL
Previous Page Home Next Page

 
 
  Published under the terms of the Open Publication License Design by Interspire