Как работает UPSERT в SQLite: ON CONFLICT и conflict target
UPSERT предназначен для обработки вставок, позволяя решать конфликты уникальности, что делает его более гибким по сравнению со стандартными SQL конструкциями
UPSERT — это обычный оператор INSERT, за которым следует одно или несколько предложений ON CONFLICT (upsert-clause), как показано на диаграмме синтаксиса выше
Синтаксис между «ON CONFLICT» и «DO» определяется как «цель конфликта» (conflict target), которая указывает на уникальное ограничение, инициирующее upsert
Для всех предложений ON CONFLICT (upsert-clause), кроме последнего, цель конфликта обязательна.
Если вставка нарушает уникальное ограничение, она игнорируется (DO NOTHING) и выполняется действие DO NOTHING или DO UPDATE. Каждое предложение ON CONFLICT (upsert-clause) проверяется по порядку, и выполнение последующих предложений прекращается, если предыдущее уже сработало
Для каждого INSERT выполняется лишь одна конструкция ON CONFLICT — первая, у которой есть совпадение цели конфликта. Если эта конструкция срабатывает, все последующие конструкции для данной строки игнорируются
В случае многострочной вставки решение об upsert принимается отдельно для каждой строки вставки
Обработка UPSERT применяется только к уникальным ограничениям. Уникальное ограничение — это явное ограничение UNIQUE или PRIMARY KEY в операторе CREATE TABLE или уникальный индекс. UPSERT не влияет на нарушения ограничений NOT NULL, CHECK и ограничений внешнего ключа.
Имена столбцов в выражениях DO UPDATE ссылаются на исходное, неизменённое значение столбца — до попытки выполнения INSERT. Чтобы использовать значение, которое было бы вставлено при отсутствии конфликта, нужно добавить к имени столбца специальный квалификатор таблицы «excluded.»
Примеры UPSERT: DO NOTHING и DO UPDATE
Несколько примеров помогут проиллюстрировать работу UPSERT.
CREATE TABLE vocabulary(word TEXT PRIMARY KEY, count INT DEFAULT 1);
INSERT INTO vocabulary(word) VALUES('jovial')
ON CONFLICT(word) DO UPDATE SET count=count+1;
Приведённый выше upsert вставляет новое слово «jovial», если его ещё нет в словаре, или, если оно уже есть, увеличивает счётчик на единицу. Выражение «count+1» можно также записать как «vocabulary.count». PostgreSQL требует второй формы, однако SQLite принимает обе
CREATE TABLE phonebook(name TEXT PRIMARY KEY, phonenumber TEXT);
INSERT INTO phonebook(name,phonenumber) VALUES('Alice','704-555-1212')
ON CONFLICT(name) DO UPDATE SET phonenumber=excluded.phonenumber;
Во втором примере выражение в конструкции DO UPDATE имеет вид «excluded.phonenumber». Префикс «excluded.» указывает, что «phonenumber» ссылается на значение номера телефона, которое было бы вставлено при отсутствии конфликта. Таким образом, эффект upsert состоит в том, чтобы вставить номер телефона Алисы, если его ещё нет, или перезаписать любой существующий номер телефона Алисы новым
Обратите внимание, что конструкция DO UPDATE действует только на ту единственную строку, которая вызвала ошибку ограничения при INSERT. Нет необходимости включать конструкцию WHERE, ограничивающую действие именно этой строкой. Единственное назначение конструкции WHERE в конце DO UPDATE — опционально превратить DO UPDATE в операцию без действия в зависимости от исходных и/или новых значений. Например:
CREATE TABLE phonebook2( name TEXT PRIMARY KEY, phonenumber TEXT, validDate DATE
);
INSERT INTO phonebook2(name,phonenumber,validDate) VALUES('Alice','704-555-1212','2018-05-08') ON CONFLICT(name) DO UPDATE SET phonenumber=excluded.phonenumber, validDate=excluded.validDate WHERE excluded.validDate>phonebook2.validDate;
В этом последнем примере запись в phonebook2 обновляется только в том случае, если validDate вновь вставляемого значения новее, чем у записи, уже находящейся в таблице. Если таблица уже содержит запись с тем же именем и актуальной датой validDate, конструкция WHERE превращает DO UPDATE в операцию без действия.
Неоднозначность разбора UPSERT-выражений
Когда оператор INSERT, к которому прикреплён UPSERT, получает значения из оператора SELECT, возникает потенциальная неоднозначность при разборе. Синтаксический анализатор может не определить, вводит ли ключевое слово «ON» конструкцию UPSERT или является конструкцией ON соединения (join). Чтобы обойти это, оператор SELECT всегда должен содержать конструкцию WHERE, даже если эта конструкция WHERE — просто «WHERE true».
Неоднозначное использование ON:
INSERT INTO t1 SELECT * FROM t2
ON CONFLICT(x) DO UPDATE SET y=excluded.y;
Неоднозначность устранена с помощью конструкции WHERE:
INSERT INTO t1 SELECT * FROM t2 WHERE true
ON CONFLICT(x) DO UPDATE SET y=excluded.y;
Я рекомендую всегда добавлять WHERE true при использовании SELECT в качестве источника данных для INSERT с UPSERT — это простой способ избежать трудноуловимых ошибок разбора.
3. Ограничения
В настоящее время UPSERT не работает с виртуальными таблицами.
Алгоритм разрешения конфликтов для операции обновления в конструкции DO UPDATE всегда равен ABORT. Иными словами, поведение такое, как если бы конструкция DO UPDATE была фактически записана как «DO UPDATE OR ABORT». Если конструкция DO UPDATE обнаруживает любое нарушение ограничения, весь оператор INSERT откатывается и останавливается
Это справедливо даже в том случае, если конструкция DO UPDATE содержится внутри оператора INSERT или триггера, задающего иной алгоритм разрешения конфликтов.
Типичные ошибки при использовании UPSERT в SQLite
На практике при работе с UPSERT в SQLite я встречал несколько повторяющихся ошибок, которые стоит разобрать отдельно.
Отсутствие цели конфликта там, где она обязательна. Цель конфликта можно опустить только в последней конструкции ON CONFLICT. Во всех предшествующих конструкциях она обязательна — иначе SQLite вернёт ошибку синтаксиса.
Путаница между именами столбцов и префиксом excluded.. Имена столбцов без префикса в выражениях DO UPDATE ссылаются на значения, уже находящиеся в таблице. Чтобы обратиться к значению, которое пытались вставить, необходимо использовать квалификатор excluded.. Смешение этих двух форм — одна из самых частых причин неожиданного поведения upsert.
Попытка применить UPSERT к виртуальным таблицам. UPSERT не работает с виртуальными таблицами — это задокументированное ограничение, которое нельзя обойти на уровне синтаксиса.
Ожидание, что DO UPDATE затронет несколько строк. Конструкция DO UPDATE действует строго на одну строку — ту, которая вызвала конфликт. Если нужно обновить несколько строк, потребуется отдельный UPDATE.
Игнорирование алгоритма ABORT в DO UPDATE. Поскольку алгоритм разрешения конфликтов внутри DO UPDATE всегда равен ABORT, любое нарушение ограничения в процессе обновления откатит весь оператор INSERT, даже если внешний контекст задаёт другой алгоритм.
5. История
Синтаксис UPSERT был добавлен в SQLite начиная с версии 3.24.0 (2018-06-04). Первоначальная реализация точно следовала синтаксису PostgreSQL в том смысле, что допускала только одно предложение ON CONFLICT (upsert-clause) и требовала указания цели конфликта (conflict-target) для DO UPDATE.
Синтаксис был обобщён: разрешено использование нескольких предложений ON CONFLICT (upsert-clause) и обработка конфликтов уникальности при вставке строк через DO UPDATE без указания цели конфликта (conflict-target) — начиная с версии SQLite 3.35.0 (2021-03-12)
По моему опыту, именно расширение синтаксиса в версии 3.35.0 сделало UPSERT по-настоящему удобным инструментом для сложных сценариев вставки с несколькими возможными конфликтами.
Ответы на эти вопросы могут быть для вас полезными
Чем UPSERT отличается от обычного INSERT OR REPLACE в SQLite?
INSERT OR REPLACE удаляет конфликтующую строку и вставляет новую, что приводит к потере всех значений столбцов, не указанных явно, и к изменению rowid. UPSERT с DO UPDATE обновляет только указанные столбцы существующей строки, сохраняя остальные данные нетронутыми.
На какие типы ограничений реагирует UPSERT?
Только на ограничения уникальности: явные UNIQUE, PRIMARY KEY и уникальные индексы. Нарушения NOT NULL, CHECK, ограничений внешнего ключа и триггеров UPSERT не перехватывает.
Что означает префикс excluded. в конструкции DO UPDATE?
Префикс excluded. обращается к значению, которое пытались вставить в ходе текущей операции INSERT, — то есть к «отклонённой» строке. Без этого префикса имя столбца ссылается на значение, уже хранящееся в таблице.
Можно ли использовать несколько предложений ON CONFLICT (upsert-clause) в одном операторе INSERT?
Да, начиная с SQLite 3.35.0. Конструкции проверяются по порядку, и для каждой строки срабатывает только первая подходящая. Последняя конструкция ON CONFLICT может не содержать цели конфликта и тогда перехватывает любое оставшееся нарушение уникальности.
Что происходит, если конструкция DO UPDATE сама нарушает ограничение?
Весь оператор INSERT откатывается. Алгоритм разрешения конфликтов внутри DO UPDATE всегда равен ABORT, и это поведение нельзя переопределить через OR IGNORE или OR REPLACE на уровне внешнего оператора.
