UPSERT в PostgreSQL

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

Синтаксис

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

Принцип работы

1. Вставка: Команда пытается вставить новую строку с указанными значениями.

2. Обновление: Если возникает конфликт по указанному уникальному ключу или первичному ключу, вместо вставки выполняется обновление существующей строки на основе выражений в DO UPDATE.

Примеры использования

Простое обновление при конфликте

В этом примере вставляется новая строка в таблицу employees. Если возникает конфликт по employee_id, выполняется обновление.

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 уже существует, она будет обновлена с новыми значениями name и salary.

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

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

В этом примере обновляется запись только если новое значение 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 = 1 существует и новое значение salary больше старого, salary будет обновлено.

• name будет обновлено в любом случае.

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

Обновление с использованием выражений

В этом примере значение quantity увеличивается на указанное значение, если возникает конфликт.

INSERT INTO inventory (item_id, quantity)
VALUES (1001, 50)
ON CONFLICT (item_id)
DO UPDATE SET quantity = inventory.quantity + EXCLUDED.quantity;

• Если запись с item_id = 1001 существует, quantity будет увеличена на 50.

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

Добавление новых строк и удаление старых (частичный обновление)

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

WITH new_data AS (
    SELECT 1 AS employee_id, 'John Doe' AS name, 75000 AS salary
    UNION ALL
    SELECT 2 AS employee_id, 'Jane Smith' AS name, 80000 AS salary
)
INSERT INTO employees (employee_id, name, salary)
SELECT employee_id, name, salary
FROM new_data
ON CONFLICT (employee_id)
DO UPDATE SET name = EXCLUDED.name, salary = EXCLUDED.salary
WHERE employees.salary < EXCLUDED.salary;

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

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

Особенности

• Поддержка: INSERT ... ON CONFLICT поддерживается начиная с PostgreSQL 9.5.

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

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

Заключение

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