UPSERT и RETURNING: вставить или обновить

«Обнови, если есть, вставь, если нет» одной командой: `INSERT ... ON CONFLICT`, `excluded`, опасность `INSERT OR REPLACE`, получение изменённых строк через `RETURNING`, `MERGE` в PostgreSQL, `ON DUPLICATE KEY UPDATE` в MySQL и загрузки, которые можно безопасно повторять.

  • Продвинутый
  • 20 мин
  • 12 вопросов
  • Урок 11 из 28

В этом уроке ты узнаешь

  • Писать upsert с ON CONFLICT ... DO NOTHING и DO UPDATE SET ... = excluded....
  • Объяснять, почему INSERT OR REPLACE — не upsert
  • Получать результат INSERT, UPDATE и DELETE через RETURNING
  • Узнавать MERGE и ON DUPLICATE KEY UPDATE и строить идемпотентную загрузку

Каждую ночь поставщик присылает прайс-лист: часть товаров в магазине уже есть, часть — новые. Наивный путь — два шага: проверить через SELECT, затем INSERT или UPDATE. Это два обращения к базе и, что хуже, состояние гонки: два процесса одновременно видят «нет», оба пытаются вставить, и один получает ошибку UNIQUE. Upsert (update + insert) принимает это решение внутри базы, одной атомарной командой. В уроке «INSERT, UPDATE, DELETE: изменение данных» он упоминался в таблице; теперь изучим его как следует — вместе с RETURNING, который сразу возвращает изменённые строки.

ON CONFLICT: DO NOTHING и DO UPDATE

UpsertОпределение

INSERT, в котором сказано, что делать, если новая строка сталкивается (конфликтует) с уникальным ключом: ничего (DO NOTHING) или обновить существующую строку (DO UPDATE). Конфликт возможен только по ограничению PRIMARY KEY или UNIQUE.

В учебной базе products.name не уникален, поэтому сначала создаём уникальный индекс. Теперь второй Laptop при обычном INSERT даёт ошибку:

SQL
CREATE UNIQUE INDEX idx_products_name ON products (name);

INSERT INTO products (name, category, price, stock)
VALUES ('Laptop', 'Electronics', 1399, 5);
Ожидаемый результат
Error: UNIQUE constraint failed: products.name

С ON CONFLICT (name) DO NOTHING конфликтующая строка молча отбрасывается, а остальные вставляются. Столбцы в скобках — цель конфликта, они должны совпадать с существующим уникальным ограничением:

SQL
CREATE UNIQUE INDEX idx_products_name ON products (name);

INSERT INTO products (name, category, price, stock)
VALUES ('Laptop', 'Electronics', 1399, 5),
       ('Tablet', 'Electronics', 520, 10)
ON CONFLICT (name) DO NOTHING;

SELECT id, name, price, stock FROM products
WHERE name IN ('Laptop', 'Tablet') ORDER BY id;
Ожидаемый результат
id | name | price | stock
1 | Laptop | 1450 | 8
11 | Tablet | 520 | 10
Старый Laptop не тронут, Tablet добавлен.

Внутри DO UPDATE SET видны две строки. **excluded — строка, которую пытались вставить и которая «осталась за бортом» из-за конфликта; имя таблицы (или столбец без префикса) — строка, уже лежащая в базе. Ниже цена заменяется новой, а остаток прибавляется** к старому:

SQL
CREATE UNIQUE INDEX idx_products_name ON products (name);

INSERT INTO products (name, category, price, stock)
VALUES ('Laptop', 'Electronics', 1399, 5),
       ('Tablet', 'Electronics', 520, 10)
ON CONFLICT (name) DO UPDATE
SET price = excluded.price,
    stock = products.stock + excluded.stock;

SELECT id, name, price, stock FROM products
WHERE name IN ('Laptop', 'Tablet') ORDER BY id;
Ожидаемый результат
id | name | price | stock
1 | Laptop | 1399 | 13
11 | Tablet | 520 | 10
Laptop сохранил свой id: цена 1399, остаток 8 + 5 = 13.

Классическое применение — счётчик: при первом посещении страницы строка создаётся, дальше растёт только число. Программе не нужно спрашивать «а строка уже есть?»:

SQL
CREATE TABLE page_views (page TEXT PRIMARY KEY, views INTEGER NOT NULL);

