PostgreSQL offers many useful statements that are used to deal with the database tables effectively. One of the most crucial and frequently used statements is INSERT INTO SELECT. It performs two operations in one go, i.e., “fetch the data from one table” and insert the fetched data into the targeted table. Let’s have a look at how this statement works in PostgreSQL.

PostgreSQL: INSERT INTO ... (SELECT * ...)

The INSERT INTO SELECT command selects some specified data from one table and inserts/pastes it into another one. Now there are two important things that are to be noticed:

• First, the table/target table where the columns are being copied remains unchanged.
• Second, the datatype of columns of both the source and target tables should be the same.

We can use the following syntax of the INSERT INTO SELECT statement to select and insert the data from one table to another:

INSERT INTO tab_2
SELECT * FROM tab_1
WHERE cond;

The name of the table in which we want to insert/paste the data is written after the INSERT INTO command. SELECT statement is followed by the name of the table from which we want to copy/select data. We can also use the optional WHERE clause followed by the condition to copy/insert the data based on a specified condition.

If we want to select/copy some columns (partial data) from a table and insert them into another, the above syntax will become:

INSERT INTO table_2 (col1, col2, col3, ...)
SELECT col1, col2, col3, ...
FROM table_1
WHERE cond;

• Table_2 is the table where the data is being copied means the target table.
• Table_1 is the source table from where the data is being copied.

Example 1: Understanding INSERT INTO SELECT

Let’s discuss the concept with the help of an example so that it becomes more clear:

Step 1: Create Table 1 and Insert Some Data Into it

Create a table named students_info with columns StudentId, StudentName, Address, and City.

CREATE TABLE Students_Info
(
StudentID int,
StudentName varchar(255),
Address varchar(255),
City varchar(255)
);

A new table will be created like this:

Now insert data in the newly created “Students_Info” table:

INSERT INTO Students_info(studentid, studentname ,address,city)
VALUES ( 01,'John','13th Street. 47 W 13th St' , 'New York'),
 ( 02,'Alex','24th Street. 32 E 24th St' , 'San Diego'),
 ( 03,'Peter','6th Street. 23 W 6th St' , 'San Francisco')
RETURNING *;

The data is successfully inserted in the table like this:

Step 2: Create Second Table

Now create another table named “attendance_list”. The attendance list will only contain two columns i.e. studentid and studentname:

CREATE TABLE attendence_list
(
StudentID int,
StudentName int
);

A table will be created:

Step 3: Insert Data From Students_info to Attendence_list

Now we will use the INSERT INTO SELECT statement to select data i.e. the “studentId column” and the “studentName column” from the students_info table to the attendence_list table. The data of rows that are specified is being copied and inserted:

INSERT INTO attendence_list(StudentId,StudentName)
SELECT StudentId,StudentName
FROM students_info;
SELECT * FROM attendence_list;

The output will show that the data from table students_info is copied to attendence_list for the two columns i.e. studentId and studentName. Below attached is the screenshot of the query:

Example 2: INSERT INTO SELECT Statement With WHERE Clause

Now if we want to copy only data of those students who have studentId >2 (maybe to place them in another section), we will make use of the WHERE clause as follows:

INSERT INTO attendence_list(StudentId,StudentName)
SELECT StudentId,StudentName FROM students_info
WHERE students_info.studentid>2;

SELECT * FROM attendence_list;

The output table would return all the data or students having studentsId >2, copied for the Students_info table. The output will look like this:

Example 3: Incompatible Column Type Error

One thing is to take notice that the datatypes of columns of both tables must be the same. Let’s suppose I change the data type of studentName from varchar to int(which is not practically possible but just to illustrate this concept let's assume this) in the attendent_list table, the INSERT INTO SELECT will not work. Instead, it will give an error. Now let's do it practically:

CREATE TABLE attendence_list
(
StudentID int,
StudentName int
)
SELECT * FROM attendence_list;

A table is created as it is normally created:

Now try to copy data from students_info to attendence_list, let's see what would happen:

INSERT INTO attendence_list(StudentId,StudentName)
SELECT StudentId,StudentName FROM students_info;

SELECT * FROM attendence_list;

The output for the above query is:

As expected, this query gave us an error so we need to have the same datatype of columns in both the tables.

Conclusion

INSERT INTO SELECT is a very useful statement in PostgreSQL which allows us to select or copy some data from one table and insert it into another. This technique saves time from manually inserting all the values again. So if we have an already existing table, we can copy the data from that table to another given that the data type of columns of both the tables i.e. source and the target are the same.