Often in PostgreSQL you may want to upsert (update or insert if not exists) multiple rows into a table at once.
You can use the following syntax to do so:
INSERT INTO athletes2 (id, team, points)
SELECT id, team, points
FROM athletes1
ON CONFLICT (id) DO UPDATE SET
team = excluded.team,
points = excluded.points;
This particular example will upsert rows from the id, team and points columns of the athletes1 table into the athletes2 table.
If any rows from the athletes1 table already exist in the athletes2 table (based on a “conflict” in the id column) then the values for those rows in the athletes2 table will be updated for the team and points columns.
The following example shows how to use this syntax in practice.
Example: How to Upsert Multiple Rows in PostgreSQL
Suppose that we create the following table named athletes1 that contains information about various basketball players:
-- create table named 'athletes1'
CREATE TABLE athletes1 (
id INTEGER PRIMARY KEY,
team TEXT,
points INTEGER
);
-- add rows to the table
INSERT INTO athletes1 VALUES (0001, 'Mavs', 23);
INSERT INTO athletes1 VALUES (0002, 'Hawks', 12);
INSERT INTO athletes1 VALUES (0003, 'Rockets', 23);
INSERT INTO athletes1 VALUES (0004, 'Spurs', 29);
INSERT INTO athletes1 VALUES (0005, 'Kings', 35);
-- view all rows in table
SELECT * FROM athletes1;
id | team | points
----+---------+--------
1 | Mavs | 23
2 | Hawks | 12
3 | Rockets | 23
4 | Spurs | 29
5 | Kings | 35
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 (0001, 'Mavs', 15);
INSERT INTO athletes2 VALUES (0002, 'Hawks', 12);
INSERT INTO athletes2 VALUES (0006, 'Lakers', 50);
INSERT INTO athletes2 VALUES (0007, 'Celtics', 18);
INSERT INTO athletes2 VALUES (0008, 'Nuggets', 10);
-- view all rows in table
SELECT * FROM athletes2;
id | team | points
----+---------+--------
1 | Mavs | 15
2 | Hawks | 40
6 | Lakers | 50
7 | Celtics | 21
8 | Nuggets | 10
Suppose that we would like to upsert each row from the athletes1 table into the athletes2 table.
We can use the following syntax to do so:
-- upsert rows from athletes1 table into athletes2 table
INSERT INTO athletes2 (id, team, points)
SELECT id, team, points
FROM athletes1
ON CONFLICT (id) DO UPDATE SET
team = excluded.team,
points = excluded.points;
-- view updated athletes2 table
SELECT * FROM athletes2;
id | team | points
----+---------+--------
6 | Lakers | 50
7 | Celtics | 21
8 | Nuggets | 10
1 | Mavs | 23
2 | Hawks | 12
3 | Rockets | 23
4 | Spurs | 29
5 | Kings | 35
We can see that each row from the athletes1 table that did not already exist in the athletes2 table has been inserted into the athletes2 table.
For any rows that did already exist, the values from the team and points columns of the athletes1 table were used to update the values in the corresponding columns of the athletes2 table.
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 Insert Values into Table from Select Query
PostgreSQL: How to Update Multiple Columns in Table
