UPSERT в PostgreSQL: вставить строку или обновить, если она уже есть

UPSERT — это короткое название для операции «вставь строку, а если такая строка уже существует — обнови её».

В обычной жизни это встречается постоянно.

Например:

• пользователь регистрируется по email;
• заказ приходит из внешней системы повторно;
• счётчик просмотров нужно увеличить;
• справочник товаров импортируется заново;
• событие из очереди может прийти второй раз.

Без UPSERT приходится писать неудобную логику:

1. Проверить, есть ли строка.
2. Если нет — вставить.
3. Если есть — обновить.

На первый взгляд всё просто. Но в реальном приложении несколько запросов могут выполняться одновременно.

Представьте две параллельные транзакции:

1. Первая проверила: пользователя с таким email нет.
2. Вторая тоже проверила: пользователя с таким email нет.
3. Первая вставила пользователя.
4. Вторая тоже пытается вставить пользователя и получает ошибку уникальности.

Вот это и есть классическая гонка.

В PostgreSQL такую задачу решает конструкция INSERT ... ON CONFLICT. Она делает операцию атомарно: база сама понимает, что делать при конфликте, и не заставляет вас вручную ловить ошибку, ставить блокировки или писать цикл «попробуй вставить, если не получилось — обнови».

Базовая идея INSERT ... ON CONFLICT

Начнём с простой таблицы пользователей.

У каждого пользователя есть уникальный email. Это важно: именно уникальное ограничение позволяет PostgreSQL понять, что две строки конфликтуют друг с другом.

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()
);

Здесь уникальность задана вот в этой части:

email text NOT NULL UNIQUE

Это значит: в таблице не может быть двух пользователей с одинаковым email.

Теперь попробуем вставить пользователя:

INSERT INTO users (email, name)
VALUES ('ann@example.com', 'Ann');

Если такого email ещё нет, строка спокойно добавится.

Но если пользователь с ann@example.com уже существует, PostgreSQL выдаст ошибку уникальности.

Чтобы не падать с ошибкой, можно добавить ON CONFLICT.

DO NOTHING: если строка уже есть, ничего не делать

Самый простой вариант — DO NOTHING.

INSERT INTO users (email, name)
VALUES ('ann@example.com', 'Ann')
ON CONFLICT (email) DO NOTHING;

Читается почти как обычная фраза:

Вставь пользователя. Если возник конфликт по email, ничего не делай.

Если строки ещё нет — PostgreSQL вставит её.

Если строка уже есть — запрос завершится без ошибки, но новую строку не добавит.

Это удобно для идемпотентных операций.

Идемпотентность означает: повторный запуск не ломает результат.

Например, если вы десять раз выполните такой запрос:

INSERT INTO users (email, name)
VALUES ('ann@example.com', 'Ann')
ON CONFLICT (email) DO NOTHING;

в таблице всё равно будет только один пользователь с таким email.

Конфликтная цель: по чему PostgreSQL понимает конфликт

В запросе:

ON CONFLICT (email) DO NOTHING

часть (email) называется конфликтной целью.

Она говорит PostgreSQL:

Конфликт нужно проверять по уникальности email.

Но здесь есть важное правило: конфликтная цель должна соответствовать реальному уникальному индексу, уникальному ограничению или первичному ключу.

Например, это сработает:

CREATE TABLE users (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email text NOT NULL UNIQUE,
    name text NOT NULL
);

Потому что email уникален.

А вот если создать таблицу без уникальности:

CREATE TABLE users (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email text NOT NULL,
    name text NOT NULL
);

и потом выполнить:

INSERT INTO users (email, name)
VALUES ('ann@example.com', 'Ann')
ON CONFLICT (email) DO NOTHING;

PostgreSQL не примет такой запрос, потому что по email нет уникального правила. База не может понять, какой именно конфликт вы имеете в виду.

DO UPDATE: если строка уже есть, обновить её

DO NOTHING просто пропускает конфликт.

