Как ВСТАВИТЬ В таблицу (не ОБНОВИТЬ) при конфликте, генерируя новое значение ключа?

Не существует прямого и простого способа достижения описанных результатов. Если нет веской причины сохранять неконфликтующие значения id из table2, лучший вариант — вставить значения name из table2 и отказаться от исходных значений id.

Ниже приводится демонстрационная среда:

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');

Ниже демонстрируется подход, который можно использовать для добавления содержимого table2 в table1, сохраняя при этом неконфликтующие значения id из 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;

Первая часть гарантирует, что последовательность, предоставляющая значения id, не будет конфликтовать со значениями id любой таблицы. После выполнения предыдущего SQL содержимое table1 будет:

Предполагая, что table1.id рисует из последовательности.

Примечательно, что команду MERGE (Postgres 15+) нельзя использовать (как одну команду), поскольку она допускает только UPDATE/DELETE (или ничего) WHEN MATCHED.

Простой случай: одновременная запись невозможна.

Во-первых, узнайте название базовой последовательности для table1.id. Тот же путь для столбцов serial и IDENTITY. Видеть:

• Столбец таблицы автоматического увеличения

Если вы не уверены, узнайте:

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

Видеть:

• Как сбросить последовательность первичных ключей Postgres, если она не синхронизирована?

Продолжаем использовать имя по умолчанию public.table1_id_seq здесь. Замените фактическим именем последовательности. Схема-квалификация, чтобы быть безопасным.

Если максимальное значение в table2.id может быть выше текущего значение последовательности, установите для него более высокое значение. Пишите только при необходимости:

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;

Сравните с текущим значением последовательности, а не с максимальным tabl1.id. Тонкая разница. Мы не хотели бы уменьшать значение последовательности, рискуя возможными конфликтами.

Обратите внимание last_value + is_called::int. Внутри каждая последовательность имеет логический тег is_called. По умолчанию используется true — то же, что и для второго параметра функции setval(). Затем следующий серийный номер будет увеличен. Приведите к integer и добавьте true → 1 / false → 0, и все встанет на свои места.

Тогда, вероятно, самый простой и быстрый:

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

Возможна одновременная запись

Если в любой из таблиц возможны одновременные записи, ЗАБЛОКИРУЙТЕ обе, чтобы избежать условий гонки. Соответствующая сила блокировки должна быть SHARE ROW EXCLUSIVE для защиты таблиц исключительно от одновременных изменений данных.

И сделать все это за одну транзакцию. (в любом случае не повредит):

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;

рабочий пример

1. Вставьте неконфликтующие строки:

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

2. Вставьте конфликтующие строки с новыми идентификаторами:

   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);
   ``
   

конфликтующие строки вставляются с новым идентификатором.