Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table1 (id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, name text);
CREATE TABLE table2 (id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, name text);

INSERT INTO table1 VALUES
  (1, 'Bob')
, (3, 'Steven')
, (4, 'Steven2')
RETURNING *;

INSERT INTO table2 VALUES
  (2, 'John')
, (3, 'Jack')
, (4, 'Jack2')
, (7, 'bigger ID from t2')
RETURNING *;

-- get sequences in sync after being overruled manually
SELECT setval(pg_get_serial_sequence('table1', 'id'), max(id)) FROM table1;
SELECT setval(pg_get_serial_sequence('table2', 'id'), max(id)) FROM table2;

CREATE TABLE

CREATE TABLE

id
name

1
Bob

3
Steven

4
Steven2

INSERT 0 3

id
name

2
John

3
Jack

4
Jack2

7
bigger ID from t2

INSERT 0 4

setval

4

SELECT 1

setval

7

SELECT 1

-- If unsure about sequence name for table1.id:
-- "public.table1_id_seq" is the default for pulic.table1.id
SELECT pg_get_serial_sequence('table1', 'id') AS seq;
-- then use it as *identifier* and as *value* in the subsequent query

seq

public.table1_id_seq

SELECT 1

BEGIN;

-- only if concurrent writes are possible
LOCK table1 IN SHARE ROW EXCLUSIVE MODE;  -- protect tables against concurrent data changes, exclusively
LOCK table2 IN SHARE ROW EXCLUSIVE MODE;

-- if table2.id can be bigger than current value of sequence for table1.id:
-- only write if necesary:
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;

BEGIN

LOCK TABLE

LOCK TABLE

case

7

SELECT 1

INSERT 0 4

COMMIT

TABLE table1 ORDER BY id;

id
name

1
Bob

2
John

3
Steven

4
Steven2

7
bigger ID from t2

8
Jack

9
Jack2

SELECT 7