Но часто нам нужно другое поведение: если строка уже есть, обновить в ней данные.

Например, пользователь поменял имя, а email остался тем же.

INSERT INTO users (email, name)
VALUES ('ann@example.com', 'Ann Smith')
ON CONFLICT (email)
DO UPDATE SET
    name = EXCLUDED.name,
    updated_at = now();

Читается так:

Попробуй вставить пользователя с email ann@example.com.
Если такой email уже есть, обнови существующую строку: поставь новое имя и обнови дату изменения.

Здесь появляется важное слово EXCLUDED.

Что такое EXCLUDED

EXCLUDED — это псевдотаблица со значениями, которые PostgreSQL пытался вставить, но не смог из-за конфликта.

Посмотрим на запрос:

INSERT INTO users (email, name)
VALUES ('ann@example.com', 'Ann Smith')
ON CONFLICT (email)
DO UPDATE SET
    name = EXCLUDED.name,
    updated_at = now();

Здесь EXCLUDED.name — это значение 'Ann Smith', которое было в части VALUES.

То есть PostgreSQL как бы говорит:

Я хотел вставить новую строку. Но такая строка уже есть. Тогда возьму значения из несостоявшейся вставки и использую их для обновления.

Это особенно удобно, когда столбцов много. Не нужно вручную повторять каждое значение.

Старое значение и новое значение

Внутри DO UPDATE можно обращаться и к текущей строке в таблице, и к новым значениям из EXCLUDED.

Например:

INSERT INTO users (email, name)
VALUES ('ann@example.com', 'Ann Smith')
ON CONFLICT (email)
DO UPDATE SET
    name = EXCLUDED.name;

Здесь:

• users.name — старое значение в таблице;
• EXCLUDED.name — новое значение, которое мы пытались вставить.

Можно даже комбинировать их.

Например, обновим имя только если новое значение не пустое:

INSERT INTO users (email, name)
VALUES ('ann@example.com', 'Ann Smith')
ON CONFLICT (email)
DO UPDATE SET
    name = COALESCE(EXCLUDED.name, users.name),
    updated_at = now();

COALESCE берёт первое значение, которое не равно NULL.

Если EXCLUDED.name не NULL, будет использовано новое имя. Если вдруг пришёл NULL, останется старое значение users.name.

Правда, в нашей таблице name объявлен как NOT NULL, поэтому вставить NULL в name не получится. Но сам приём полезен для столбцов, где NULL разрешён.

Например, для необязательного телефона:

