Обработка конфликтов при вставке данных с UPSERT

В PostgreSQL команда UPSERT реализована с помощью конструкции INSERT ... ON CONFLICT, которая позволяет обрабатывать конфликты, возникающие при вставке данных. Это позволяет либо обновлять существующие записи, либо выполнять другие действия при возникновении конфликта.

Синтаксис INSERT ... ON CONFLICT

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON CONFLICT (conflict_column)
DO UPDATE SET column1 = value1, column2 = value2, ...;

Обработка конфликтов

Указание уникального ключа

Конфликт возникает, если в таблице уже существует запись с таким же значением уникального ключа. Уникальный ключ может быть первичным ключом или уникальным индексом.

INSERT INTO employees (employee_id, name, salary)
VALUES (1, 'John Doe', 75000)
ON CONFLICT (employee_id)
DO UPDATE SET name = EXCLUDED.name, salary = EXCLUDED.salary;

• Если запись с employee_id = 1 уже существует, она будет обновлена значениями из EXCLUDED, то есть новыми значениями для name и salary.

• Если записи с таким employee_id нет, она будет вставлена.

Обновление с условиями

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

INSERT INTO employees (employee_id, name, salary)
VALUES (1, 'John Doe', 75000)
ON CONFLICT (employee_id)
DO UPDATE SET salary = CASE
                          WHEN EXCLUDED.salary > employees.salary THEN EXCLUDED.salary
                          ELSE employees.salary
                       END,
              name = EXCLUDED.name;

• Обновление salary произойдет только если новое значение больше текущего значения.

• name будет обновлено всегда.

Обновление и выполнение действий в случае конфликта

Вы можете использовать DO NOTHING для пропуска вставки в случае конфликта.

INSERT INTO employees (employee_id, name, salary)
VALUES (1, 'John Doe', 75000)
ON CONFLICT (employee_id)
DO NOTHING;

• Если запись с employee_id = 1 уже существует, вставка будет пропущена, и никаких изменений не будет.

Обработка нескольких уникальных ключей

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

INSERT INTO employees (employee_id, email, name, salary)
VALUES (1, '[e-mail]', 'John Doe', 75000)
ON CONFLICT (employee_id)
DO UPDATE SET name = EXCLUDED.name, salary = EXCLUDED.salary
ON CONFLICT (email)
DO UPDATE SET salary = EXCLUDED.salary;

• Обработка конфликта будет сначала по employee_id, а затем, если конфликт по employee_id не возникнет, проверяется email.

Пример с более сложными действиями

В этом примере происходит обновление записи только если новое значение salary больше старого и вставка новой записи, если конфликт не возник.

INSERT INTO employees (employee_id, name, salary)
VALUES (1, 'John Doe', 75000)
ON CONFLICT (employee_id)
DO UPDATE SET salary = CASE
                          WHEN EXCLUDED.salary > employees.salary THEN EXCLUDED.salary
                          ELSE employees.salary
                       END,
              name = EXCLUDED.name;

• Если запись с employee_id существует, она будет обновлена в зависимости от значения salary.

• Если запись с таким employee_id отсутствует, она будет вставлена.

Особенности

• EXCLUDED: Ключевое слово EXCLUDED используется для ссылки на значения, которые пытались вставить, но возник конфликт. Это позволяет использовать эти значения в выражениях для обновления.

• Производительность: INSERT ... ON CONFLICT обычно более эффективен, чем отдельные команды INSERT и UPDATE, так как объединяет логику в одном запросе.

• Ограничения: Команда работает только с уникальными ключами и первичными ключами. Она не может обрабатывать конфликты по неуникальным индексам напрямую.

Команда INSERT ... ON CONFLICT в PostgreSQL предоставляет гибкий и мощный способ для обработки конфликтов при вставке данных, позволяя эффективно управлять уникальными записями и выполнять различные действия в зависимости от ситуации.