Каждую ночь поставщик присылает прайс-лист: часть товаров в магазине уже есть, часть — новые. Наивный путь — два шага: проверить через 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 даёт ошибку:
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 конфликтующая строка молча отбрасывается, а остальные вставляются. Столбцы в скобках — цель конфликта, они должны совпадать с существующим уникальным ограничением:
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
Внутри DO UPDATE SET видны две строки. **excluded — строка, которую пытались вставить и которая «осталась за бортом» из-за конфликта; имя таблицы (или столбец без префикса) — строка, уже лежащая в базе. Ниже цена заменяется новой, а остаток прибавляется** к старому:
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
id: цена 1399, остаток 8 + 5 = 13.Классическое применение — счётчик: при первом посещении страницы строка создаётся, дальше растёт только число. Программе не нужно спрашивать «а строка уже есть?»:
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) всегда хранить самый свежий остаток?
Показать решениеСкрыть решение
Решение
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, а заказы, ссылавшиеся на неё, становятся «сиротами»:
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, с выражениями и псевдонимами:
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 без дополнительного запроса.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 с нулевым остатком не тронут).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
RETURNING: и обновлённая, и вставленная строка возвращаются в итоговом виде.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)
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):
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)
Тонкость: в PostgreSQL один INSERT ... ON CONFLICT DO UPDATE не может изменить одну и ту же строку дважды. Если в источнике один ключ встречается два раза, команда падает с ошибкой — сначала объедини дубликаты (GROUP BY). SQLite такую команду выполняет, и вторая строка обновляет первую. Кроме того, MERGE при параллельных INSERT может остановиться с ошибкой уникальности; для простого «upsert по ключу» документация PostgreSQL советует INSERT ... ON CONFLICT.
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 при обновлении.
-- 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;| Задача | SQLite | PostgreSQL | MySQL | SQL Server |
|---|---|---|---|---|
| Обновить или вставить | ON CONFLICT … DO UPDATE | ON CONFLICT … DO UPDATE, MERGE (15+) | ON DUPLICATE KEY UPDATE | MERGE |
| Пропустить дубликаты | ON CONFLICT DO NOTHING | ON CONFLICT DO NOTHING | INSERT IGNORE (!) | MERGE / NOT EXISTS |
| Вернуть изменённые строки | RETURNING (3.35+) | RETURNING | нет | OUTPUT |
INSERT IGNORE превращает в предупреждения не только дубликаты, но и многие другие ошибки — используй осторожно.Мини-проект: ночная загрузка, которую можно повторять
Идемпотентная операцияОпределение
Операция, которая даёт один и тот же результат, выполнена ли она один раз или десять. Если загрузка оборвалась на середине, её просто запускают снова — без дубликатов и двойного счёта.
Промежуточная таблица
Файл поставщика сначала загружается в
price_feed.Upsert
INSERT ... SELECT ... WHERE true ON CONFLICT (name) DO UPDATE. В SQLiteWHEREпослеSELECTобязателен: без него словоONчитается какONсоединения, и получаемnear "DO": syntax error.Обновляй только изменившееся
WHERE products.price IS NOT excluded.price— равная цена не перезаписывается, журналы и триггеры не срабатывают впустую.Отчёт и проверка
RETURNINGпоказывает, что изменилось; второй запуск должен изменить 0 строк.
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
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.
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 этих трёх товаров, отсортировав по названию.
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 — другие формы той же задачи.- Хорошая загрузка идемпотентна: повторный запуск ничего не должен менять.