Αυτό το άρθρο είναι προς το παρόν στα ρωσικά — η αγγλική μετάφραση βρίσκεται σε εξέλιξη.
UPSERT — это «вставь, а если строка уже есть, обнови» в одном атомарном запросе. В PostgreSQL за это отвечает INSERT ... ON CONFLICT. Он закрывает классическую гонку, когда вы сначала делаете SELECT, не находите строку, делаете INSERT — и ловите ошибку уникальности, потому что параллельная транзакция успела вставить ту же строку. ON CONFLICT решает это на уровне движка, без явных блокировок и без цикла «попробуй вставить, поймай ошибку, обнови».
Дальше — рабочая схема и примеры, которые можно копировать в psql.
Базовый синтаксис: DO NOTHING и DO UPDATE
Возьмём таблицу пользователей с уникальным email:
CREATE TABLE users (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email text NOT NULL UNIQUE,
name text NOT NULL,
visits int NOT NULL DEFAULT 0,
updated_at timestamptz NOT NULL DEFAULT now()
);
DO NOTHING — тихо пропустить конфликт, не падая с ошибкой:
INSERT INTO users (email, name)
VALUES ('ann@example.com', 'Ann')
ON CONFLICT (email) DO NOTHING;
DO UPDATE — обновить уже существующую строку. Важная деталь: после ON CONFLICT указывается конфликтная цель — столбец или столбцы с уникальным индексом (либо constraint), по которым PostgreSQL определяет столкновение:
INSERT INTO users (email, name)
VALUES ('ann@example.com', 'Ann Smith')
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name,
updated_at = now();
- Конфликтная цель обязана соответствовать реальному уникальному индексу или PK. Без подходящего индекса PostgreSQL не примет запрос.
- Можно ссылаться на имя constraint:
ON CONFLICT ON CONSTRAINT users_email_key DO UPDATE ....
EXCLUDED: значения из несостоявшейся вставки
EXCLUDED — это псевдотаблица со строкой, которую вы пытались вставить, но не смогли из-за конфликта. Через неё в DO UPDATE вы достаёте «новые» значения, не повторяя их вручную. Это особенно удобно для batch-вставок:
INSERT INTO users (email, name)
VALUES ('ann@example.com', 'Ann'),
('bob@example.com', 'Bob')
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name;
Можно комбинировать старое и новое значение — например, обновлять только если пришло непустое имя:
INSERT INTO users (email, name)
VALUES ('ann@example.com', NULL)
ON CONFLICT (email)
DO UPDATE SET name = COALESCE(EXCLUDED.name, users.name);
Здесь users.name — текущее значение в таблице, EXCLUDED.name — то, что мы пытались записать. Это даёт точечный контроль: какие столбцы перезаписывать, а какие сохранять.
Идемпотентные вставки и WHERE в DO UPDATE
Идемпотентность означает, что повторный запуск того же запроса не меняет результат. Это критично для повторных доставок сообщений, ретраев и импортов. Простейший вариант — DO NOTHING по натуральному ключу:
INSERT INTO orders (order_id, user_id, amount, status)
VALUES ('ORD-1001', 42, 199.00, 'paid')
ON CONFLICT (order_id) DO NOTHING;
Прогоните это десять раз — строка появится ровно один раз. Если же нужно обновлять, но только при реальном изменении данных, добавьте WHERE в DO UPDATE — это убирает лишние записи и не дёргает триггеры зря:
INSERT INTO orders (order_id, user_id, amount, status)
VALUES ('ORD-1001', 42, 199.00, 'shipped')
ON CONFLICT (order_id)
DO UPDATE SET status = EXCLUDED.status,
updated_at = now()
WHERE orders.status IS DISTINCT FROM EXCLUDED.status;
IS DISTINCT FROM корректно сравнивает значения с учётом NULL. Если статус не изменился — обновления не будет вовсе.
Атомарные счётчики
Классическая задача: считать визиты или просмотры без гонок. Вместо SELECT ... + UPDATE делайте инкремент прямо в DO UPDATE, ссылаясь на текущее значение строки:
INSERT INTO users (email, name, visits)
VALUES ('ann@example.com', 'Ann', 1)
ON CONFLICT (email)
DO UPDATE SET visits = users.visits + 1,
updated_at = now();
Первый запрос создаёт строку с visits = 1, каждый следующий атомарно увеличивает счётчик на единицу. Конкурентные транзакции выстраиваются в очередь на уровне строки, потерянных обновлений не будет. Добавьте RETURNING, чтобы сразу получить новое значение:
... DO UPDATE SET visits = users.visits + 1
RETURNING visits;
Подводные камни и отличия от MySQL
- Несколько конфликтов в одном запросе. Если строка нарушает сразу два разных уникальных индекса,
ON CONFLICT обрабатывает только указанную цель — по другому индексу всё равно прилетит ошибка.
- Дубликаты внутри одного VALUES. Нельзя дважды апдейтить одну и ту же строку в рамках одного запроса:
ON CONFLICT DO UPDATE command cannot affect row a second time. Дедуплицируйте входные данные до вставки.
- Партиционированные таблицы. Конфликтная цель должна включать ключ партиционирования.
В MySQL аналог — INSERT ... ON DUPLICATE KEY UPDATE. Он срабатывает по любому уникальному ключу (цель не указывается), а вместо EXCLUDED используется функция VALUES() (в новых версиях — алиас строки):
INSERT INTO users (email, name, visits)
VALUES ('ann@example.com', 'Ann', 1)
ON DUPLICATE KEY UPDATE
name = VALUES(name),
visits = visits + 1;
В ClickHouse полноценного UPSERT нет: движок не гарантирует уникальность на лету. Используют ReplacingMergeTree (дедупликация происходит фоново при слиянии) либо INSERT с последующей фильтрацией. Если вам нужны строгие идемпотентные вставки и атомарные счётчики прямо сейчас — это территория PostgreSQL и MySQL, а не аналитических колоночных СУБД.
UPSERT — это «вставь, а если строка уже есть, обнови» в одном атомарном запросе. В PostgreSQL за это отвечает
INSERT ... ON CONFLICT. Он закрывает классическую гонку, когда вы сначала делаетеSELECT, не находите строку, делаетеINSERT— и ловите ошибку уникальности, потому что параллельная транзакция успела вставить ту же строку.ON CONFLICTрешает это на уровне движка, без явных блокировок и без цикла «попробуй вставить, поймай ошибку, обнови».Дальше — рабочая схема и примеры, которые можно копировать в
psql.Базовый синтаксис: DO NOTHING и DO UPDATE
Возьмём таблицу пользователей с уникальным email:
CREATE TABLE users ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, email text NOT NULL UNIQUE, name text NOT NULL, visits int NOT NULL DEFAULT 0, updated_at timestamptz NOT NULL DEFAULT now() );DO NOTHING— тихо пропустить конфликт, не падая с ошибкой:INSERT INTO users (email, name) VALUES ('ann@example.com', 'Ann') ON CONFLICT (email) DO NOTHING;DO UPDATE— обновить уже существующую строку. Важная деталь: послеON CONFLICTуказывается конфликтная цель — столбец или столбцы с уникальным индексом (либо constraint), по которым PostgreSQL определяет столкновение:INSERT INTO users (email, name) VALUES ('ann@example.com', 'Ann Smith') ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name, updated_at = now();ON CONFLICT ON CONSTRAINT users_email_key DO UPDATE ....EXCLUDED: значения из несостоявшейся вставки
EXCLUDED— это псевдотаблица со строкой, которую вы пытались вставить, но не смогли из-за конфликта. Через неё вDO UPDATEвы достаёте «новые» значения, не повторяя их вручную. Это особенно удобно для batch-вставок:INSERT INTO users (email, name) VALUES ('ann@example.com', 'Ann'), ('bob@example.com', 'Bob') ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;Можно комбинировать старое и новое значение — например, обновлять только если пришло непустое имя:
INSERT INTO users (email, name) VALUES ('ann@example.com', NULL) ON CONFLICT (email) DO UPDATE SET name = COALESCE(EXCLUDED.name, users.name);Здесь
users.name— текущее значение в таблице,EXCLUDED.name— то, что мы пытались записать. Это даёт точечный контроль: какие столбцы перезаписывать, а какие сохранять.Идемпотентные вставки и WHERE в DO UPDATE
Идемпотентность означает, что повторный запуск того же запроса не меняет результат. Это критично для повторных доставок сообщений, ретраев и импортов. Простейший вариант —
DO NOTHINGпо натуральному ключу:INSERT INTO orders (order_id, user_id, amount, status) VALUES ('ORD-1001', 42, 199.00, 'paid') ON CONFLICT (order_id) DO NOTHING;Прогоните это десять раз — строка появится ровно один раз. Если же нужно обновлять, но только при реальном изменении данных, добавьте
WHEREвDO UPDATE— это убирает лишние записи и не дёргает триггеры зря:INSERT INTO orders (order_id, user_id, amount, status) VALUES ('ORD-1001', 42, 199.00, 'shipped') ON CONFLICT (order_id) DO UPDATE SET status = EXCLUDED.status, updated_at = now() WHERE orders.status IS DISTINCT FROM EXCLUDED.status;IS DISTINCT FROMкорректно сравнивает значения с учётомNULL. Если статус не изменился — обновления не будет вовсе.Атомарные счётчики
Классическая задача: считать визиты или просмотры без гонок. Вместо
SELECT ... + UPDATEделайте инкремент прямо вDO UPDATE, ссылаясь на текущее значение строки:INSERT INTO users (email, name, visits) VALUES ('ann@example.com', 'Ann', 1) ON CONFLICT (email) DO UPDATE SET visits = users.visits + 1, updated_at = now();Первый запрос создаёт строку с
visits = 1, каждый следующий атомарно увеличивает счётчик на единицу. Конкурентные транзакции выстраиваются в очередь на уровне строки, потерянных обновлений не будет. ДобавьтеRETURNING, чтобы сразу получить новое значение:... DO UPDATE SET visits = users.visits + 1 RETURNING visits;Подводные камни и отличия от MySQL
ON CONFLICTобрабатывает только указанную цель — по другому индексу всё равно прилетит ошибка.ON CONFLICT DO UPDATE command cannot affect row a second time. Дедуплицируйте входные данные до вставки.В MySQL аналог —
INSERT ... ON DUPLICATE KEY UPDATE. Он срабатывает по любому уникальному ключу (цель не указывается), а вместоEXCLUDEDиспользуется функцияVALUES()(в новых версиях — алиас строки):-- MySQL INSERT INTO users (email, name, visits) VALUES ('ann@example.com', 'Ann', 1) ON DUPLICATE KEY UPDATE name = VALUES(name), visits = visits + 1;В ClickHouse полноценного UPSERT нет: движок не гарантирует уникальность на лету. Используют
ReplacingMergeTree(дедупликация происходит фоново при слиянии) либоINSERTс последующей фильтрацией. Если вам нужны строгие идемпотентные вставки и атомарные счётчики прямо сейчас — это территория PostgreSQL и MySQL, а не аналитических колоночных СУБД.