Что такое UPSERT в PostgreSQL и когда применять?
Что такое UPSERT
UPSERT — это комбинация слов UPdate + inSERT. Операция атомарно решает дилемму: если запись с указанным уникальным ключом отсутствует — вставить новую, если существует — обновить существующую.
В PostgreSQL UPSERT реализован через конструкцию INSERT ... ON CONFLICT, появившуюся в версии 9.5.
Синтаксис
INSERT INTO таблица (колонки)
VALUES (значения)
ON CONFLICT (конфликтующая_колонка)
DO UPDATE SET колонка = EXCLUDED.колонка;
Ключевое слово EXCLUDED ссылается на значения, которые пытались вставить, но не смогли из-за конфликта.
Альтернатива — DO NOTHING, если при конфликте нужно просто проигнорировать вставку без ошибки.
Когда применять
- Синхронизация данных: импорт внешних данных, где дубли — норма, а не исключение.
- Счётчики и агрегаты: инкрементировать просмотры, лайки, события — без предварительного SELECT.
- Идемпотентные операции: повторные вызовы API или очередей не должны ломать данные.
- Кэш-таблицы: обновление снапшотов, которые периодически пересчитываются.
Примеры
Обновление при конфликте
-- Вставляем пользователя или обновляем email, если логин уже занят
INSERT INTO users (username, email, updated_at)
VALUES ('ivan', 'ivan@example.com', NOW())
ON CONFLICT (username)
DO UPDATE SET
email = EXCLUDED.email,
updated_at = EXCLUDED.updated_at;
Игнорирование дублей
-- Добавляем тег к статье, дубль просто пропускаем
INSERT INTO article_tags (article_id, tag_id)
VALUES (42, 7)
ON CONFLICT (article_id, tag_id)
DO NOTHING;
Инкрементирование счётчика
-- Увеличиваем счётчик просмотров страницы
INSERT INTO page_views (page_slug, views, last_viewed_at)
VALUES ('/about', 1, NOW())
ON CONFLICT (page_slug)
DO UPDATE SET
views = page_views.views + 1,
last_viewed_at = NOW();
Важные ограничения
ON CONFLICTсрабатывает только на уникальные ограничения (UNIQUE, PRIMARY KEY) — не на произвольные условия.- Конфликтная колонка должна быть явно указана, либо можно использовать
ON CONFLICT ON CONSTRAINT constraint_name. - UPSERT не заменяет
MERGE(появился в PostgreSQL 15) — для сложных условий слияния лучше использоватьMERGE.
Атомарность
Вся операция выполняется атомарно: не нужно вручную делать SELECT → проверка → INSERT/UPDATE в транзакции. Это исключает race condition в конкурентной среде.
Что хочет услышать интервьюер
Кандидат знает синтаксис INSERT ... ON CONFLICT и понимает, что это атомарная операция
Понимание ключевого слова EXCLUDED и что оно хранит пытавшиеся вставиться значения
Знание разницы между DO UPDATE и DO NOTHING
Понимание, что ON CONFLICT работает только с уникальными ограничениями (UNIQUE/PRIMARY KEY)
Практические примеры применения: счётчики, синхронизация, идемпотентность
Пример: UPSERT через node-postgres (pg)
import { Pool } from 'pg';
const pool = new Pool({ connectionString: process.env.DATABASE_URL });
// Сохраняем настройки пользователя: создаём или обновляем
async function upsertUserSettings(
userId: number,
theme: string,
language: string
): Promise<void> {
await pool.query(
`INSERT INTO user_settings (user_id, theme, language, updated_at)
VALUES ($1, $2, $3, NOW())
ON CONFLICT (user_id)
DO UPDATE SET
theme = EXCLUDED.theme,
language = EXCLUDED.language,
updated_at = EXCLUDED.updated_at`,
[userId, theme, language]
);
}
// Атомарно инкрементируем счётчик просмотров
async function incrementPageViews(slug: string): Promise<number> {
const result = await pool.query<{ views: number }>(
`INSERT INTO page_views (slug, views)
VALUES ($1, 1)
ON CONFLICT (slug)
DO UPDATE SET views = page_views.views + 1
RETURNING views`,
[slug]
);
return result.rows[0].views;
}
Типичные ошибки
Путают UPSERT с обычным UPDATE — не понимают, что при отсутствии строки произойдёт INSERT
Забывают, что конфликтная колонка обязана иметь UNIQUE или PRIMARY KEY ограничение
Не знают ключевое слово EXCLUDED и пытаются использовать значения из VALUES напрямую
Используют SELECT + IF EXISTS вместо атомарного UPSERT, создавая race condition
Путают синтаксис PostgreSQL с MySQL (INSERT ... ON DUPLICATE KEY UPDATE)
AI-тренажеры
4.7
Практика в студии
