PostgreSQL Insert Into Select

Introduction

When you’re managing data in PostgreSQL, you may encounter situations where you need to copy data from one table into another table. Normally, you’d use the INSERT INTO statement to add new rows to a table, adding the VALUES clause to specify which data to insert. There’s another way to insert data into a table– the INSERT INTO SELECT statement. Instead of specifying values to insert, the inserted data will come from another table as the result of a SELECT statement. In this article, we’ll learn more about the PostgreSQL INSERT INTO SELECT statement and see how it can be used to copy table data into another target table.

Prerequisites

Before attempting any of the examples in this article, make sure you have PostgreSQL installed on your computer.

Insert Into Select Tutorial

Let’s start by creating two tables that we can use in our examples. These tables will be named student and scholar. In our examples, we will copy data from the student table to the scholar table

Here’s the contents of the student table. We’ll copy this data to insert it into the scholar table:

Shown below is the scholar table. We’ll be inserting our copied data into this table:

Let’s check out an example where we copy data from one table to another. We’ll insert a student named ‘Wayne Rosing’ into the scholar table:

If you wanted to copy all of the columns from a row of the table, you can just use the asterisk ( * ):

NOTE: We need to use the WHERE clause to specify which values we are targeting to copy, otherwise we’ll end up copying all the values in the table.

At this point, our scholar table will now have a result like the one shown below. We can now see ‘Wayne Rossing’ in the table:

Here’s another example where we insert two or more students that have a grade of ’92’:

We can see that two rows are now added to our scholar table:

You can just remove the WHERE clause if you want to copy all the data:

We now have all the data in our student table inserted in our scholar table:

NOTE: If you’re planning to copy all the values from a table, make sure they don’t already exist in the target table.

Conclusion

If you need to copy data from one table into a target table, the PostgreSQL INSERT INTO SELECT statement is an easy way to get the job done. With this statement, you can choose to copy and insert just a single row or even all the rows of a table. In this tutorial, we looked at some typical examples of the PostgreSQL INSERT INTO SELECT statement. After following along with these examples, you’ll be able to perform this type of INSERT statement in your own PostgreSQL database operations.