Решаем проблемы с update, insert и subquery в PostgreSQL

Быстрый ответ

Для операции upsert в PostgreSQL рекомендуется использовать команды INSERT в сочетании с ON CONFLICT. Вот основной образец SQL для upsert:

INSERT INTO целевая_таблица (список_столбцов)
SELECT данные_для_вставки
FROM исходные_данные
ON CONFLICT (столбцы_конфликта) DO UPDATE
SET обновляемый_столбец = EXCLUDED.обновляемый_столбец;

Настройте данный шаблон в соответствии с вашими требованиями:

• целевая_таблица — это таблица, в которую будут вставлены или обновлены данные.
• список_столбцов включает столбцы, которые вы намерены вставить или обновить.
• исходные_данные — это подзапрос или таблица, откуда берутся обновляемые данные.
• столбцы_конфликта указывают на строку с уникальными данными, как правило, это первичный ключ.
• обновляемый_столбец — столбец, который будет обновлён в случае совпадения ключей.

Команда DO UPDATE SET определяет действие по обновлению, а через EXCLUDED описывается строка, которая могла быть добавлена в отсутствие конфликта.

Глубокое погружение в операцию upsert

Термин "upsert" является сочетанием терминов "update" (обновить) и "insert" (вставить) и обозначает двойное действие: обновление существующих строк или вставка новых. PostgreSQL обрабатывает оба действия одним запросом, что позволяет уменьшить затраты на выполнение и ускорить время обработки.

Управление сложными подзапросами

При работе с подзапросами:

• Всегда присваивайте алиасы подзапросам для упрощения их использования в командах INSERT.
• Будьте внимательны к соответствию типов данных и столбцов. Несоответствие может стать причиной ошибок.
• Обращайте внимание на условия соединения в подзапросах, чтобы исключить неверные обновления.

Подробнее об этом расскажет наш спикер на видео

Использование временных таблиц для ускорения производительности

Временные таблицы могут улучшить производительность операции upsert, особенно при работе с большими данными или сложными запросами:

CREATE TEMP TABLE tmp_subquery AS
SELECT результат_сложной_операции
FROM некий_большой_набор_данных;

INSERT INTO целевая_таблица (список_столбцов)
SELECT * FROM tmp_subquery
ON CONFLICT (столбцы_конфликта) DO UPDATE
SET обновляемый_столбец = EXCLUDED.обновляемый_столбец;

DROP TABLE tmp_subquery; -- не забывайте удалять временные таблицы после использования!

Рекомендации по оптимизации

Применение JOIN в подзапросах

Использование JOIN позволяет синхронизировать данные из различных источников до вставки в таблицу, что позволяет сэкономить ресурсы и время.

Упрощённая вставка за счёт использования подзапросов вместо VALUES

Применяйте подзапросы вместо VALUES для массовой вставки данных:

INSERT INTO целевая_таблица (столбец1, столбец2)
SELECT столбец1, столбец2
FROM исходная_таблица;

Это делает SQL-код более лаконичным и наглядным.

Визуализация

Представьте себе склад (подзапрос) с коробками данных, которые нужно перевести в грузовики (целевая таблица):

Склад (подзапрос): [📦📦📦📦📦]
Грузовики (таблица):       [🚚, 🚚]

## Для каждого из грузовиков:
- Проверка наличия аналогичной коробки
- Если найдена: Обновление содержимого (🔄)
- Если не найдена: Добавление новой коробки (➕)

UPSERT через ON CONFLICT:

🚚🔄🚚➕🚚➕...
# Каждый грузовик получает актуализированные коробки или дополнительные грузы

Основная идея заключается в массовой обработке данных с аккуратным распределением "коробок" по "грузовикам".

Рисование границ для точного и эффективного обновления

Важность корректного указания столбцов

При использовании SET крайне важно правильно связать столбцы базовой строки и строки, вызывающей конфликт. Ошибки в ссылках могут привести к ошибкам операции.

Фильтрация обновлений при помощи WHERE-условия

Иногда, не каждый конфликт должен приводить к обновлению. Условие WHERE помогает отфильтровать подобные случаи, обновляя лишь те строки, в которых это необходимо:

ON CONFLICT (столбцы_конфликта) DO UPDATE
SET обновляемый_столбец = EXCLUDED.обновляемый_столбец
WHERE (условие_для_обновления);

Значение понимания изоляции транзакций

Настоятельно рекомендуется обладать пониманием уровней изоляции транзакций, чтобы спроектировать операцию upsert таким образом, чтобы она была эффективной и безопасной, уменьшая риски возникновения блокировок и аномалий.

Полезные материалы

1. PostgreSQL: Documentation: 16: INSERT — Описание синтаксиса INSERT ... ON CONFLICT в PostgreSQL.
2. UPSERT – PostgreSQL wiki — Обзор операции UPSERT и её реализации в PostgreSQL.
3. PostgreSQL: Documentation: 16: 9.23. Subquery Expressions — Руководство по подзапросам в PostgreSQL.
4. How to UPSERT (MERGE, INSERT ... ON DUPLICATE UPDATE) in PostgreSQL? – Stack Overflow — Обсуждение способов выполнения upsert в PostgreSQL на Stack Overflow.
5. PostgreSQL: Documentation: 16: 13.2. Transaction Isolation — Об изоляции транзакций в PostgreSQL и их влиянии на конкуренцию.