I have two Postgres tables as shown below, primary key is id:
table1:
id | name
----------
1 | Bob
3 | Steven
table2:
id | name
----------
2 | John
3 | Jack
I would like to combine these two tables by inserting table2 to table1, and table1 should look like below after the operation.
Essentially, it can maintain the same primary key if there is no conflict, but when it has conflict, it will generate a new id for the incoming data from table2, and insert that as a new row in table1. In this example, ‘Jack’ from table2 will be inserted as a new row with a new id of 4 (max id from table1 + 1).
id | name
----------
1 | Bob
2 | John
3 | Steven
4 | Jack
Below is my current approach. Which updates the id in conflicted row in table1 (not what I want):
INSERT INTO table1 (id, name)
SELECT id, name
FROM table2
ON CONFLICT(id) DO UPDATE SET id=nextval(pg_get_serial_sequence('table1', 'id'));
How to insert a new row with a new id?
2
-
Insert non-conflicting rows:
INSERT INTO table1 (id, name) SELECT id, name FROM table2 ON CONFLICT(id) DO NOTHING; -
Insert conflicting rows with new IDs:
INSERT INTO table1 (id, name) SELECT nextval(pg_get_serial_sequence('table1', 'id')), name FROM table2 t2 WHERE EXISTS (SELECT 1 FROM table1 t1 WHERE t1.id = t2.id); ``
conflicting rows are inserted with a new ID.
Krutarth is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
2
There isn’t a direct and simple way to achieve the described results. Unless there is a compelling reason to preserve non-conflicting id values from table2, the best option is insert the name values from table2 and discard the original id values.
The following establishes the demonstration environment:
CREATE TABLE table1 (id serial PRIMARY KEY, name TEXT);
CREATE TABLE table2 (id serial PRIMARY KEY, name TEXT);
INSERT INTO
table1 (id, name)
VALUES
(1, 'Bob'),
(3, 'Steven');
INSERT INTO
table2 (id, name)
VALUES
(2, 'John'),
(3, 'Jack');
The following demonstrates an approach that can be used to add the contents of table2 to table1 while preserving non-conflicting id values from table2:
WITH set_seq AS
(SELECT s.dst_seq,
SETVAL(s.dst_seq,
GREATEST((SELECT MAX(id) FROM table1),
(SELECT MAX(id) FROM table2)))
FROM (SELECT PG_GET_SERIAL_SEQUENCE('table1', 'id') AS dst_seq) s)
INSERT INTO table1 (id, name)
SELECT CASE WHEN dst.id IS NOT NULL THEN NEXTVAL(set_seq.dst_seq) ELSE src.id END, src.name
FROM set_seq
CROSS JOIN table2 src
LEFT JOIN table1 dst ON dst.id = src.id;
The first part ensures that the sequence that provides id values will not conflict with the id values of either table. After the preceding SQL runs, the contents of table1 will be:
| id | name |
|---|---|
| 1 | Bob |
| 2 | John |
| 3 | Steven |
| 4 | Jack |
Assuming table1.id draws from a sequence.
Notably, the MERGE command (Postgres 15+) cannot be used (as single command), since it only allows UPDATE / DELETE (or nothing) WHEN MATCHED.
Simple case: no concurrent writes possible
First, know the name of the underlying sequence for table1.id. Same path for serial and IDENTITY columns. See:
- Auto increment table column
If you are not sure, find out:
SELECT pg_get_serial_sequence('table1', 'id') AS seq;
See:
- How to reset Postgres’ primary key sequence when it falls out of sync?
Proceeding with the default name public.table1_id_seq here. Replace with your actual sequence name. Schema-qualify to be safe.
If the maximum value in table2.id can be higher than the current
value of the sequence, set it to the higher value. Only write if necessary:
SELECT CASE WHEN t2_max > t1_seq THEN setval('public.table1_id_seq', t2_max) END
FROM (SELECT max(id) AS t2_max FROM table2) t2
, (SELECT last_value + is_called::int AS t1_seq FROM public.table1_id_seq) s;
Compare to the current value of the sequence, rather than the maximum tabl1.id. Subtle difference. We wouldn’t want to decrease the sequence value, risking possible conflicts.
Note last_value + is_called::int. Internally, every sequence has a boolean tag is_called. The default is true – same as for the 2nd parameter of the function setval(). Then the next serial number will be incremented. Cast to integer and add, true → 1 / false → 0, and everything falls into place.
Then, probably simplest and fastest:
INSERT INTO table1 (id, name)
SELECT CASE WHEN t1.id IS NULL
THEN t2.id
ELSE nextval('table1_id_seq') END
, t2.name
FROM table2 t2
LEFT JOIN table1 t1 USING (id);
Concurrent writes possible
If there can be concurrent writes in either table, LOCK both to avoid race conditions. The appropriate lock strength should be SHARE ROW EXCLUSIVE to protect tables against concurrent data changes exclusively.
And do it all in a single transaction. (Can’t hurt either way):
BEGIN;
LOCK table1 IN SHARE ROW EXCLUSIVE MODE; -- protect against concurrent data changes, exclusively
LOCK table2 IN SHARE ROW EXCLUSIVE MODE;
SELECT CASE WHEN t2_max > t1_seq THEN setval('public.table1_id_seq', t2_max) END
FROM (SELECT max(id) AS t2_max FROM table2) t2
, (SELECT last_value + is_called::int AS t1_seq FROM public.table1_id_seq) s;
INSERT INTO table1 (id, name)
SELECT CASE WHEN t1.id IS NULL
THEN t2.id
ELSE nextval('table1_id_seq') END
, t2.name
FROM table2 t2
LEFT JOIN table1 t1 USING (id);
COMMIT;
fiddle