In PostgreSQL, the SELECT INTO statement performs various functionalities in one go. The stated command creates a new table, copies data from the original table, and pastes it into the newly created table. The newly created table will have the same structure as the original/selected table. Using the SELECT INTO command a partial, or a complete table can be copied or duplicated.

This post explains how to use the Postgres SELECT INTO command to copy data from the selected table into a new temporary or regular table.

PostgreSQL SELECT INTO Statement

The “SELECT INTO” statement can be used as an alternative to the CREATE TABLE AS statement. Since both commands let us create a new table based on some other table.

Here is the syntax of the SELECT INTO statement:

SELECT col_list
INTO [ TEMPORARY | TEMP | UNLOGGED ] [ TABLE ] new_tab_name
FROM tab_name
WHERE condition;

In this syntax:

- The “col_list” represents the columns to be selected from the original table.
- “TEMPORARY”, “TEMP”, “UNLOGGED”, and “TABLE” keywords are used to define a new temporary, unlogged, or regular table.
- The “WHERE” clause lets us specify particular criteria for copying the data from the actual table to the newly created table.

Different clauses can be used with the “SELECT INTO” statement to perform different operations on the tables, such as the WHERE, INNER JOIN, GROUP BY, etc.

How Does the SELECT INTO Statement Work in Postgres?

The working of the SELECT INTO statement is illustrated below:

- First, it selects or fetches the data from the original table.
- Next, it creates a new table or temporary table.
- Finally, it inserts the selected data from the original table and puts it into the newly created table.

Let’s understand the SELECT INTO statement via the following examples.

Example 1: Copying Data to Regular Postgres Table

We have already created a table with the following records:

SELECT * FROM emp_details;

Now, utilize the SELECT INTO command to copy the “emp_id” and “emp_name” columns of the “emp_info” table into a new table named “emp_info_copy”:

SELECT emp_id, emp_name
INTO TABLE emp_info_copy
FROM emp_info;

To verify the working of the SELECT INTO command, use the following command:

SELECT * FROM emp_info_copy;

The output proves that the selected records have been copied into the “emp_info_copy” table.

Example 2: Copying Data to a Temporary Table

In the following code, we will utilize a TEMP keyword with the SELECT INTO command to copy the data from the “emp_info” table into a newly created temporary table:

SELECT *
INTO TEMP TABLE emp_temp_table
FROM emp_info;

Execute the below command to confirm the table’s duplication:

SELECT * FROM emp_temp_table;

The output states that ten records have been selected from the “emp_info” table and inserted into the “emp_temp_table”.

Conclusion

In PostgreSQL, the SELECT INTO statement creates a new table, copies data from the original table, and pastes it into the newly created table. The newly created table will have a similar structure to the actual table. Using the SELECT INTO command a partial, or a complete table can be copied. Different clauses can be used with the “SELECT INTO” statement to perform different operations on the tables, such as the WHERE, INNER JOIN, GROUP BY, etc. This post explained different use cases of the SELECT INTO statement in PostgreSQL.