INSERT INTO page_views VALUES ('/sql', 1)
ON CONFLICT (page) DO UPDATE SET views = views + 1;
INSERT INTO page_views VALUES ('/sql', 1)
ON CONFLICT (page) DO UPDATE SET views = views + 1;
INSERT INTO page_views VALUES ('/python', 1)
ON CONFLICT (page) DO UPDATE SET views = views + 1;
INSERT INTO page_views VALUES ('/sql', 1)
ON CONFLICT (page) DO UPDATE SET views = views + 1;

SELECT * FROM page_views ORDER BY page;
Ожидаемый результат
page | views
/python | 1
/sql | 3

Пусть старые данные не затирают новые

Задача

Складская система присылает сообщения об остатках, но иногда они приходят не по порядку: сначала сообщение 10:05 (остаток 7), затем 10:00 (остаток 9). Как в stock_levels(sku PRIMARY KEY, qty, updated_at) всегда хранить самый свежий остаток?

Показать решениеСкрыть решение

Решение

1) Добавь условие в DO UPDATE: ON CONFLICT (sku) DO UPDATE SET qty = excluded.qty, updated_at = excluded.updated_at WHERE excluded.updated_at > stock_levels.updated_at.
2) Пришло сообщение 10:05 — строка создаётся или обновляется: остаток 7.
3) Пришло сообщение 10:00 — WHERE ложно (10:00 не позже 10:05), строка не меняется.
4) Итог: остаток остаётся 7. Без WHERE победило бы сообщение, пришедшее последним (9).

Чтобы читать дальше, зарегистрируйся бесплатно

Остальные разделы урока, тест и заметки открываются с аккаунтом Educora. Регистрация занимает минуту, а прогресс сохраняется на всех твоих устройствах.

INSERT OR REPLACE — не upsert

В SQLite (и в MySQL — REPLACE INTO) есть запись покороче, но она делает другое: при конфликте удаляет старую строку и вставляет новую. Не перечисленные столбцы становятся NULL (или DEFAULT), строка получает новый id, а заказы, ссылавшиеся на неё, становятся «сиротами»:

SQL
CREATE UNIQUE INDEX idx_products_name ON products (name);

INSERT OR REPLACE INTO products (name, price)
VALUES ('Laptop', 1399);

SELECT p.id, p.name, p.category, p.price, p.stock,
       (SELECT COUNT(*) FROM orders AS o WHERE o.product_id = p.id) AS orders
FROM products AS p
WHERE p.name = 'Laptop';
Ожидаемый результат
id | name | category | price | stock | orders
11 | Laptop | NULL | 1399 | NULL | 0
Категория и остаток потеряны, id сменился с 1 на 11, а заказ 1 теперь указывает на несуществующий товар.

RETURNING: сразу получить изменённые строки

id новой строки, значение, вычисленное базой, удалённые строки — обычно ради них пишут второй SELECT. RETURNING (в SQLite с версии 3.35, в PostgreSQL давно) пишется в конце INSERT, UPDATE и DELETE и возвращает перечисленные столбцы для каждой изменённой строки — как список SELECT, с выражениями и псевдонимами:

SQL
INSERT INTO students (first_name, last_name, age, grade, city)
VALUES ('Nərmin', 'Səlimova', 15, 9, 'Bakı'),
       ('Rauf', 'Məlikov', 16, 10, 'Gəncə')
RETURNING id, first_name || ' ' || last_name AS student;
Ожидаемый результат
id | student
13 | Nərmin Səlimova
14 | Rauf Məlikov
Программа получает новые id без дополнительного запроса.
SQL
UPDATE products
SET price = ROUND(price * 0.9, 2)
WHERE category = 'Electronics' AND stock > 0
RETURNING name, price AS new_price;
Ожидаемый результат
name | new_price
Laptop | 1305
Smartphone | 809.99
Headphones | 108.45
RETURNING показывает значения после изменения (Monitor с нулевым остатком не тронут).
SQL
CREATE UNIQUE INDEX idx_products_name ON products (name);

INSERT INTO products (name, category, price, stock)
VALUES ('Laptop', 'Electronics', 1399, 5),
       ('Tablet', 'Electronics', 520, 10)
ON CONFLICT (name) DO UPDATE
SET price = excluded.price, stock = products.stock + excluded.stock
RETURNING id, name, price, stock;
Ожидаемый результат
id | name | price | stock
1 | Laptop | 1399 | 13
11 | Tablet | 520 | 10
Upsert + RETURNING: и обновлённая, и вставленная строка возвращаются в итоговом виде.
SQL
CREATE TABLE orders_archive (LIKE orders);

WITH moved AS (
  DELETE FROM orders
  WHERE order_date < '2025-02-01'
  RETURNING *
)
INSERT INTO orders_archive
SELECT * FROM moved
RETURNING id, order_date;

