Пошук уроків, статей та іншого контенту
Обробіть конфлікт унікальності й реалізуйте вставку нового або оновлення наявного запису.
Upsert — це операція, яка:
вставляє новий запис, якщо конфлікту немає;
оновлює наявний запис, якщо виникає конфлікт унікальності.
У PostgreSQL Upsert реалізується конструкцією:
INSERT ...
ON CONFLICT ...
DO NOTHINGабо:
INSERT ...
ON CONFLICT ...
DO UPDATE SET ...Конфлікт визначається унікальним обмеженням або унікальним індексом. Найчастіше це первинний ключ або поле з обмеженням UNIQUE.
Якщо запис із таким унікальним значенням уже існує, можна просто пропустити вставку:
INSERT INTO users (email, name)
VALUES ('olena@example.com', 'Олена')
ON CONFLICT (email) DO NOTHING;Якщо email уже існує, запит завершиться успішно, але новий запис не буде вставлено.
Це відрізняється від звичайного INSERT, який завершився б помилкою порушення унікальності.
Щоб оновити наявний запис, використовується DO UPDATE:
INSERT INTO users (email, name)
VALUES ('olena@example.com', 'Олена Коваль')
ON CONFLICT (email)
DO UPDATE SET
name = EXCLUDED.name;Тут:
(email) — стовпець, за яким визначається конфлікт;
EXCLUDED.name — значення name із рядка, який PostgreSQL намагався вставити;
name у лівій частині — стовпець уже наявного рядка.
Назва EXCLUDED означає рядок, який був виключений зі вставки через конфлікт.
Наведений приклад можна виконати в PostgreSQL. Таблиця створюється як тимчасова, тому після завершення сесії вона буде видалена.
DROP TABLE IF EXISTS inventory;
CREATE TEMP TABLE inventory (
product_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
sku text NOT NULL,
product_name text NOT NULL,
quantity integer NOT NULL CHECK (quantity >= 0),
updated_at timestamptz NOT NULL DEFAULT now(),
CONSTRAINT inventory_sku_key UNIQUE (sku)
);
-- Додаємо початковий товар
INSERT INTO inventory (sku, product_name, quantity)
VALUES ('KB-001', 'Механічна клавіатура', 10);
-- Якщо товару з таким SKU немає — він буде вставлений.
-- Якщо товар уже існує — його кількість збільшиться.
INSERT INTO inventory AS current (sku, product_name, quantity)
VALUES ('KB-001', 'Механічна клавіатура', 3)
ON CONFLICT (sku)
DO UPDATE SET
product_name = EXCLUDED.product_name,
quantity = current.quantity + EXCLUDED.quantity,
updated_at = now()
RETURNING
current.product_id,
current.sku,
current.product_name,
current.quantity,
current.updated_at;
-- Для нового SKU буде вставлено новий рядок.
INSERT INTO inventory AS current (sku, product_name, quantity)
VALUES ('MS-002', 'Бездротова миша', 5)
ON CONFLICT (sku)
DO UPDATE SET
product_name = EXCLUDED.product_name,
quantity = current.quantity + EXCLUDED.quantity,
updated_at = now()
RETURNING
current.product_id,
current.sku,
current.product_name,
current.quantity,
current.updated_at;
SELECT *
FROM inventory
ORDER BY product_id;Після виконання таблиця міститиме приблизно такі дані:
для KB-001 кількість зміниться з 10 на 13;
для MS-002 буде створено новий запис із кількістю 5.
У конфлікті можна вказати стовпці унікального обмеження:
ON CONFLICT (email) DO NOTHINGЯкщо унікальність визначена складеним ключем, потрібно вказати всі його стовпці:
CREATE TABLE memberships (
user_id bigint NOT NULL,
team_id bigint NOT NULL,
role text NOT NULL,
PRIMARY KEY (user_id, team_id)
);
INSERT INTO memberships (user_id, team_id, role)
VALUES (10, 20, 'member')
ON CONFLICT (user_id, team_id)
DO UPDATE SET
role = EXCLUDED.role;Також можна посилатися безпосередньо на ім’я обмеження:
INSERT INTO memberships (user_id, team_id, role)
VALUES (10, 20, 'admin')
ON CONFLICT ON CONSTRAINT memberships_pkey
DO UPDATE SET
role = EXCLUDED.role;Посилання на ім’я обмеження корисне, коли:
у таблиці є кілька унікальних обмежень;
конфлікт визначається складеним ключем;
потрібно явно зафіксувати, яке саме обмеження використовується.
У частині DO UPDATE доступні два логічні рядки:
поточний рядок таблиці;
рядок, який намагалися вставити, через EXCLUDED.
Для однозначності цільову таблицю можна перейменувати за допомогою псевдоніма:
INSERT INTO inventory AS current (sku, product_name, quantity)
VALUES ('KB-001', 'Механічна клавіатура', 2)
ON CONFLICT (sku)
DO UPDATE SET
quantity = current.quantity + EXCLUDED.quantity;У цьому прикладі:
current.quantity — кількість, яка вже зберігається в таблиці;
EXCLUDED.quantity — кількість із нового запиту;
результатом буде сума двох значень.
Псевдонім особливо корисний, коли потрібно відрізняти стовпці цільової таблиці від значень у EXCLUDED.
ON CONFLICT працює не лише з одним рядком:
INSERT INTO inventory AS current (sku, product_name, quantity)
VALUES
('KB-001', 'Механічна клавіатура', 2),
('MS-002', 'Бездротова миша', 4),
('HD-003', 'USB-накопичувач', 7)
ON CONFLICT (sku)
DO UPDATE SET
product_name = EXCLUDED.product_name,
quantity = current.quantity + EXCLUDED.quantity,
updated_at = now();Для кожного рядка PostgreSQL окремо:
перевіряє конфлікт;
вставляє рядок, якщо конфлікту немає;
виконує оновлення, якщо конфлікт є.
Це ефективніше й безпечніше, ніж виконувати окремий запит для кожного запису в коді застосунку.
Іноді конфлікт потрібно обробити не завжди. Для цього в DO UPDATE можна додати WHERE:
INSERT INTO inventory AS current (sku, product_name, quantity)
VALUES ('KB-001', 'Механічна клавіатура', 20)
ON CONFLICT (sku)
DO UPDATE SET
product_name = EXCLUDED.product_name,
quantity = EXCLUDED.quantity,
updated_at = now()
WHERE EXCLUDED.quantity > current.quantity
RETURNING
current.sku,
current.quantity;У цьому прикладі оновлення відбудеться лише тоді, коли нова кількість більша за поточну.
Якщо умова WHERE не виконується:
помилки не буде;
рядок не буде оновлено;
він не буде повернений через RETURNING.
Це зручно для реалізації правила «зберігати тільки найвище значення»:
INSERT INTO scores AS current (user_id, score)
VALUES (42, 900)
ON CONFLICT (user_id)
DO UPDATE SET
score = EXCLUDED.score
WHERE EXCLUDED.score > current.score;RETURNING разом з UpsertRETURNING дає змогу отримати вставлений або оновлений рядок без додаткового SELECT:
INSERT INTO inventory AS current (sku, product_name, quantity)
VALUES ('KB-001', 'Механічна клавіатура', 1)
ON CONFLICT (sku)
DO UPDATE SET
quantity = current.quantity + EXCLUDED.quantity,
updated_at = now()
RETURNING
current.product_id,
current.sku,
current.quantity;Це корисно, коли застосунку потрібні:
ідентифікатор запису;
фактичне значення після оновлення;
значення, згенеровані базою даних;
час останньої зміни.
Для DO NOTHING RETURNING не поверне рядок, якщо конфлікт відбувся:
INSERT INTO users (email, name)
VALUES ('olena@example.com', 'Олена')
ON CONFLICT (email) DO NOTHING
RETURNING user_id, email;Якщо запис уже існував, результат буде порожнім, оскільки PostgreSQL не вставляв і не оновлював рядок.
Поширений, але небезпечний підхід виглядає так:
виконати SELECT, щоб перевірити наявність рядка;
якщо рядка немає — виконати INSERT;
якщо рядок є — виконати UPDATE.
Між SELECT та INSERT інший паралельний запит може вставити той самий ключ. У результаті виникає конфлікт або потрібна складна ручна логіка повторення.
INSERT ... ON CONFLICT об’єднує перевірку й дію в одну операцію PostgreSQL. Це правильний базовий механізм для конкурентного Upsert за унікальним ключем.
Під час DO UPDATE PostgreSQL працює з рядком, який спричинив конфлікт, і синхронізує доступ до нього відповідно до механізмів блокування бази даних.
ON CONFLICT обробляє конфлікти, пов’язані з:
PRIMARY KEY;
UNIQUE-обмеженням;
відповідним унікальним індексом.
Він не перетворює будь-яку помилку вставки на оновлення. Наприклад, порушення CHECK, помилка типу даних або помилка зовнішнього ключа не є конфліктом, який можна обробити через ON CONFLICT.
CREATE TABLE accounts (
account_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email text NOT NULL UNIQUE,
balance numeric NOT NULL CHECK (balance >= 0)
);У цій таблиці можна обробити повторний email, але не від’ємний balance:
INSERT INTO accounts (email, balance)
VALUES ('user@example.com', -100)
ON CONFLICT (email)
DO UPDATE SET
balance = EXCLUDED.balance;Такий запит завершиться помилкою через CHECK (balance >= 0).
У межах одного INSERT не можна кілька разів оновити той самий рядок через один конфлікт:
INSERT INTO inventory (sku, product_name, quantity)
VALUES
('KB-001', 'Клавіатура', 2),
('KB-001', 'Клавіатура', 3)
ON CONFLICT (sku)
DO UPDATE SET
quantity = inventory.quantity + EXCLUDED.quantity;Якщо обидва рядки конфліктують з одним і тим самим рядком таблиці, PostgreSQL може завершити запит помилкою про те, що один рядок не можна змінити двічі в межах однієї команди.
Перед Upsert потрібно попередньо об’єднати дублікати за ключем. Наприклад, для даних із джерела можна спочатку агрегувати кількість:
INSERT INTO inventory AS current (sku, product_name, quantity)
SELECT
source.sku,
max(source.product_name),
sum(source.quantity)
FROM (
VALUES
('KB-001', 'Механічна клавіатура', 2),
('KB-001', 'Механічна клавіатура', 3),
('MS-002', 'Бездротова миша', 4)
) AS source(sku, product_name, quantity)
GROUP BY source.sku
ON CONFLICT (sku)
DO UPDATE SET
product_name = EXCLUDED.product_name,
quantity = current.quantity + EXCLUDED.quantity,
updated_at = now();Після групування для кожного sku залишається один рядок.
EXCLUDEDEXCLUDED містить значення з нового рядка, а не поточні значення таблиці:
ON CONFLICT (sku)
DO UPDATE SET
quantity = EXCLUDED.quantity;Цей варіант замінює кількість новим значенням.
Якщо потрібно додати нову кількість до наявної, слід використовувати обидва джерела:
ON CONFLICT (sku)
DO UPDATE SET
quantity = inventory.quantity + EXCLUDED.quantity;Такий запит не спрацює, якщо email не має первинного ключа, унікального обмеження або відповідного унікального індексу:
INSERT INTO users (email, name)
VALUES ('user@example.com', 'Користувач')
ON CONFLICT (email)
DO UPDATE SET
name = EXCLUDED.name;Спочатку потрібно гарантувати унікальність на рівні схеми:
ALTER TABLE users
ADD CONSTRAINT users_email_key UNIQUE (email);Безумовне оновлення може перезаписати актуальні дані застарілим значенням. Якщо значення має змінюватися лише за певним правилом, додайте WHERE до DO UPDATE.
DO NOTHINGDO NOTHING не повертає вже наявний рядок через RETURNING. Якщо застосунку потрібен його ідентифікатор, потрібно обрати іншу модель запиту або явно використовувати DO UPDATE із коректною логікою оновлення.
Перед масовим Upsert перевіряйте, що в одному операторі немає кількох рядків із тим самим ключем конфлікту. За потреби агрегуйте або дедуплікуйте вхідні дані.
ON CONFLICT обробляє конфлікти унікальності під час INSERT.
DO NOTHING ігнорує конфлікт.
DO UPDATE виконує Upsert.
EXCLUDED містить значення рядка, який намагалися вставити.
Псевдонім цільової таблиці допомагає розрізняти поточні та нові значення.
WHERE у DO UPDATE дає змогу виконувати умовне оновлення.
RETURNING повертає вставлені або оновлені дані.
Upsert є атомарнішою альтернативою послідовності SELECT → INSERT або UPDATE.
Для масових операцій потрібно уникати дублювання одного ключа в межах одного INSERT.