Питання на співбесіді: Можливості 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

Звичайний підзапит у FROM не бачить колонок інших таблиць того самого FROM - він обчислюється один раз незалежно. LATERAL дозволяє підзапиту посилатися на попередні таблиці - він виконується для кожного їхнього рядка, як цикл.

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

SELECT u.id, u.email, o.id AS order_id, o.total, o.created_at
FROM users u
CROSS JOIN LATERAL (
    SELECT id, total, created_at
    FROM orders
    WHERE orders.user_id = u.id
    ORDER BY created_at DESC
    LIMIT 3
) o;

LIMIT 3 усередині застосовується окремо для кожного користувача - звичайним JOIN чи GROUP BY такого не виразити.

LEFT JOIN LATERAL ... ON true - зберегти користувачів без замовлень:

SELECT u.id, last_order.total
FROM users u
LEFT JOIN LATERAL (
    SELECT total FROM orders WHERE user_id = u.id ORDER BY created_at DESC LIMIT 1
) last_order ON true;

Коли LATERAL доречний:

• топ-N у групі, особливо з індексом (user_id, created_at DESC): для кожного користувача - коротке сканування індексу, а не сортування всієї таблиці;
• функції, що повертають набори рядків з аргументами з іншої таблиці:

SELECT p.id, tag
FROM posts p, LATERAL unnest(p.tags) AS tag;

• кілька обчислених значень без повторення виразів у SELECT;
• розгортання JSON (jsonb_array_elements, jsonb_to_recordset) для кожного рядка.

Порівняння для «топ-N у групі»:

Ризик: LATERAL - це фактично вкладений цикл. На мільйоні зовнішніх рядків без індексу для внутрішнього запиту він виконає мільйон повних сканувань. Перевіряйте план через EXPLAIN ANALYZE.

У Laravel: joinLateral() і leftJoinLateral() у конструкторі запитів:

$latest = DB::table('orders')
    ->whereColumn('user_id', 'users.id')
    ->orderByDesc('created_at')
    ->limit(3);

User::query()->joinLateral($latest, 'recent_orders')->get();

Докладніше в документації: Табличні вирази: LATERAL

FILTER - умова для окремого агрегату. Кілька різних підрахунків за один прохід таблиці:

SELECT
    count(*)                                      AS total,
    count(*) FILTER (WHERE status = 'paid')       AS paid,
    count(*) FILTER (WHERE status = 'refunded')   AS refunded,
    sum(total) FILTER (WHERE status = 'paid')     AS revenue
FROM orders
WHERE created_at >= date_trunc('month', now());

Те саме в стандартному SQL пишуть через sum(CASE WHEN ... THEN 1 ELSE 0 END) - FILTER коротший і читабельніший. Він працює з будь-якими агрегатами: sum, avg, array_agg, jsonb_agg.

generate_series - згенерувати послідовність чисел чи дат:

SELECT generate_series(1, 5);
SELECT generate_series('2026-10-01'::date, '2026-10-07', interval '1 day');

Звіт по днях без пропусків. Якщо згрупувати замовлення за днем, дні без замовлень просто зникнуть з результату - а на графіку потрібен нуль:

SELECT d::date AS day,
       count(o.id) AS orders,
       coalesce(sum(o.total), 0) AS revenue
FROM generate_series(date_trunc('day', now()) - interval '29 days',
                     date_trunc('day', now()),
                     interval '1 day') AS d
LEFT JOIN orders o
       ON o.created_at >= d AND o.created_at < d + interval '1 day'
GROUP BY d
ORDER BY d;

• серія дає всі дні періоду;
• LEFT JOIN зберігає дні без замовлень;
• count(o.id), а не count(*) - інакше порожній день порахується як 1.

Зведена таблиця (pivot) через FILTER:

SELECT date_trunc('week', created_at) AS week,
       count(*) FILTER (WHERE channel = 'web')    AS web,
       count(*) FILTER (WHERE channel = 'mobile') AS mobile
