Как инертировать (не обновлять) таблицу при конфликте путем генерации нового первичного ключа

В настоящее время у меня есть две таблицы postgresql table1 и table2, показанные ниже, первичный ключ id.

table1:
id | name
----------
1  | Bob
3  | Steven

table2:
id | name
----------
2  | John
3  | Jack

Я хочу объединить эти две таблицы, вставив table2 в table1, и table1 после этой операции должна выглядеть как показано ниже. По сути, она может поддерживать тот же первичный ключ, если нет конфликта, но если конфликт есть, она будет генерировать новый идентификатор для входящих данных из table2 и вставлять его как новую роль в table1. В этом примере Jack из таблицы2 будет иметь новый id из 4 (максимальный id из таблицы1 + 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.
INSERT INTO table1 (id, name)
SELECT id, name
FROM table2
ON CONFLICT(id) DO UPDATE SET id=nextval(pg_get_serial_sequence('table1', 'id'));

Мне нужна помощь, чтобы понять, как вставить в новую роль с новым идентификатором.

Не существует прямого и простого способа добиться описанных результатов. Если нет веских причин сохранять не противоречащие друг другу значения 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 будет таким:

id name
1 Bob
2 John
3 Steven
4 Jack

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

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

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

Сначала узнайте имя базовой последовательности для table1.id. Тот же путь для столбцов serial и IDENTITY. Смотрите:

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

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

Смотрите:

Здесь используется имя по умолчанию 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 - так же, как и для 2-го параметра функции 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);

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

Если могут быть одновременные записи в обе таблицы, LOCK обе, чтобы избежать условий гонки. Соответствующая сила блокировки должна быть 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;

fiddle

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

Это гарантирует, что конфликтующие строки будут вставлены с новым идентификатором.

Вернуться на верх