Ожидаемый результат

 id | order_date
----+------------
  1 | 2025-01-15
  2 | 2025-01-15
(2 rows)
PostgreSQL: старые заказы переносятся в архив одной командой.

MERGE и ON DUPLICATE KEY UPDATE

MERGE — команда из стандарта SQL: сравнивает целевую таблицу с источником и для каждого случая отдельно описывает действие: WHEN MATCHED [AND условие] THEN UPDATE (или DELETE), WHEN NOT MATCHED THEN INSERT. В SQL Server и Oracle она давно, в PostgreSQL — с версии 15; в версии 17 добавлены RETURNING, merge_action() и WHEN NOT MATCHED BY SOURCE (строки, которых нет в источнике). Результат проверен в PGlite (PostgreSQL 18):

SQL
MERGE INTO products AS p
USING price_feed AS f
ON p.name = f.name
WHEN MATCHED AND p.price <> f.price THEN
  UPDATE SET price = f.price
WHEN NOT MATCHED THEN
  INSERT (name, price) VALUES (f.name, f.price)
RETURNING merge_action() AS action, p.id, p.name, p.price;

Ожидаемый результат

 action | id |  name  |  price
--------+----+--------+---------
 UPDATE |  1 | Laptop | 1399.00
 INSERT | 11 | Tablet |  520.00
(2 rows)
Цена Headphones та же — она не попала ни в одну ветку.

Тонкость: в PostgreSQL один INSERT ... ON CONFLICT DO UPDATE не может изменить одну и ту же строку дважды. Если в источнике один ключ встречается два раза, команда падает с ошибкой — сначала объедини дубликаты (GROUP BY). SQLite такую команду выполняет, и вторая строка обновляет первую. Кроме того, MERGE при параллельных INSERT может остановиться с ошибкой уникальности; для простого «upsert по ключу» документация PostgreSQL советует INSERT ... ON CONFLICT.

SQL
INSERT INTO products (name, price, stock)
VALUES ('Tablet', 520, 5),
       ('Tablet', 499, 3)
ON CONFLICT (name) DO UPDATE
SET stock = products.stock + excluded.stock;

Ожидаемый результат

ERROR:  ON CONFLICT DO UPDATE command cannot affect row a second time
HINT:  Ensure that no rows proposed for insertion within the same command have duplicate constrained values.

У MySQL свой синтаксис: ON DUPLICATE KEY UPDATE. Цель конфликта не пишется — срабатывает любой индекс PRIMARY KEY или UNIQUE. С 8.0.19 к предлагаемой строке обращаются по псевдониму (AS new); старая функция VALUES(столбец) с 8.0.20 считается устаревшей. MySQL сообщает 1 «затронутую строку» при вставке и 2 при обновлении.

SQL
-- MySQL 8.0.19+
INSERT INTO products (name, category, price, stock)
VALUES ('Laptop', 'Electronics', 1399, 5),
       ('Tablet', 'Electronics', 520, 10) AS new
ON DUPLICATE KEY UPDATE
  price = new.price,
  stock = products.stock + new.stock;
MySQL: тот же upsert, без цели конфликта.
ЗадачаSQLitePostgreSQLMySQLSQL Server
Обновить или вставитьON CONFLICT … DO UPDATEON CONFLICT … DO UPDATE, MERGE (15+)ON DUPLICATE KEY UPDATEMERGE
Пропустить дубликатыON CONFLICT DO NOTHINGON CONFLICT DO NOTHINGINSERT IGNORE (!)MERGE / NOT EXISTS
Вернуть изменённые строкиRETURNING (3.35+)RETURNINGнетOUTPUT
INSERT IGNORE превращает в предупреждения не только дубликаты, но и многие другие ошибки — используй осторожно.

Мини-проект: ночная загрузка, которую можно повторять

Идемпотентная операцияОпределение

Операция, которая даёт один и тот же результат, выполнена ли она один раз или десять. Если загрузка оборвалась на середине, её просто запускают снова — без дубликатов и двойного счёта.

  1. Промежуточная таблица

    Файл поставщика сначала загружается в price_feed.

  2. Upsert

    INSERT ... SELECT ... WHERE true ON CONFLICT (name) DO UPDATE. В SQLite WHERE после SELECT обязателен: без него слово ON читается как ON соединения, и получаем near "DO": syntax error.

  3. Обновляй только изменившееся

    WHERE products.price IS NOT excluded.price — равная цена не перезаписывается, журналы и триггеры не срабатывают впустую.

  4. Отчёт и проверка

    RETURNING показывает, что изменилось; второй запуск должен изменить 0 строк.

