Вставка с обновлением, если запись уже есть
Очень частая задача: вставить строку, а если строка с таким же ключом уже существует — обновить её. Без UPSERT пришлось бы сначала делать SELECT, а потом по результату выбирать между INSERT и UPDATE — это два обращения к базе и гонка между ними.
В SQLite команда UPSERT решает всё одним запросом:
При первом запуске строка вставится. Запустите ещё раз с другой ценой, но тем же sku — и существующая строка обновится прямо на месте. Ни дубликата, ни ошибки.
Из чего состоит ON CONFLICT
Полная форма выражения:
INSERT INTO table (...) VALUES (...)
ON CONFLICT(conflict_target) DO UPDATE SET col = expr, ...
WHERE condition;
Здесь важны три вещи:
conflict_target— столбец или столбцы с ограничениемUNIQUEилиPRIMARY KEY, по которым вы ожидаете конфликт. По нему SQLite выбирает, за каким индексом следить.DO UPDATE SET ...— что изменить в существующей строке при конфликте. (ЛибоDO NOTHING, чтобы молча пропустить.)- Необязательный
WHERE— дополнительное условие, которое должно выполниться, иначе обновление не сработает.
Цель конфликта обязана совпадать с реальным уникальным ограничением. ON CONFLICT(price) не скомпилируется, если price не уникален — SQLite просто нечего считать конфликтом.
DO NOTHING: вставить, если нет, иначе пропустить
Вариант попроще. Пригодится, когда вы заливаете начальные данные или пишете события, а дубликаты надо тихо игнорировать:
Второй INSERT попадает в ту же запись по event_id, и в обычной ситуации SQLite выдал бы UNIQUE constraint failed. Но с DO NOTHING он просто пропускает вставку — без исключений и без затронутых строк.
Это та самая «идемпотентная вставка», ради которой обычно тянутся к INSERT OR IGNORE. UPSERT с DO NOTHING решает ту же задачу, но дружит с WHERE и RETURNING гораздо лучше.
Псевдотаблица excluded в sqlite
Когда срабатывает конфликт, в игре оказываются сразу две строки: уже существующая в таблице и новая, которую вы пытались вставить. SQLite даёт способ обращаться к обеим.
- Просто имя столбца (
price,name) — это существующая строка. excluded.column— это входящая строка, которая не прошла вставку.
quantity = quantity + excluded.quantity читается как «старое количество плюс новое». После двух вставок у A-100 количество станет равно 8. Такой шаблон — накопление значений в уже существующей строке — один из самых полезных приёмов UPSERT в SQLite.
Условный UPSERT с WHERE
Завершающий WHERE позволяет пропустить обновление, если условие не выполняется. Он применяется к существующей строке, а через excluded.* можно обращаться к данным, которые пришли на вставку:
Новая строка приходит с более старым updated_at, поэтому условие WHERE ложно и обновление не выполняется. У существующей записи остаётся более свежая цена. Поменяйте даты местами — и обновление сработает. Это классический сценарий «перезаписывать только более свежими данными».
SQLite upsert для нескольких строк
В VALUES можно передать сразу много строк, и ON CONFLICT применится к каждой из них независимо:
A-100 конфликтует и получает обновление. A-200 и A-300 — новые записи, они просто вставляются. Один запрос — а на выходе и вставка, и обновление. Очень удобный способ синхронизировать пачку записей из внешнего источника.
UPSERT против INSERT OR REPLACE
На первый взгляд кажется, что INSERT OR REPLACE делает то же самое. На самом деле — нет.
notes исчезли. INSERT OR REPLACE целиком удалил строку 1 и вставил на её место новую — любой столбец, который вы не указали, сбросился в NULL или к значению по умолчанию. Заодно сработали триггеры DELETE и каскады по внешним ключам ON DELETE.
UPSERT же сохраняет строку:
notes остался на месте. Поменялись только те столбцы, что перечислены в SET. По умолчанию используйте UPSERT, а к INSERT OR REPLACE обращайтесь только тогда, когда вам действительно нужна семантика «удалить и вставить заново».
Несколько целевых ограничений в UPSERT
Если строка может конфликтовать сразу по нескольким ограничениям, клаузы ON CONFLICT можно собирать в цепочку:
Срабатывает то ограничение, которое поймало конфликт первым, и выполняется DO UPDATE именно из этой ветки. На практике у большинства таблиц есть один очевидный «виновник» конфликта — первичный ключ или одна UNIQUE-колонка, — и больше одного ON CONFLICT обычно не нужно.
Типичные грабли
Вот что чаще всего ломает людям день:
- Нет подходящего уникального индекса — нет и UPSERT. Для
ON CONFLICT(col)колонкаcolдолжна бытьPRIMARY KEYлибо иметь ограничениеUNIQUE. Иначе SQLite упадёт с ошибкой «no such constraint». DO UPDATEне срабатывает, если конфликта нет. Это альтернатива вставке, а не дополнительное действие. Когда ключ встречается впервые, выполняется толькоINSERT.excluded— только на чтение. Из неё можно читать, писать в неё нельзя. ЦельюSETвсегда остаётся уже существующая строка.- Автоинкрементный
INTEGER PRIMARY KEY. Если не передавать id явно, при каждой вставке генерируется новый — конфликтовать просто не с чем. UPSERT имеет смысл только тогда, когда конфликтующая колонка приходит с детерминированным значением от вызывающего кода.
Дальше: RETURNING
UPSERT никак не сообщает, какие строки были вставлены, какие обновлены и какими стали итоговые значения. Для этого есть RETURNING — он возвращает затронутые строки прямо в том же запросе, без дополнительного SELECT. Об этом и поговорим дальше.
Часто задаваемые вопросы
Что такое UPSERT в SQLite?
UPSERT — это INSERT, который при нарушении ограничения UNIQUE или PRIMARY KEY превращается в UPDATE (или вообще ничего не делает). Записывается как INSERT ... ON CONFLICT(column) DO UPDATE SET ... или DO NOTHING. В SQLite появилось начиная с версии 3.24.0 (2018 год).
Что такое таблица excluded в UPSERT?
excluded — это псевдотаблица, в которой лежит та строка, которую вы пытались вставить. Внутри DO UPDATE SET ... к существующей строке обращаемся просто по имени колонки, а к «отклонённой» — через excluded.column. То есть SET price = excluded.price означает: «перезаписать price тем значением, которое пришло в новом INSERT».
Чем INSERT OR REPLACE отличается от UPSERT?
INSERT OR REPLACE удаляет конфликтующую строку и вставляет новую — а это срабатывание DELETE-триггеров, каскадное удаление по внешним ключам с ON DELETE CASCADE и сброс всех колонок к значениям по умолчанию. UPSERT же обновляет существующую строку на месте, и меняются только те колонки, которые вы перечислили в SET. Если вам не нужно именно «удалить и вставить заново», берите UPSERT.
Можно ли в SQLite сделать UPSERT сразу для нескольких строк?
Да, без проблем. Конструкция INSERT INTO t(...) VALUES (...), (...), (...) ON CONFLICT(col) DO UPDATE SET ... отлично работает. Каждая строка проверяется на конфликт по отдельности, а excluded внутри DO UPDATE ссылается именно на ту входящую строку, которая вызвала конфликт.
