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

Question

Accepted Answer

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

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

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