SQL
CREATE UNIQUE INDEX idx_products_name ON products (name);
CREATE TABLE price_feed (name TEXT, category TEXT, price REAL);
INSERT INTO price_feed VALUES
  ('Laptop',     'Electronics', 1399),
  ('Headphones', 'Electronics', 120.5),
  ('Tablet',     'Electronics', 520);

INSERT INTO products (name, category, price, stock)
SELECT name, category, price, 0 FROM price_feed WHERE true
ON CONFLICT (name) DO UPDATE
SET price = excluded.price
WHERE products.price IS NOT excluded.price
RETURNING id, name, price;
Ожидаемый результат
id | name | price
1 | Laptop | 1399
11 | Tablet | 520
Первый запуск: Laptop обновлён, Tablet вставлен, Headphones (цена та же) не тронуты.
SQL
CREATE UNIQUE INDEX idx_products_name ON products (name);
CREATE TABLE price_feed (name TEXT, category TEXT, price REAL);
INSERT INTO price_feed VALUES
  ('Laptop',     'Electronics', 1399),
  ('Headphones', 'Electronics', 120.5),
  ('Tablet',     'Electronics', 520);

INSERT INTO products (name, category, price, stock)
SELECT name, category, price, 0 FROM price_feed WHERE true
ON CONFLICT (name) DO UPDATE
SET price = excluded.price
WHERE products.price IS NOT excluded.price;   -- first run
INSERT INTO products (name, category, price, stock)
SELECT name, category, price, 0 FROM price_feed WHERE true
ON CONFLICT (name) DO UPDATE
SET price = excluded.price
WHERE products.price IS NOT excluded.price;   -- second run, the same file

SELECT changes() AS rows_changed_by_second_run,
       (SELECT COUNT(*) FROM products) AS products;
Ожидаемый результат
rows_changed_by_second_run | products
0 | 11
changes() — число строк, изменённых последней командой: второй запуск ничего не изменил, товаров по-прежнему 11.

Задание

Счётчик посещений: создай таблицу daily_visits (page TEXT PRIMARY KEY, visits INTEGER NOT NULL). Напиши upsert, который регистрирует одно посещение страницы (создаёт строку с 1, если её нет, или прибавляет 1), и выполни его по очереди для страниц /home, /sql, /home. Покажи таблицу, отсортировав по page.

Задание · SQL
CREATE TABLE daily_visits (page TEXT PRIMARY KEY, visits INTEGER NOT NULL);

-- three upserts: /home, /sql, /home

SELECT page, visits FROM daily_visits ORDER BY page;
Ожидаемый результат
page | visits
/home | 2
/sql | 1

Задание

Пришла поставка: 200 Notebook, 50 Pen set и 10 штук нового товара Tablet (категория Electronics, цена 520). Одним upsert увеличь остаток существующих товаров и добавь новый (уникальный индекс по products.name есть в стартовом коде). Затем покажи name и stock этих трёх товаров, отсортировав по названию.

Задание · SQL
CREATE UNIQUE INDEX idx_products_name ON products (name);

-- one INSERT ... ON CONFLICT here

SELECT name, stock FROM products
WHERE name IN ('Notebook', 'Pen set', 'Tablet')
ORDER BY name;
Ожидаемый результат
name | stock
Notebook | 700
Pen set | 350
Tablet | 10

Запомни

  • Upsert — одна атомарная команда: INSERT ... ON CONFLICT (ключ) DO NOTHING | DO UPDATE SET ...; цель должна совпадать с уникальным ограничением.
  • excluded — строка, которую пытались вставить, имя таблицы — существующая строка; DO UPDATE ... WHERE отсекает лишние обновления.
  • INSERT OR REPLACE — это удалить + вставить: новый id, потерянные столбцы, осиротевшие связи.
  • RETURNING сразу возвращает изменённые строки (SQLite 3.35+, PostgreSQL); в SQL Server — OUTPUT, в MySQL нет.
  • MERGE (PostgreSQL 15+, SQL Server, Oracle) и ON DUPLICATE KEY UPDATE в MySQL — другие формы той же задачи.
  • Хорошая загрузка идемпотентна: повторный запуск ничего не должен менять.
Заметил ошибку или неточность в уроке?

Проверь себя

Вопросов: 12. Каждый правильный ответ приносит XP.

Вопрос 1 / 12

Вопрос 1 / 12. Что такое excluded в ON CONFLICT (name) DO UPDATE SET price = excluded.price?