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