FROM orders
GROUP BY 1
ORDER BY 1;

Часові пояси: date_trunc('day', created_at) обрізає за поясом сесії бази. Для звіту за київським часом - date_trunc('day', created_at, 'Europe/Kyiv') (для timestamptz), інакше «день» починатиметься о 02:00 чи 03:00 за Києвом.

Продуктивність: умова за діапазоном created_at >= ... AND created_at < ... використовує індекс, а WHERE date(created_at) = ... - ні (функція над колонкою).

У Laravel такі запити зручно писати через selectRaw і DB::select, а для дашбордів - кешувати: звіт за місяць не має перераховуватися на кожне відкриття сторінки.

Докладніше в документації: Агрегатні функції

MERGE (PostgreSQL 15+, стандартний SQL) порівнює цільову таблицю з джерелом і для кожного рядка виконує дію залежно від того, знайдено збіг чи ні:

MERGE INTO products AS p
USING staging_products AS s
ON p.sku = s.sku
WHEN MATCHED AND s.discontinued THEN
    DELETE
WHEN MATCHED THEN
    UPDATE SET price = s.price, updated_at = now()
WHEN NOT MATCHED THEN
    INSERT (sku, name, price) VALUES (s.sku, s.name, s.price);

PostgreSQL 17+: WHEN NOT MATCHED BY SOURCE - рядки цільової таблиці, яких немає в джерелі (наприклад, видалити товари, що зникли з фіда), і RETURNING з функцією merge_action(), що показує, яку дію виконано для рядка.

Порівняння:

Головна відмінність - поведінка при конкуренції. ON CONFLICT гарантовано обробляє ситуацію, коли інший запит одночасно вставляє той самий ключ: він чекає й оновлює. MERGE спершу визначає, чи є збіг, а потім виконує дію - якщо паралельний запит встиг вставити рядок, MERGE спробує вставити його вдруге й отримає помилку унікальності.

Коли що обирати:

• ON CONFLICT - upsert з високою конкуренцією: лічильники, кеш-таблиці, синхронізація окремих записів з багатьох процесів;
• MERGE - пакетна синхронізація з таблицею-джерелом: імпорт фіда, ETL з проміжної таблиці, де потрібні і вставки, і оновлення, і видалення, а паралельних записувачів немає (чи їх виключено блокуванням).

Типовий сценарій імпорту:

1. COPY файлу в тимчасову таблицю;
2. MERGE з неї в робочу таблицю;
3. одна транзакція - або все застосовано, або нічого.

У Laravel конструктор запитів не має методу для MERGE - використовують DB::statement() з параметрами.

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

У PostgreSQL у WITH можна використовувати не лише SELECT, а й INSERT, UPDATE, DELETE з RETURNING. Результат такого CTE доступний основному запиту.

Архівування: перенести старі замовлення в архів одним запитом:

WITH moved AS (
    DELETE FROM orders
    WHERE created_at < now() - interval '2 years'
    RETURNING *
)
INSERT INTO orders_archive
SELECT * FROM moved;

Видалення й вставка - один оператор, одна атомарна операція: рядок не може зникнути з orders, не з'явившись в архіві.

Створити пов'язані записи з отриманим id:

WITH new_user AS (
    INSERT INTO users (email) VALUES ('olena@example.com')
    RETURNING id
)
INSERT INTO profiles (user_id, locale)
SELECT id, 'uk' FROM new_user;

Журнал змін разом з оновленням:

WITH changed AS (
    UPDATE products SET price = price * 1.1
    WHERE category_id = 3
    RETURNING id, price
)
INSERT INTO price_history (product_id, price, changed_at)
SELECT id, price, now() FROM changed;

Важливі правила виконання:

