Изменить/добавить значения в полях для разных значений другого поля postgresql

Question

Коллеги, есть таблица, скажем table, в ней есть поля: id - уникальное integer, value1, value2...valueN - какие-то значения. Я получаю новые данные от стороннего сервиса, с помощью API запросов из Python, в которых указаны id и обновленные значений value. id в новых данных могут входить в множество id в таблице, а могут быть и новыми. Таких обновленных записей может за раз прийти тысячи. Вопрос - как мне максимально эффективно заменить те, которые присутствуют в таблице и добавить те, которые не присутствуют. Я вижу следующие варианты:

Первый вариант С помощью Питона разделить множество новых id на те, что уже есть в таблице и те, что еще не присутствуют. И далее в два этапа:

1 этап. Заспамить базу апдейтами каждой отдельной записи динамически сгенерированными в цикле Python. Типа того:

UPDATE table
SET value2=new_value_2, value5=new_value_5
WHERE id = id_from_python;

Где новые значения в SET и каждого id_from_python будут подставляться питоном в цикле.

2 этап. Сделать INSERT всех новых id и данных для них.

INSERT INTO table (id, value1, value2, value3) 
VALUES (new_id, new_val, new_val, new_val), 
       (new_id, new_val, new_val, new_val);

Второй вариант

Прочитал вот тут. Если коротко - делаем из новых данных временную таблицу и действуем как по ссылке выше, только тоже в два этапа -

1 этап. Меняем существующие значения в рабочей таблице на значения из временной.

UPDATE table b
SET value1 = a.value1,
    value2 = a.value2,
    value3 = a.value3
FROM temp_table a
WHERE a.id = b.id;

2 этап Добавляем новые id из временной таблицы в рабочую.

INSERT INTO table (id, value1, value2, value3)  
SELECT id, value1, value2, value3
  FROM temp_table
 WHERE temp_table.id not in table.id;

Гибридный вариант С помощью Питона разделяем id на новые и существующие. Из существующих id делаем временную таблицу и делаем UPDATE как во втором варианте, а множество новых id добавляем в рабочую таблицу как в первом варианте.

Вопрос. Правильно ли составлены запросы? Какой вариант лучше? Есть ли варианты лучше/быстрее/оптимальнее?

Answer 1

Можно сделать одним запросом и пусть база данных сама выберет, вставлять данные как новые или обновлять записи. В простейшем варианте вот вам два варианта:

Использовать для версии 9.6+ insert into ... on conflict

insert into table (id, value1, value2, value3)
values (id, value1, value2, value3),
       (id, value1, value2, value3),
       (id, value1, value2, value3)
on conflict (id) do update set 
    value1 = EXCLUDED.value1, 
    value2 = EXCLUDED.value2, 
    value3 = EXCLUDED.value3;

Использовать для версии 15+ merge into ... when matched ... when not matched

merge into table
using (select id as id, value1 as value1, value2 as value2,value3 as value3) as t
on t.id = table.id
when matched then
    update set value1 = t.value1, 
               value2 = t.value2, 
               value3 = t.value3
when not matched then
    insert (id, value1, value2, value3)
    values (t.id, t.value1, t.value2, t.value3);

БЛОГ НА HUSL

Изменить/добавить значения в полях для разных значений другого поля postgresql

Ответы (1 шт):