Увійти Реєстрація
Вакансії
Вакансії Laravel Компанії Статистика
Підготовка
Як підготуватися Співбесіди Тестування Документація
Блог
Всі статті Серії
Екосистема
Пакети Ресурси Проєкти Інструменти Події Відео
Інше
Про нас Реклама

Що таке CTE, що змінюють дані, і як перенести рядки між таблицями одним запитом?

У 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: оператори зміни даних

Схожі питання