• усі частини бачать один знімок даних - знімок на момент початку оператора. Основний запит не бачить змін, зроблених у CTE, через звичайне читання таблиці - лише через RETURNING;
• не можна змінювати той самий рядок двічі в одному операторі (в CTE й в основному запиті) - результат непередбачуваний;
• CTE зі зміною даних виконується завжди повністю, навіть якщо основний запит не читає всіх його рядків;
• порядок виконання частин не гарантований - не можна покладатися на те, що один CTE спрацює раніше за інший, якщо вони не пов'язані через RETURNING.

Переваги перед кількома запитами в транзакції:

• один мережевий обмін замість кількох;
• немає проміжного стану в застосунку - не треба передавати тисячі id з бази в PHP і назад.

Обмеження й ризики:

• великі обсяги: перенесення мільйонів рядків одним оператором - довга транзакція, роздування таблиць і блокування. Для архівування великих таблиць - порціями (... WHERE id IN (SELECT id ... LIMIT 10000)) у циклі;
• читабельність: складний ланцюжок CTE важко рев'ювати - коментарі й тести на ці запити обов'язкові;
• переносимість: MySQL такого не підтримує.

У Laravel - сирий запит через DB::statement() чи DB::select() (якщо потрібен результат RETURNING).

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

WITH RECURSIVE складається з двох частин, об'єднаних UNION ALL:

• початкова - стартові рядки;
• рекурсивна - посилається на сам CTE і додає наступний рівень, доки не поверне порожній результат.

Усі підкатегорії категорії з глибиною:

WITH RECURSIVE tree AS (
    SELECT id, parent_id, name, 1 AS depth
    FROM categories
    WHERE id = 10

    UNION ALL

    SELECT c.id, c.parent_id, c.name, t.depth + 1
    FROM categories c
    JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree;

Шлях від вузла до кореня (хлібні крихти) - те саме в зворотному напрямку: JOIN tree t ON c.id = t.parent_id.

Проблема циклів. У дереві циклів немає, але в графі (рекомендації, залежності, «хто кого запросив») чи в зіпсованих даних (категорія стала батьком свого предка) рекурсія ніколи не завершиться - запит працюватиме до вичерпання пам'яті чи тайм-ауту.

PostgreSQL 14+: CYCLE - вбудоване виявлення циклів:

WITH RECURSIVE graph AS (
    SELECT id, parent_id FROM categories WHERE id = 10
    UNION ALL
    SELECT c.id, c.parent_id FROM categories c JOIN graph g ON c.parent_id = g.id
) CYCLE id SET is_cycle USING path
SELECT * FROM graph WHERE NOT is_cycle;

PostgreSQL сам відстежує шлях і зупиняє гілку, що повертається до вже відвіданого вузла.

SEARCH задає порядок обходу:

) SEARCH DEPTH FIRST BY id SET ordercol
SELECT * FROM tree ORDER BY ordercol;

DEPTH FIRST дає порядок, у якому зручно виводити дерево з відступами; BREADTH FIRST - рівень за рівнем.

До PG 14 - вручну: накопичувати масив відвіданих id і перевіряти NOT c.id = ANY(path).

Додаткові запобіжники:

• обмеження глибини (WHERE t.depth < 20) - навіть з CYCLE захищає від неочікувано глибоких структур;
• statement_timeout для таких запитів;
• UNION замість UNION ALL прибирає дублікати рядків і теж обриває прості цикли, але дорожчий і не завжди достатній.

Продуктивність: індекс на parent_id обов'язковий - кожен рівень рекурсії шукає дітей за ним.

Альтернативи рекурсії для дерев, які часто читають і рідко змінюють: матеріалізований шлях (ltree), closure table, nested sets - вони дають піддерево одним простим запитом.

У Laravel рекурсивні CTE пишуть сирим SQL або через пакет staudenmeir/laravel-adjacency-list, що додає зв'язки descendants() і ancestors() на основі WITH RECURSIVE.

Докладніше в документації: WITH: виявлення циклів