Использование RETURNING с ON CONFLICT в PostgreSQL 9.5

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

Таким образом можно сочетать выполнение INSERT с обновлением при возникновении конфликта (ON CONFLICT), и при этом получить результат операции с помощью RETURNING:

INSERT INTO table_name (id, col1) VALUES (1, 'A')
ON CONFLICT (id) DO UPDATE SET col1 = EXCLUDED.col1
RETURNING *;

Здесь RETURNING * вернёт строку, которая была вставлена или обновлена. Псевдотаблица EXCLUDED содержит значения, которые должны были быть вставлены.

Совершенствование UPSERT

Эффективность обновлений с ON CONFLICT DO UPDATE

Воспользуйтесь ON CONFLICT DO UPDATE для эффективного управления обновлениями и избежания нежелательных последствий. Чётко поставленные ограничения облегчат корректное определение конфликтов.

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

Управление конкурентностью и стратегия блокировки

При работе с большим объемом конкурентных операций важно осуществлять грамотное управление и блокировку данных. Общие Табличные Выражения (Common Table Expressions, CTE) и стратегии блокировки помогут избежать лишних обновлений и обеспечат непрерывность работы с данными.

WITH inserted AS (
  INSERT INTO my_table (id, data)
  VALUES (1, 'NewData')
  ON CONFLICT (id) DO UPDATE SET data = 'UpdatedData'
  RETURNING id
)
SELECT * FROM inserted;

RETURNING возвращает id строки, которая была вставлена или обновлена, с тем, чтобы избежать эффектов, не предусмотренных при многократных вызовах.

COALESCE и UNION: Ваш новый инструментарий

Функции COALESCE и оператор UNION предоставляют креативные приёмы решения нестандартных задач. Они позволяют управлять идентификаторами и комбинировать результаты различных запросов как настоящий специалист!

INSERT INTO my_table (id, data)
VALUES (2, 'Recorded')
ON CONFLICT (id) DO NOTHING
RETURNING id;
SELECT COALESCE (
  (SELECT id FROM my_table WHERE id = 2),
  (SELECT id FROM inserted)
) AS id;

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

Для наглядности представим, что вы вставляете новую карту с уникальным номером в полную колоду. Если карта с таким номером уже есть, следует обновить данные этой карты.

INSERT INTO card_deck (card_number, score) VALUES (7, 150)
ON CONFLICT (card_number)
DO UPDATE SET score = excluded.score RETURNING *;

• Колода до: [1, 🂢, 3, 4, 5, 6, 🂧, 8...]
• Попытка вставки: 🂡 (score: 150)
• Колода после: [1, 🂢, 3, 4, 5, 6, 🂡 (score: 150), 8...]

RETURNING * выводит результат операции вставки или обновления.

Тонкости UPSERT и продвинутые стратегии

Влияние на производительность

Если злоупотреблять RETURNING, производительность больших таблиц может снизиться. Оптимизация запросов даст возможность достигнуть максимальной производительности.

Особенности: Чем новее версия, тем лучше

Если вы столкнулись с накапливающимися операциями UPSERT и возникающими конфликтами, проверьте и при необходимости обновите версию PostgreSQL, чтобы получить доступ к новейшим методам управления конфликтами.

Распознавание взаимных блокировок и терминология

Для предотвращения взаимных блокировок следует соблюдать порядок вставки строк, и избегать использования ключевых слов СУБД.

Упрощение повторных операций UPSERT

Если часто возникают конфликты, которые усложняют работу, можно разделить операции на отдельные вставки и обновления. Такой подход менее компактен, но позволяет избежать многих проблем при повторных операциях UPSERT.

Завершение

Интеграция предложенных стратегий поможет вам улучшить подход к выполнению операций UPSERT в PostgreSQL. Планируйте действия, принимая во внимание конкурентность, предвосхитите взаимные блокировки и оптимизируйте производительность, обеспечивая надежное объявление ошибок и готовность к повторной работе в случае неудач.

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

1. PostgreSQL: Documentation: 16: INSERT — Официальная документация PostgreSQL.
2. sql – How to use RETURNING with ON CONFLICT in PostgreSQL? — Обсуждение на Stack Overflow: примеры и объяснение использования RETURNING с ON CONFLICT.
3. Postgresql improve select performance/parallelism in huge table — Обсуждение на DBA Stack Exchange о сочетании INSERT, ON CONFLICT и RETURNING.