CREATE TABLE customers (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email text NOT NULL UNIQUE,
    phone text,
    updated_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO customers (email, phone)
VALUES ('ann@example.com', NULL)
ON CONFLICT (email)
DO UPDATE SET
    phone = COALESCE(EXCLUDED.phone, customers.phone),
    updated_at = now();

Если новый телефон пришёл — обновим. Если не пришёл — сохраним старый.

Batch-вставка: сразу несколько строк

ON CONFLICT отлично работает не только с одной строкой, но и с несколькими.

INSERT INTO users (email, name)
VALUES
    ('ann@example.com', 'Ann'),
    ('bob@example.com', 'Bob'),
    ('kate@example.com', 'Kate')
ON CONFLICT (email)
DO UPDATE SET
    name = EXCLUDED.name,
    updated_at = now();

Такой запрос пытается вставить сразу трёх пользователей.

Для каждого PostgreSQL отдельно решит:

• если email новый — вставить строку;
• если email уже есть — обновить существующую строку.

Это очень удобно для импортов, синхронизаций и загрузки справочников.

DO UPDATE с WHERE: обновлять только при реальном изменении

Иногда обновлять строку каждый раз вредно.

Например, у нас есть таблица заказов:

CREATE TABLE orders (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id text NOT NULL UNIQUE,
    user_id bigint NOT NULL,
    amount numeric(10, 2) NOT NULL,
    status text NOT NULL,
    updated_at timestamptz NOT NULL DEFAULT now()
);

Внешняя система присылает заказ. Если заказ уже есть, мы хотим обновить его статус.

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();

Работает, но есть нюанс.

Если статус уже был shipped, запрос всё равно выполнит UPDATE. Из-за этого может измениться updated_at, могут сработать триггеры, появятся лишние записи в журнале изменений.

Чтобы обновлять строку только при реальном изменении, добавляют 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.

Обычное сравнение с NULL может дать неожиданный результат, потому что NULL означает неизвестное значение.

А IS DISTINCT FROM честно отвечает на вопрос:

Эти значения действительно отличаются?

Если статус не изменился, PostgreSQL не будет делать UPDATE.

Идемпотентные вставки: защита от повторов

Идемпотентность особенно важна, когда данные могут прийти повторно.

Например:

• сообщение из очереди доставилось два раза;
• внешний сервис повторил webhook;
• импорт случайно запустили ещё раз;
• пользователь дважды нажал кнопку оплаты.

Допустим, у заказа есть внешний идентификатор order_id.

INSERT INTO orders (order_id, user_id, amount, status)
VALUES ('ORD-1001', 42, 199.00, 'paid')
ON CONFLICT (order_id) DO NOTHING;

Сколько раз ни выполнить этот запрос, заказ ORD-1001 появится только один раз.

Это простой и надёжный способ защититься от дублей.

Если же при повторном приходе нужно не игнорировать заказ, а обновлять его статус, используйте DO UPDATE.

INSERT INTO orders (order_id, user_id, amount, status)
VALUES ('ORD-1001', 42, 199.00, 'paid')
ON CONFLICT (order_id)
DO UPDATE SET
    status = EXCLUDED.status,
    updated_at = now()
WHERE orders.status IS DISTINCT FROM EXCLUDED.status;

Такой запрос можно безопасно повторять: он не создаст дубль и не будет лишний раз обновлять строку, если данные не изменились.

Атомарные счётчики без потерянных обновлений

Классическая задача: считать визиты пользователя.

Плохой подход выглядит так:

SELECT visits
FROM users
WHERE email = 'ann@example.com';

Потом приложение прибавляет единицу и отправляет:

UPDATE users
SET visits = 11
WHERE email = 'ann@example.com';

Проблема в конкурентности.

Если два запроса одновременно прочитали visits = 10, оба могут записать 11. Хотя визитов было два, счётчик увеличится только на один. Это называется потерянное обновление.

С ON CONFLICT можно сделать лучше:

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;
• если пользователь уже есть — атомарно увеличиваем текущий visits на единицу.

Ключевая часть:

visits = users.visits + 1

Мы берём текущее значение из таблицы и увеличиваем его внутри одного SQL-запроса.

PostgreSQL сам выстроит конкурентные обновления в безопасный порядок на уровне строки. Поэтому обновления не потеряются.

RETURNING: сразу получить результат

После INSERT ... ON CONFLICT часто хочется сразу получить итоговую строку.

Например, увеличить счётчик и вернуть новое значение:

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()
RETURNING id, email, visits;

RETURNING вернёт строку после вставки или после обновления.

Это удобно для API: приложение отправило один запрос и сразу получило актуальное состояние.

ON CONFLICT ON CONSTRAINT: конфликт по имени ограничения

Иногда удобнее ссылаться не на столбцы, а на имя ограничения.

Например:

CREATE TABLE users (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email text NOT NULL,
    name text NOT NULL,
    CONSTRAINT users_email_key UNIQUE (email)
);

Тогда можно написать так:

INSERT INTO users (email, name)
VALUES ('ann@example.com', 'Ann')
ON CONFLICT ON CONSTRAINT users_email_key
DO UPDATE SET
    name = EXCLUDED.name;

Это полезно, когда уникальное ограничение составное или когда вы хотите явно показать, какое именно правило обрабатывает конфликт.

UPSERT по нескольким столбцам

Конфликтная цель может состоять из нескольких столбцов.

Допустим, пользователь может добавить товар в избранное. Одна и та же пара user_id и product_id должна быть уникальной.

CREATE TABLE favorite_products (
    user_id bigint NOT NULL,
    product_id bigint NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (user_id, product_id)
);

Теперь можно безопасно добавлять товар в избранное:

INSERT INTO favorite_products (user_id, product_id)
VALUES (42, 1001)
ON CONFLICT (user_id, product_id) DO NOTHING;

Если такая пара уже есть, PostgreSQL просто ничего не сделает.

Это хороший пример, где ON CONFLICT защищает от дублей на уровне базы, а не только на уровне приложения.

Частая ошибка: нет уникального индекса

Новички иногда думают, что ON CONFLICT сам найдёт похожую строку.

Например, если в таблице есть email, PostgreSQL якобы сам поймёт, что email не должен повторяться.

Но база так не работает.

Если вы хотите обрабатывать конфликт по email, это правило должно быть явно задано через UNIQUE или уникальный индекс.

Правильно:

CREATE UNIQUE INDEX idx_users_email
ON users (email);

После этого можно писать:

INSERT INTO users (email, name)
VALUES ('ann@example.com', 'Ann')
ON CONFLICT (email)
DO UPDATE SET
    name = EXCLUDED.name;

Без уникальности PostgreSQL не знает, что считать конфликтом.

Частая ошибка: дубликаты внутри одного VALUES

Есть ещё один неприятный случай.

Например:

INSERT INTO users (email, name)
VALUES
    ('ann@example.com', 'Ann'),
    ('ann@example.com', 'Ann Smith')
ON CONFLICT (email)
DO UPDATE SET
    name = EXCLUDED.name;

Здесь в одном запросе два раза приходит один и тот же email.

PostgreSQL может выдать ошибку вида:

ON CONFLICT DO UPDATE command cannot affect row a second time

Смысл ошибки: один и тот же запрос пытается дважды обновить одну и ту же строку.

Решение простое: дедуплицировать входные данные до вставки. То есть заранее оставить только одну строку на каждый уникальный ключ.

Если строка нарушает другой уникальный индекс

ON CONFLICT обрабатывает только ту конфликтную цель, которую вы указали.

Допустим, в таблице уникальны и email, и username.

CREATE TABLE accounts (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email text NOT NULL UNIQUE,
    username text NOT NULL UNIQUE
);

Запрос:

INSERT INTO accounts (email, username)
VALUES ('ann@example.com', 'ann')
ON CONFLICT (email) DO NOTHING;

обрабатывает конфликт только по email.

Если конфликт возникнет по username, но не по email, PostgreSQL всё равно выдаст ошибку.

Это правильно: вы явно сказали базе, какой конфликт хотите обработать.

Партиционированные таблицы

С партиционированными таблицами нужно быть внимательнее.

В PostgreSQL уникальность в партиционированной таблице обычно должна учитывать ключ партиционирования. Иначе база не сможет гарантировать уникальность между партициями так же просто, как в обычной таблице.

Например, если заказы партиционированы по дате, а вы хотите уникальность по order_id, нужно заранее продумать схему ключей и ограничений.

Для новичка главное правило такое: если ON CONFLICT не работает на партиционированной таблице так, как вы ожидали, проверьте уникальные ограничения и ключ партиционирования.

Отличие от MySQL

В MySQL похожая конструкция называется INSERT ... ON DUPLICATE KEY UPDATE.

Пример:

INSERT INTO users (email, name, visits)
VALUES ('ann@example.com', 'Ann', 1)
ON DUPLICATE KEY UPDATE
    name = VALUES(name),
    visits = visits + 1;

Главное отличие: в MySQL конфликтная цель явно не указывается. Конструкция срабатывает при конфликте по любому уникальному ключу или первичному ключу.

В PostgreSQL вы обычно пишете, какой конфликт обрабатываете:

ON CONFLICT (email)

Также в PostgreSQL для новых значений используется EXCLUDED, а в MySQL исторически использовалась функция VALUES().

По смыслу задачи похожи, но синтаксис и детали поведения отличаются.

А что в ClickHouse

В ClickHouse нет полноценного UPSERT в привычном смысле PostgreSQL.

ClickHouse — аналитическая колоночная СУБД. Она отлично подходит для больших объёмов событий, логов, метрик и аналитики, но не работает как классическая транзакционная база для точечных атомарных обновлений.

Там часто используют движки вроде ReplacingMergeTree, где дубликаты могут схлопываться позже, во время фоновых слияний.

Но это не то же самое, что строгий атомарный UPSERT в PostgreSQL.

Если вам нужно прямо сейчас гарантированно вставить строку или обновить существующую, без дублей и гонок, PostgreSQL подходит намного лучше.

Когда использовать DO NOTHING

DO NOTHING хорошо подходит, когда повторная вставка не должна ничего менять.

Например:

INSERT INTO favorite_products (user_id, product_id)
VALUES (42, 1001)
ON CONFLICT (user_id, product_id) DO NOTHING;

Типичные случаи:

• добавить в избранное;
• записать факт обработки события;
• импортировать справочник без обновления старых строк;
• защититься от повторного webhook;
• создать запись, если её ещё нет.

Главная мысль: если строка уже есть и вам не нужно её менять, выбирайте DO NOTHING.

Когда использовать DO UPDATE

DO UPDATE нужен, когда при конфликте данные должны обновиться.

Например:

INSERT INTO users (email, name)
VALUES ('ann@example.com', 'Ann Smith')
ON CONFLICT (email)
DO UPDATE SET
    name = EXCLUDED.name,
    updated_at = now();

Типичные случаи:

• обновить имя пользователя;
• синхронизировать товар из внешней системы;
• изменить статус заказа;
• увеличить счётчик;
• сохранить последнюю версию данных.

Главная мысль: если новая строка несёт более свежую информацию, используйте DO UPDATE.

Практический шаблон для реального проекта

Для большинства прикладных задач шаблон выглядит так:

INSERT INTO users (email, name, updated_at)
VALUES ('ann@example.com', 'Ann Smith', now())
ON CONFLICT (email)
DO UPDATE SET
    name = EXCLUDED.name,
    updated_at = EXCLUDED.updated_at
WHERE users.name IS DISTINCT FROM EXCLUDED.name
RETURNING id, email, name, updated_at;

Что здесь происходит:

• INSERT пытается добавить строку;
• ON CONFLICT (email) ловит конфликт по уникальному email;
• DO UPDATE обновляет существующую строку;
• EXCLUDED даёт доступ к новым значениям;
• WHERE защищает от лишнего обновления;
• RETURNING возвращает итоговую строку.

Это аккуратный и понятный вариант для API, импортов и повторяемых операций.

Главное

UPSERT — это операция «вставь, а если уже есть — обнови».

В PostgreSQL для этого используется INSERT ... ON CONFLICT.

ON CONFLICT DO NOTHING тихо пропускает конфликт и не создаёт дубль.

ON CONFLICT DO UPDATE обновляет существующую строку, если вставка упёрлась в уникальное ограничение.

Конфликтная цель должна соответствовать реальному уникальному индексу, уникальному ограничению или первичному ключу.

EXCLUDED хранит значения, которые PostgreSQL пытался вставить, но не смог из-за конфликта.

WHERE внутри DO UPDATE помогает не делать лишние обновления, если данные фактически не изменились.

Для счётчиков можно безопасно писать visits = users.visits + 1 внутри DO UPDATE: PostgreSQL выполнит это атомарно и защитит от потерянных обновлений.

Главная польза INSERT ... ON CONFLICT в том, что логика становится проще и надёжнее. Вы не гадаете, есть строка или нет, не ловите гонки вручную и не разносите одну операцию на несколько запросов. Вы отдаёте задачу базе данных — а PostgreSQL делает её правильно на уровне движка.