1. Синтаксис

upsert-clause: hide

2. Описание

UPSERT это пункт, добавленный к INSERT, который заставляет INSERT вести себя как UPDATE или не делать ничего, если INSERT нарушил бы ограничение уникальности. UPSERT это не стандартный SQL. UPSERT в SQLite следует за синтаксисом, установленным PostgreSQL с обобщениями.

UPSERT это обычный INSERT, который сопровождается одним или более пунктами ON CONFLICT, как показано на диаграмме синтаксиса выше.

Синтаксис между "ON CONFLICT" и "DO" называют "целью конфликта". Цель конфликта определяет ограничение уникальности, которое вызовет upsert. Цель конфликта может быть опущена на последнем ON CONFLICT в INSERT, но требуется для любого другого ON CONFLICT.

Если операция по вставке заставила бы целевое ограничение уникальности конфликта терпеть неудачу, то вставка опущена и соответствующий DO NOTHING или DO UPDATE выполняется вместо этого. ON CONFLICT проверяется в определенном порядке. Если последний ON CONFLICT опускает цель конфликта, то это будет работать, если терпит неудачу любое ограничение уникальности, которое не захватывается предшествующим ON CONFLICT.

Только единственный пункт ON CONFLICT, определенно первый ON CONFLICT с соответствующей целью конфликта, может работать для каждой строки в INSERT. Когда ON CONFLICT работает, весь последующий ON CONFLICT обойден для той строки.

В случае многострочной вставки upsert решение принято отдельно для каждой строки вставки.

Обработка UPSERT происходит только для ограничений уникальности. "Ограничением уникальности" является явный UNIQUE или ограничение PRIMARY KEY в рамках CREATE TABLE или уникальный индекс. UPSERT не вмешивается ограничения NOT NULL, CHECK, внешнего ключа или для ограничений, которые осуществляются, используя триггеры.

Имена столбцов в выражениях DO UPDATE относятся к первоначальному неизменному значению колонки перед предпринятым INSERT. Для применения значения, которое было бы вставлено, если бы не ограничение, добавьте к имени столбца квалификатор таблицы "excluded.".

2.1. Примеры

Некоторые примеры помогут иллюстрировать, как 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" относиться к значению phonenumber, которое было бы вставлено, если бы там не было никакого конфликта. Следовательно, эффект upsert состоит в том, чтобы вставить phonenumber для Alice, если ни один не существует, или переписывать какой-либо предшествующий phonenumber для Alice, если он уже есть.

Обратите внимание на то, что 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 ничего не менять.

2.2. Парсинг двусмысленности

Когда INSERT, к которому приложен UPSERT, берет свои значения от SELECT, есть потенциальная двусмысленность парсинга. Анализатор не мог бы быть в состоянии сказать, вводит ли ключевое слово "ON" UPSERT или если это пункт ON соединения. Чтобы работать вокруг этого, оператор 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;

3. Ограничения

UPSERT в настоящее время не работает на виртуальных таблицах.

Алгоритмом разрешения конфликтов для операции по обновлению DO UPDATE всегда будет ABORT. Другими словами, поведение состоит в том, как будто DO UPDATE был на самом деле написан как "DO UPDATE OR ABORT". Если DO UPDATE сталкивается с любым ограничительным нарушением, весь оператор INSERT, откатывается назад и останавливается. Это верно, даже если DO UPDATE содержится в операторе INSERT или триггере, который определяет некоторый другой алгоритм разрешения конфликтов.

4. История

UPSERT был добавлен к SQLite с версии 3.24.0 (2018-06-04). Оригинальное внедрение тесно следовало синтаксису PostgreSQL, в котором оно разрешило только единственный ON CONFLICT и оно потребовало цели конфликта на DO UPDATE. Синтаксис был обобщен, чтобы разрешить многократный ON CONFLICT и позволить DO UPDATE без цели конфликта в версии в SQLite version 3.35.0 (2021-03-12).