PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to INSERT INTO a table (not UPDATE) on conflict, generating a new key value?

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?

Tags:

3

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    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:

    If you are not sure, find out:

    SELECT pg_get_serial_sequence('table1', 'id') AS seq;

    See:

    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

    Login or Signup to reply.
  3. 0

    1. Insert non-conflicting rows:

      INSERT INTO table1 (id, name)
SELECT id, name FROM table2
ON CONFLICT(id) DO NOTHING;

    2. 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);

    This will ensure that conflicting rows are inserted with a new ID.

    I hope this will help you..

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search