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:

1
2
3
4
5
6
7
8
9
10
11
 id  |       name       | grade
-----+------------------+-------
  32 | Allen James      |    89
 371 | Jerry Lawrel     |    92
 536 | Tommy Jones      |    85
 312 | Michelle Rogers  |    85
 162 | Kelly Ripa       |    87
  51 | Fred Smith       |    89
 322 | Alfred Roosevelt |    89
 481 | Wayne Rosing     |    91
 261 | Samuel Davis     |    92

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

1
2
3
4
5
6
7
 id  |     name      | grade
-----+---------------+-------
 146 | Joan Rivers   |    91
 621 | Philip Wilson |    95
 782 | Jay Leno      |    93
 361 | MAX Heindel   |    97
 891 | Gerry Starr   |    94

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:

1
2
INSERT INTO scholar(id, name, grade)
SELECT id, name, grade  FROM student WHERE id = 481;

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

1
2
INSERT INTO scholar(id, name, grade)
SELECT *  FROM student WHERE id = 481;

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:

1
2
3
4
5
6
7
8
 id  |     name      | grade
-----+---------------+-------
 146 | Joan Rivers   |    91
 621 | Philip Wilson |    95
 782 | Jay Leno      |    93
 361 | MAX Heindel   |    97
 891 | Gerry Starr   |    94
 481 | Wayne Rosing  |    91

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

1
2
INSERT INTO scholar(id, name, grade)
SELECT *  FROM student WHERE grade = 92;

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

1
2
3
4
5
6
7
8
9
10
 id  |     name      | grade
-----+---------------+-------
 146 | Joan Rivers   |    91
 621 | Philip Wilson |    95
 782 | Jay Leno      |    93
 361 | MAX Heindel   |    97
 891 | Gerry Starr   |    94
 481 | Wayne Rosing  |    91
 371 | Jerry Lawrel  |    92
 261 | Samuel Davis  |    92

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

1
2
INSERT INTO scholar
SELECT *  FROM student;

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 id  |     name         | grade
-----+------------------+-------
 146 | Joan Rivers      |    91
 621 | Philip Wilson    |    95
 782 | Jay Leno         |    93
 361 | MAX Heindel      |    97
 891 | Gerry Starr      |    94
 481 | Wayne Rosing     |    91
 371 | Jerry Lawrel     |    92
 261 | Samuel Davis     |    92
  32 | Allen James      |    89
 536 | Tommy Jones      |    85
 312 | Michelle Rogers  |    85
 162 | Kelly Ripa       |    87
  51 | Fred Smith       |    89
 322 | Alfred Roosevelt |    89

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.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.