UPSERT через ON CONFLICT

Учимся вставлять строку «или обновить, если уже есть» одной командой — это UPSERT.

UPSERT — операция «вставить или обновить»: если строка с таким ключом уже есть, она обновляется, иначе вставляется новая. В PostgreSQL это INSERT ... ON CONFLICT.

Какую проблему решает UPSERT

Частая задача: «сохрани настройку пользователя». Если записи ещё нет — вставить, если есть — обновить. Наивно это требует двух шагов: сначала проверить наличие, потом вставить или обновить. Между проверкой и вставкой возможна гонка: кто-то успеет вставить ту же строку, и вы получите ошибку дубликата ключа.

UPSERT решает это атомарно — одной командой, без гонок.

Синтаксис ON CONFLICT

К обычному INSERT добавляется ON CONFLICT (столбец) DO .... PostgreSQL пытается вставить, а при нарушении уникальности по указанному столбцу выполняет действие вместо ошибки. Это PG-специфика, помечаем pgsql.

CREATE TABLE settings (
    user_id INTEGER PRIMARY KEY,
    theme   TEXT NOT NULL
);

-- Вставить или, если user_id уже есть, обновить theme
INSERT INTO settings (user_id, theme)
VALUES (1, 'dark')
ON CONFLICT (user_id)
DO UPDATE SET theme = EXCLUDED.theme;

Магическое слово здесь — EXCLUDED: это виртуальная строка, которую вы пытались вставить. EXCLUDED.theme — то новое значение theme, что не прошло из-за конфликта. Так мы говорим «обнови существующую строку тем, что хотели вставить».

DO NOTHING — тихо пропустить

Иногда при конфликте нужно просто ничего не делать — например, при массовой вставке, где дубликаты допустимы и их надо игнорировать.

-- Если такой email уже есть — пропустить без ошибки
INSERT INTO users (email, name)
VALUES ('[email protected]', 'Анна')
ON CONFLICT (email) DO NOTHING;

Вариант	Поведение при конфликте
`DO NOTHING`	пропустить строку, не вставлять и не падать
`DO UPDATE SET ...`	обновить существующую строку (доступна `EXCLUDED`)

Условный апдейт и RETURNING

DO UPDATE можно дополнить условием WHERE и вернуть результат через RETURNING — обновлять только если что-то реально поменялось.

INSERT INTO products (sku, price)
VALUES ('A-100', 500)
ON CONFLICT (sku)
DO UPDATE SET price = EXCLUDED.price
WHERE products.price IS DISTINCT FROM EXCLUDED.price
RETURNING sku, price;

Здесь products.price — старое значение, EXCLUDED.price — новое. IS DISTINCT FROM сравнивает их безопасно даже с NULL, и обновление случится, только если цена изменилась.

Этой конструкции нет в стандартном SQLite, поэтому в нашей песочнице она не выполнится — это чисто PostgreSQL.

Итог

UPSERT = «вставить или обновить» одной атомарной командой INSERT ... ON CONFLICT.
DO UPDATE SET ... = EXCLUDED.поле обновляет тем значением, что хотели вставить; DO NOTHING тихо пропускает.
Конфликт определяется по столбцу с уникальным ограничением (PRIMARY KEY или UNIQUE).