PostgreSQL: Insert Values into Table from Select Query

Often in PostgreSQL you may want to insert values into a table that are returned from a SELECT query.

You can use the following syntax to do so:

INSERT INTO athletes2
SELECT * FROM athletes1 WHERE team = 'Rockets';

This particular example will select all rows from the athletes1 table where the team column is equal to ‘Rockets’ and then insert each of these rows into the athletes2 table.

The following example shows how to use this syntax in practice.

Example: Insert Values into Table from Select Query in PostgreSQL

Suppose that we create the following table named athletes1 that contains information about various basketball players:

-- create table named 'products'
CREATE TABLE athletes1 (
  id INTEGER PRIMARY KEY,
  team TEXT,
  points INTEGER
);

-- add rows to the table 
INSERT INTO athletes1 VALUES (0001, 'Mavs', 22);
INSERT INTO athletes1 VALUES (0002, 'Spurs', 30);
INSERT INTO athletes1 VALUES (0003, 'Rockets', 12);
INSERT INTO athletes1 VALUES (0004, 'Nuggets', 25);
INSERT INTO athletes1 VALUES (0005, 'Rockets', 31);

-- view all rows in table
SELECT * FROM athletes1;

 id |  team   | points 
----+---------+--------
  1 | Mavs    |     22
  2 | Spurs   |     30
  3 | Rockets |     12
  4 | Nuggets |     25
  5 | Rockets |     31

And suppose that we create another table named athletes2 that contains information about additional basketball players:

-- create table named 'athletes2'
CREATE TABLE athletes2 (
  id INTEGER PRIMARY KEY,
  team TEXT,
  points INTEGER
);

-- add rows to the table 
INSERT INTO athletes2 VALUES (0006, 'Celtics', 35);
INSERT INTO athletes2 VALUES (0007, 'Heat', 22);
INSERT INTO athletes2 VALUES (0008, 'Knicks', 17);
INSERT INTO athletes2 VALUES (0009, 'Celtics', 14);
INSERT INTO athletes2 VALUES (0010, 'Magic', 30);

-- view all rows in table
SELECT * FROM athletes2;

 id |  team   | points 
----+---------+--------
  6 | Celtics |     35
  7 | Heat    |     22
  8 | Knicks  |     17
  9 | Celtics |     14
 10 | Magic   |     30

Suppose that we would like to select each row from the athletes1 table where the team column is equal to ‘Rockets’ and then insert each of these rows into the athletes2 table.

We can use the following syntax to do so:

INSERT INTO athletes2
SELECT * FROM athletes1 WHERE team = 'Rockets';

 id |  team   | points 
----+---------+--------
  6 | Celtics |     35
  7 | Heat    |     22
  8 | Knicks  |     17
  9 | Celtics |     14
 10 | Magic   |     30
  3 | Rockets |     12
  5 | Rockets |     31

We can see that each row that had a team value of ‘Rockets’ from the athletes1 table has been inserted into the athletes2 table.

You can use similar syntax to insert rows from a SELECT query into your own table in PostgreSQL.

Additional Resources

The following tutorials explain how to perform other common tasks in PostgreSQL:

PostgreSQL: How to INSERT If Not Exists Already
PostgreSQL: How to Use WHERE NOT EQUAL
PostgreSQL: How to Select the Last Row per Group