PostgreSQL - SELECT INTO Keyword

The PostgreSQL SELECT INTO keyword is used to create a table from an existing table by copying the existing table's columns. When creating a table in this way, the new table will be populated with the records from the existing table (based on the SELECT statement).

Syntax

The syntax for using SELECT INTO keyword in PostgreSQL is given below:

/* copy all columns into new_table */
SELECT *
INTO new_table
FROM old_table
WHERE condition;

/* copy selected columns into new_table */
SELECT column1, column2, column3, ...
INTO new_table
FROM old_table
WHERE condition;

Example:

Consider a database table called Employee with the following records:

• Copy all columns of a table: The below statement is used to copy all columns of Employee table into a new table called Employee_PT. The new table will contain all records of Employee table where the age of the employee is less than 28.

  SELECT * INTO Employee_PT 
  FROM Employee
  WHERE Age < 28;
  
  -- see the result
  SELECT * FROM Employee_PT;
  

  
  

  This will produce the result as shown below:

  EmpID	Name	City	Age	Salary
  1	John	London	25	3000
  2	Marry	New York	24	2750
  3	Jo	Paris	27	2800

• Copy selected columns of a table: It is also possible to copy selected columns when using SELECT INTO statement. In the example below, the columns which are copied are: EmpID, Name and City.

  SELECT EmpID, Name, City INTO Employee_PT 
  FROM Employee
  WHERE Age < 28;
  
  -- see the result
  SELECT * FROM Employee_PT;
  

  
  

  This will produce the result as shown below:

  EmpID	Name	City
  1	John	London
  2	Marry	New York
  3	Jo	Paris

• Using AS clause: The AS clause can be used to rename the column name. See the example below:

  SELECT EmpID, Name AS EmployeeName, City INTO Employee_PT 
  FROM Employee
  WHERE Age < 28;
  
  -- see the result
  SELECT * FROM Employee_PT;
  

  
  

  This will produce the result as shown below:

  EmpID	EmployeeName	City
  1	John	London
  2	Marry	New York
  3	Jo	Paris