Junior: питання на співбесіді з теми «Можливості SQL у PostgreSQL»

Upsert - «вставити, а якщо такий запис уже є, - оновити». У PostgreSQL це один атомарний запит:

INSERT INTO product_prices (product_id, currency, amount)
VALUES (42, 'UAH', 1999)
ON CONFLICT (product_id, currency)
DO UPDATE SET amount = EXCLUDED.amount, updated_at = now();

• ON CONFLICT (колонки) - на якому унікальному обмеженні чи індексі визначати конфлікт;
• EXCLUDED - рядок, який намагалися вставити: з нього беруть нові значення;
• DO NOTHING - просто пропустити дублікат.

INSERT INTO subscriptions (user_id, list_id)
VALUES (7, 3)
ON CONFLICT DO NOTHING;

Обов'язкова умова: на колонках з ON CONFLICT має бути унікальний індекс чи обмеження. Інакше:

ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

Умовне оновлення - не перезаписувати свіжіші дані старішими:

INSERT INTO stock (sku, qty, synced_at) VALUES ('A-1', 10, '2026-10-04 09:00')
ON CONFLICT (sku) DO UPDATE
SET qty = EXCLUDED.qty, synced_at = EXCLUDED.synced_at
WHERE stock.synced_at < EXCLUDED.synced_at;

Лічильник одним запитом:

INSERT INTO page_views (page_id, day, views) VALUES (5, current_date, 1)
ON CONFLICT (page_id, day) DO UPDATE SET views = page_views.views + 1;

Чому не «SELECT, потім INSERT або UPDATE»: між перевіркою й записом інший запит може вставити той самий рядок - і один з двох отримає помилку унікальності чи перезапише чужі дані. ON CONFLICT розв'язує конфлікт атомарно всередині бази.

У Laravel Model::upsert($rows, uniqueBy: [...], update: [...]) генерує саме INSERT ... ON CONFLICT ... DO UPDATE. Події моделі при цьому не спрацьовують.

Нюанси:

• одним запитом не можна оновити той самий рядок двічі: якщо в пакеті вставки два рядки з однаковим ключем - помилка ON CONFLICT DO UPDATE command cannot affect row a second time. Дублікати треба прибрати до запиту;
• послідовність identity/serial витрачає значення навіть при конфлікті - у нумерації з'являються пропуски, і це нормально.

Докладніше в документації: INSERT: ON CONFLICT

RETURNING повертає дані рядків, які щойно вставлено, оновлено чи видалено, - тим самим запитом, без додаткового SELECT.

INSERT INTO orders (user_id, total) VALUES (7, 1999)
RETURNING id, created_at;

Типова потреба: дізнатися згенерований id, значення за замовчуванням (created_at, uuid), результат тригера.

З UPDATE і DELETE:

UPDATE accounts SET balance = balance - 500
WHERE id = 7 AND balance >= 500
RETURNING balance;
-- 0 рядків у відповіді - коштів не вистачило

DELETE FROM sessions WHERE last_activity < now() - interval '30 days'
RETURNING user_id;

PostgreSQL 18: старі й нові значення в одному запиті:

UPDATE products SET price = price * 1.1 WHERE category_id = 3
RETURNING id, old.price AS old_price, new.price AS new_price;

Раніше для «було - стало» потрібен був окремий запит до оновлення чи тригер.

З ON CONFLICT - дізнатися, чи рядок вставлено, чи оновлено:

INSERT INTO prices (sku, amount) VALUES ('A-1', 100)
ON CONFLICT (sku) DO UPDATE SET amount = EXCLUDED.amount
RETURNING id, (xmax = 0) AS inserted;

(xmax = 0 - поширений прийом, що спирається на внутрішню деталь MVCC, а не на гарантований інтерфейс.)

Чому це важливо:

• на один запит менше - менше затримки, особливо коли база на іншому сервері;
• атомарність: окремий SELECT після UPDATE може побачити вже змінений іншим запитом рядок, а RETURNING повертає саме те, що записав ваш запит;
• черга задач: UPDATE ... WHERE id = (SELECT ... FOR UPDATE SKIP LOCKED) RETURNING * - взяти задачу й позначити її зайнятою одним запитом.

У Laravel: при Model::create() на PostgreSQL Eloquent отримує id саме через INSERT ... RETURNING "id". Для решти сценаріїв - сирий запит:

$rows = DB::select('UPDATE accounts SET balance = balance - ? WHERE id = ? AND balance >= ? RETURNING balance', [500, 7, 500]);

Докладніше в документації: Повернення даних зі змінених рядків

DISTINCT ON (вирази) - розширення PostgreSQL: з кожної групи рядків з однаковими значеннями виразів лишається перший рядок за порядком ORDER BY.

Задача: останнє замовлення кожного користувача.

SELECT DISTINCT ON (user_id) user_id, id, total, created_at
FROM orders
ORDER BY user_id, created_at DESC;

• групи визначає user_id;
• усередині групи рядки впорядковані за created_at DESC;
• лишається перший - тобто найновіший.

Правило: вирази з DISTINCT ON мають бути на початку ORDER BY. Інакше:

ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions

Без додаткового сортування всередині групи «перший» рядок випадковий - завжди вказуйте, який саме потрібен.

Порівняння з альтернативами:

-- те саме через віконну функцію
SELECT * FROM (
  SELECT o.*, row_number() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM orders o
) t WHERE rn = 1;

Продуктивність: індекс (user_id, created_at DESC) дозволяє виконати DISTINCT ON без окремого сортування всієї таблиці. Без індексу PostgreSQL сортує всі рядки - на великій таблиці це повільно.

У Laravel є прямий метод:

Order::query()
    ->distinct('user_id')
    ->orderBy('user_id')
    ->latest()
    ->get();

distinct('user_id') на PostgreSQL генерує DISTINCT ON ("user_id"). Для зв'язку «останнє замовлення» в Eloquent є latestOfMany() - він будує підзапит, що працює в усіх базах.

Типова помилка - очікувати від DISTINCT ON агрегацію: він не рахує суми й кількості, лише вибирає один рядок з групи. Для підсумків потрібен GROUP BY.

Докладніше в документації: SELECT: DISTINCT ON