CTE с DML в SQL: INSERT, UPDATE, DELETE внутри WITH

CTE (Common Table Expressions) не только для SELECT. В PostgreSQL WITH работает с INSERT, UPDATE и DELETE — это открывает мощные паттерны для атомарных операций.

Базовый синтаксис: DML в CTE

WITH cte_name AS (
    INSERT INTO ... RETURNING *
    -- или UPDATE ... RETURNING *
    -- или DELETE FROM ... RETURNING *
)
SELECT * FROM cte_name;

Ключевой момент: все DML-операции в одном запросе WITH выполняются как единая транзакция и видят одно снимочное (snapshot) состояние базы.

Паттерн 1: DELETE + INSERT (перемещение строк)

-- Переместить выполненные задачи в архив
WITH deleted AS (
    DELETE FROM tasks
    WHERE status = 'completed' AND completed_at < NOW() - INTERVAL '30 days'
    RETURNING *
)
INSERT INTO tasks_archive SELECT * FROM deleted;

Атомарно: задачи либо удалены и вставлены в архив, либо ничего.

Паттерн 2: INSERT с возвратом и последующей вставкой

-- Создать пользователя и его начальные настройки в одном запросе
WITH new_user AS (
    INSERT INTO users (email, name, created_at)
    VALUES ('alice@example.com', 'Алиса', NOW())
    RETURNING id, email
),
new_profile AS (
    INSERT INTO profiles (user_id, bio, avatar_url)
    SELECT id, '', NULL FROM new_user
    RETURNING user_id
)
INSERT INTO user_settings (user_id, theme, notifications)
SELECT id, 'light', true FROM new_user
RETURNING *;

Паттерн 3: Upsert с аудитом

-- Вставить или обновить + записать в лог
WITH upserted AS (
    INSERT INTO products (sku, name, price, updated_at)
    VALUES ('SKU-001', 'Ноутбук Pro', 95000, NOW())
    ON CONFLICT (sku) DO UPDATE SET
        name = EXCLUDED.name,
        price = EXCLUDED.price,
        updated_at = NOW()
    RETURNING sku, name, price,
              (xmax = 0) AS is_insert  -- xmax=0 → INSERT, иначе UPDATE
)
INSERT INTO price_change_log (sku, new_price, operation, logged_at)
SELECT sku, price,
       CASE WHEN is_insert THEN 'INSERT' ELSE 'UPDATE' END,
       NOW()
FROM upserted;

Паттерн 4: UPDATE нескольких таблиц

-- Обновить заказ и уменьшить остаток
WITH updated_order AS (
    UPDATE orders
    SET status = 'shipped', shipped_at = NOW()
    WHERE id = 555
    RETURNING id, product_id, quantity
),
stock_update AS (
    UPDATE inventory
    SET reserved = reserved - u.quantity,
        shipped = shipped + u.quantity
    FROM updated_order u
    WHERE inventory.product_id = u.product_id
    RETURNING inventory.product_id, inventory.reserved
)
SELECT o.id AS order_id, s.product_id, s.reserved AS remaining_reserved
FROM updated_order o
JOIN stock_update s ON s.product_id = o.product_id;

Паттерн 5: Безопасное перемещение с проверкой

-- Переместить только если условие выполнено (concurrency-safe)
WITH candidate AS (
    SELECT id FROM jobs
    WHERE status = 'pending'
    ORDER BY priority DESC, created_at
    LIMIT 1
    FOR UPDATE SKIP LOCKED  -- взять свободную задачу
),
taken AS (
    UPDATE jobs
    SET status = 'processing',
        started_at = NOW(),
        worker_id = :worker_id
    FROM candidate
    WHERE jobs.id = candidate.id
    RETURNING jobs.*
)
SELECT * FROM taken;
-- Возвращает задачу воркеру, или пустой результат если нет свободных

Паттерн 6: Bulk update с разными значениями

-- Обновить много строк с разными значениями за один запрос
WITH updates(id, new_price) AS (
    VALUES
        (1, 85000),
        (2, 1600),
        (3, 13000),
        (4, 26000)
)
UPDATE products p
SET price = u.new_price, updated_at = NOW()
FROM updates u
WHERE p.id = u.id
RETURNING p.id, p.name, p.price;

Паттерн 7: Собрать статистику по операции

WITH
inserted AS (
    INSERT INTO products (sku, name, price)
    SELECT sku, name, price FROM staging_products
    WHERE sku NOT IN (SELECT sku FROM products)
    RETURNING 1
),
updated AS (
    UPDATE products p
    SET price = s.price, name = s.name, updated_at = NOW()
    FROM staging_products s
    WHERE p.sku = s.sku AND (p.price <> s.price OR p.name <> s.name)
    RETURNING 1
)
SELECT
    (SELECT COUNT(*) FROM inserted) AS rows_inserted,
    (SELECT COUNT(*) FROM updated) AS rows_updated;

Ограничения DML в CTE

-- Нельзя обновить одну таблицу дважды в одном WITH
WITH first AS (UPDATE t SET x = 1 WHERE id = 1 RETURNING id),
     second AS (UPDATE t SET y = 2 WHERE id = 1 RETURNING id)  -- та же таблица!
SELECT * FROM first, second;
-- Результат непредсказуем — PostgreSQL предупреждает

-- Нельзя использовать данные из RETURNING в WHERE того же запроса
WITH del AS (DELETE FROM t WHERE id = 1 RETURNING id)
DELETE FROM t WHERE id IN (SELECT id FROM del);  -- ошибка

Производительность

DML в CTE всегда материализуется в PostgreSQL — данные вычисляются полностью перед использованием. Это гарантирует корректность, но может быть медленнее чем несколько отдельных запросов для очень больших объёмов.

Для больших батчей лучше разбить на части:

-- Для очень больших операций: батчи по 10000
WITH batch AS (
    DELETE FROM old_events WHERE id IN (
        SELECT id FROM old_events ORDER BY id LIMIT 10000
    )
    RETURNING 1
)
SELECT COUNT(*) AS deleted FROM batch;
-- Повторять до пустого результата

Итог: паттерны Writable CTE

Writable CTE — мощный инструмент для сложных атомарных операций. Альтернатива хранимым процедурам для большинства ETL-задач.