CTE + DELETE ... RETURNING: Move Rows in One Statement

В PostgreSQL команда DELETE может не только удалять строки, но и возвращать их через RETURNING.

А WITH может содержать не только обычные SELECT, но и изменяющие данные команды:

INSERT
UPDATE
DELETE

Если соединить эти две возможности, получится очень полезный паттерн:

WITH moved AS (
  DELETE FROM orders
  WHERE status = 'cancelled'
  RETURNING *
)
INSERT INTO orders_archive
SELECT *
FROM moved;

Этот запрос делает сразу две вещи:

1. удаляет строки из рабочей таблицы orders;
2. вставляет эти же удалённые строки в архивную таблицу orders_archive.

И всё это — одним SQL-оператором.

Такой подход используют, когда нужно не просто удалить данные, а безопасно перенести их в другую таблицу:

• архивировать старые заказы;
• переносить отменённые записи;
• перемещать обработанные события;
• убирать старые логи из рабочей таблицы;
• сохранять удаляемые строки для аудита;
• переносить данные между «активной» и «архивной» таблицей.

Главная идея:

DELETE ... RETURNING удаляет строки и тут же отдаёт их следующему шагу запроса.

Проблема двух отдельных запросов

Наивное архивирование часто пишут двумя командами.

Например, сначала копируем отменённые заказы в архив:

INSERT INTO orders_archive
SELECT *
FROM orders
WHERE status = 'cancelled';

Потом удаляем их из основной таблицы:

DELETE FROM orders
WHERE status = 'cancelled';

На первый взгляд всё нормально.

Но у такого подхода есть несколько проблем.

Проблема 1: между командами есть окно

Между первым и вторым запросом данные могут измениться.

Например:

1. первый запрос скопировал отменённые заказы в архив;
2. другой процесс изменил часть этих заказов;
3. второй запрос удалил уже другой набор строк.

Или наоборот:

1. первый запрос скопировал строки;
2. другой процесс удалил часть строк;
3. второй запрос удалил меньше строк, чем было скопировано.

В результате архив может перестать соответствовать реальному удалению.

Проблема 2: сбой между INSERT и DELETE

Допустим, первый запрос выполнился:

INSERT INTO orders_archive
SELECT *
FROM orders
WHERE status = 'cancelled';

Строки попали в архив.

А потом приложение упало до выполнения DELETE.

В итоге строки остались и в orders, и в orders_archive.

Если всё обёрнуто в транзакцию, это можно откатить. Но логика всё равно разбита на два отдельных действия.

DELETE ... RETURNING внутри CTE позволяет выразить перенос как одну цельную операцию.

Базовый паттерн: удалить и вставить в архив

Допустим, есть таблица orders:

id  | user_id | amount | status     | created_at
----+---------+--------+------------+---------------------
101 | 1       | 1000   | paid       | 2024-01-01 10:00:00
102 | 2       | 500    | cancelled  | 2024-01-02 11:00:00
103 | 3       | 700    | cancelled  | 2024-01-03 12:00:00

И есть архивная таблица orders_archive.

Хотим перенести все отменённые заказы в архив.

WITH moved AS (
  DELETE FROM orders
  WHERE status = 'cancelled'
  RETURNING *
)
INSERT INTO orders_archive
SELECT *
FROM moved;

Что происходит:

WITH moved AS (...)

создаёт CTE с именем moved.

Внутри него:

DELETE FROM orders
WHERE status = 'cancelled'
RETURNING *

удаляет строки из orders и возвращает удалённые строки.

Внешний запрос:

INSERT INTO orders_archive
SELECT *
FROM moved;

берёт строки из moved и вставляет их в архив.

То есть строки не нужно читать дважды: мы берём именно то, что удалили.

Почему это атомарно

Запрос:

WITH moved AS (
  DELETE FROM orders
  WHERE status = 'cancelled'
  RETURNING *
)
INSERT INTO orders_archive
SELECT *
FROM moved;

это один SQL-оператор.

Он выполняется целиком.

Если вставка в архив не получится, например из-за ограничения, ошибка откатит весь оператор.

То есть строки не будут просто удалены без архивации.

Это важное отличие от варианта:

INSERT INTO archive ...
DELETE FROM source ...

где логика физически разнесена на два действия.

Главная мысль:

DELETE ... RETURNING передаёт в архив именно те строки, которые удаляет текущая команда.

Лучше перечислять колонки явно

В учебных примерах часто пишут:

RETURNING *

и:

INSERT INTO orders_archive
SELECT *
FROM moved;

Но в реальном проекте так лучше не делать.

Почему?

Потому что SELECT * зависит от порядка и состава колонок.

Если в orders добавят новую колонку, а в orders_archive её не будет, запрос может сломаться.

Или хуже: если порядок колонок не совпадает, данные могут попасть не туда.

Лучше перечислять колонки явно.

Например:

WITH moved AS (
  DELETE FROM orders
  WHERE status = 'cancelled'
  RETURNING
    id,
    user_id,
    amount,
    status,
    created_at
)
INSERT INTO orders_archive (
  id,
  user_id,
  amount,
  status,
  created_at
)
SELECT
  id,
  user_id,
  amount,
  status,
  created_at
FROM moved;

Да, это длиннее.

Но зато безопаснее и понятнее.

Добавить archived_at при переносе

Часто архивная таблица отличается от основной.

Например, в архиве есть колонка:

archived_at

Тогда можно добавить её прямо в SELECT.

WITH moved AS (
  DELETE FROM orders
  WHERE created_at < now() - interval '1 year'
  RETURNING
    id,
    user_id,
    amount,
    status,
    created_at
)
INSERT INTO orders_archive (
  id,
  user_id,
  amount,
  status,
  created_at,
  archived_at
)
SELECT
  id,
  user_id,
  amount,
  status,
  created_at,
  now() AS archived_at
FROM moved;

Здесь DELETE возвращает данные заказа, а внешний INSERT добавляет техническое время архивации:

now() AS archived_at

Так в архиве будет видно, когда строку перенесли.

Перенос с RETURNING во внешнем INSERT

Можно добавить RETURNING и к внешнему INSERT.

Например, хотим перенести строки и сразу получить список id, которые реально попали в архив.

WITH moved AS (
  DELETE FROM orders
  WHERE status = 'cancelled'
  RETURNING
    id,
    user_id,
    amount,
    status,
    created_at
)
INSERT INTO orders_archive (
  id,
  user_id,
  amount,
  status,
  created_at,
  archived_at
)
SELECT
  id,
  user_id,
  amount,
  status,
  created_at,
  now()
FROM moved
RETURNING id, status, archived_at;

Результат может быть таким:

id  | status    | archived_at
----+-----------+-------------------------------
102 | cancelled | 2026-06-18 10:30:00.123456+00
103 | cancelled | 2026-06-18 10:30:00.123456+00

Так приложение сразу получает отчёт о том, что было заархивировано.

Перенос с итоговым отчётом

Можно построить цепочку из нескольких CTE.

Например:

1. удалить сотрудников без зарплаты;
2. вставить их в архив;
3. посчитать, сколько сотрудников перенесли по каждому отделу.

WITH removed AS (
  DELETE FROM employees
  WHERE salary IS NULL
  RETURNING
    id,
    name,
    manager_id,
    dept,
    salary
),
archived AS (
  INSERT INTO employees_archive (
    id,
    name,
    manager_id,
    dept,
    salary,
    archived_at
  )
  SELECT
    id,
    name,
    manager_id,
    dept,
    salary,
    now()
  FROM removed
  RETURNING dept
)
SELECT
  dept,
  count(*) AS moved_count
FROM archived
GROUP BY dept
ORDER BY dept;

Что здесь происходит:

removed  -- удалил строки и вернул их
archived -- вставил удалённые строки в архив и вернул dept
SELECT   -- посчитал, сколько строк ушло по отделам

Результат:

dept | moved_count
-----+------------
eng  | 3
qa   | 2
sales| 1

Это удобно для логов, админок и проверок.

Один оператор не только переносит строки, но и возвращает сводку по переносу.

Правило единого снимка данных

У изменяющих CTE в PostgreSQL есть важная особенность.

Все части одного оператора работают с одним снимком данных, созданным на старте оператора.

Это значит: если один CTE удалил строки, другой CTE не увидит это удаление через обычный SELECT из исходной таблицы.

Удалённые строки нужно передавать именно через RETURNING.

Например:

WITH moved AS (
  DELETE FROM orders
  WHERE status = 'cancelled'
  RETURNING id
),
still_seen AS (
  SELECT count(*) AS cnt
  FROM orders
  WHERE status = 'cancelled'
)
SELECT *
FROM still_seen;

Интуитивно можно ожидать, что still_seen увидит уже удалённые строки как отсутствующие.

Но логика не такая простая: CTE в одном операторе работают на одном снимке данных. Поэтому не стоит строить взаимодействие между ними через повторное чтение исходной таблицы.

Правильный способ передавать удалённые строки — через RETURNING.

WITH moved AS (
  DELETE FROM orders
  WHERE status = 'cancelled'
  RETURNING id
)
SELECT count(*) AS moved_count
FROM moved;

Главное правило:

Внутри одного изменяющего CTE передавайте изменённые строки через RETURNING, а не через повторный SELECT из исходной таблицы.

Порядок выполнения CTE не должен быть бизнес-логикой

Нельзя полагаться на то, что CTE выполнятся строго в том порядке, в котором вы их написали, если между ними нет зависимости по данным.

Например:

WITH a AS (
  DELETE FROM table_a
  WHERE deleted = true
  RETURNING id
),
b AS (
  DELETE FROM table_b
  WHERE deleted = true
  RETURNING id
)
SELECT 1;

Если b не зависит от a, не стоит строить бизнес-логику на порядке их выполнения.

Если порядок важен, сделайте зависимость явной: передавайте данные из одного CTE в другой.

Например:

WITH moved AS (
  DELETE FROM orders
  WHERE status = 'cancelled'
  RETURNING id, user_id, amount, status
),
archived AS (
  INSERT INTO orders_archive (id, user_id, amount, status)
  SELECT id, user_id, amount, status
  FROM moved
  RETURNING id
)
SELECT count(*) AS archived_count
FROM archived;

Здесь archived явно зависит от moved, потому что читает из него.

Не изменяйте одну и ту же строку дважды

Ещё одна важная ловушка: не стоит пытаться изменить одну и ту же строку несколько раз в одном операторе.

Например, два CTE обновляют одну таблицу и потенциально одни и те же строки.

WITH first_update AS (
  UPDATE orders
  SET status = 'step_1'
  WHERE id = 100
  RETURNING id
),
second_update AS (
  UPDATE orders
  SET status = 'step_2'
  WHERE id = 100
  RETURNING id
)
SELECT * FROM first_update;

Так делать не нужно.

Результат может быть не тем, что вы ожидаете.

В одном операторе лучше:

• один раз изменять конкретную строку;
• не строить цепочку повторных UPDATE/DELETE по тем же строкам;
• разделить операции на отдельные команды, если нужна последовательная обработка;
• использовать RETURNING для передачи результата, а не повторного изменения тех же строк.

Архивирование по другой таблице

Можно сочетать этот паттерн с DELETE ... USING.

Например, есть таблица пользователей users, и нужно перенести в архив заказы пользователей из США.

WITH moved AS (
  DELETE FROM orders o
  USING users u
  WHERE o.user_id = u.id
    AND u.country = 'US'
  RETURNING
    o.id,
    o.user_id,
    o.amount,
    o.status,
    o.created_at,
    u.country
)
INSERT INTO orders_archive (
  id,
  user_id,
  amount,
  status,
  created_at,
  country,
  archived_at
)
SELECT
  id,
  user_id,
  amount,
  status,
  created_at,
  country,
  now()
FROM moved;

Здесь:

• DELETE FROM orders o удаляет заказы;
• USING users u подключает пользователей для фильтра;
• RETURNING возвращает и поля заказа, и страну пользователя;
• внешний INSERT сохраняет всё в архив.

Это очень полезный паттерн для сложных архивных операций.

Архивирование старых событий

Допустим, есть рабочая таблица events и архив events_archive.

Хотим перенести события старше 90 дней.

WITH moved AS (
  DELETE FROM events
  WHERE created_at < now() - interval '90 days'
  RETURNING
    id,
    user_id,
    event_type,
    payload,
    created_at
)
INSERT INTO events_archive (
  id,
  user_id,
  event_type,
  payload,
  created_at,
  archived_at
)
SELECT
  id,
  user_id,
  event_type,
  payload,
  created_at,
  now()
FROM moved;

Так можно периодически чистить рабочую таблицу, не теряя историю.

Архивирование отменённых заказов с отчётом по статусам

Можно вернуть статистику по перенесённым строкам.

WITH moved AS (
  DELETE FROM orders
  WHERE status IN ('cancelled', 'refunded')
  RETURNING
    id,
    user_id,
    amount,
    status,
    created_at
),
archived AS (
  INSERT INTO orders_archive (
    id,
    user_id,
    amount,
    status,
    created_at,
    archived_at
  )
  SELECT
    id,
    user_id,
    amount,
    status,
    created_at,
    now()
  FROM moved
  RETURNING status
)
SELECT
  status,
  count(*) AS archived_count
FROM archived
GROUP BY status
ORDER BY status;

Результат:

status    | archived_count
----------+---------------
cancelled | 120
refunded  | 35

Такой запрос удобно запускать из фоновой задачи и логировать итог.

Если архивная вставка может конфликтовать

Иногда в архиве уже есть часть строк.

Например, orders_archive.id уникален, и повторный запуск может столкнуться с конфликтом.

Можно добавить ON CONFLICT.

WITH moved AS (
  DELETE FROM orders
  WHERE status = 'cancelled'
  RETURNING
    id,
    user_id,
    amount,
    status,
    created_at
)
INSERT INTO orders_archive (
  id,
  user_id,
  amount,
  status,
  created_at,
  archived_at
)
SELECT
  id,
  user_id,
  amount,
  status,
  created_at,
  now()
FROM moved
ON CONFLICT (id) DO NOTHING;

Но здесь нужно быть очень внимательным.

Если строка была удалена из orders, а вставка в архив по конфликту сделала DO NOTHING, значит удалённая строка не была записана заново в архив текущей командой.

Возможно, она уже там есть — тогда всё нормально.

Но если конфликт означает проблему данных, DO NOTHING может её скрыть.

Для критичных архивов лучше не гасить конфликты молча, а разбираться с причиной.

RETURNING * в продакшене лучше избегать

Плохой паттерн:

WITH moved AS (
  DELETE FROM orders
  WHERE status = 'cancelled'
  RETURNING *
)
INSERT INTO orders_archive
SELECT *
FROM moved;

В учебном примере это нормально.

В реальном проекте лучше так:

WITH moved AS (
  DELETE FROM orders
  WHERE status = 'cancelled'
  RETURNING
    id,
    user_id,
    amount,
    status,
    created_at
)
INSERT INTO orders_archive (
  id,
  user_id,
  amount,
  status,
  created_at,
  archived_at
)
SELECT
  id,
  user_id,
  amount,
  status,
  created_at,
  now()
FROM moved;

Почему лучше явно:

• меньше риск сломаться при изменении схемы;
• понятнее, какие данные переносим;
• можно добавить archived_at;
• можно изменить порядок колонок;
• легче ревьюить запрос;
• безопаснее для продакшена.

Индексы для переноса

DELETE всё равно должен найти строки, которые нужно перенести.

Например:

DELETE FROM orders
WHERE status = 'cancelled'

может выиграть от индекса:

CREATE INDEX orders_status_idx
ON orders (status);

Если условие такое:

WHERE created_at < now() - interval '1 year'

полезен индекс:

CREATE INDEX orders_created_at_idx
ON orders (created_at);

Если условие комбинированное:

WHERE status = 'cancelled'
  AND created_at < now() - interval '1 year'

может подойти составной индекс:

CREATE INDEX orders_status_created_idx
ON orders (status, created_at);

Но индексы нужно подбирать под реальные запросы и объёмы.

Перед большим переносом полезно проверить план:

EXPLAIN
WITH moved AS (
  DELETE FROM orders
  WHERE status = 'cancelled'
  RETURNING
    id,
    user_id,
    amount,
    status,
    created_at
)
INSERT INTO orders_archive (
  id,
  user_id,
  amount,
  status,
  created_at
)
SELECT
  id,
  user_id,
  amount,
  status,
  created_at
FROM moved;

Большие переносы лучше делать батчами

Если нужно перенести миллионы строк, один огромный DELETE может быть тяжёлым.

Он может:

• долго держать блокировки;
• создать много dead tuples;
• раздуть таблицу и индексы;
• нагрузить autovacuum;
• долго откатываться при ошибке;
• перегрузить архивную вставку.

Часто лучше переносить данные батчами.

Например, по 10 000 строк за запуск:

WITH batch AS (
  SELECT id
  FROM orders
  WHERE status = 'cancelled'
  ORDER BY id
  LIMIT 10000
),
moved AS (
  DELETE FROM orders o
  USING batch b
  WHERE o.id = b.id
  RETURNING
    o.id,
    o.user_id,
    o.amount,
    o.status,
    o.created_at
)
INSERT INTO orders_archive (
  id,
  user_id,
  amount,
  status,
  created_at,
  archived_at
)
SELECT
  id,
  user_id,
  amount,
  status,
  created_at,
  now()
FROM moved;

Такой запрос можно запускать повторно, пока строки не закончатся.

Для фоновых задач это обычно безопаснее, чем огромная транзакция на всю таблицу.

Проверка количества перенесённых строк

Если нужно вернуть количество перенесённых строк, можно добавить CTE с RETURNING.

WITH moved AS (
  DELETE FROM orders
  WHERE status = 'cancelled'
  RETURNING
    id,
    user_id,
    amount,
    status,
    created_at
),
archived AS (
  INSERT INTO orders_archive (
    id,
    user_id,
    amount,
    status,
    created_at,
    archived_at
  )
  SELECT
    id,
    user_id,
    amount,
    status,
    created_at,
    now()
  FROM moved
  RETURNING id
)
SELECT count(*) AS archived_count
FROM archived;

Результат:

archived_count
--------------
235

Так приложение получает понятный ответ:

сколько строк реально попало в архив

Триггеры и внешние ключи

DELETE внутри CTE — это обычный DELETE.

Значит, для него работают:

• внешние ключи;
• проверки ограничений;
• триггеры;
• правила доступа;
• каскадные действия, если они настроены.

Если на исходной таблице есть AFTER DELETE-триггеры, они будут срабатывать.

Если есть внешние ключи, которые запрещают удаление, запрос может упасть.

Например, если order_items ссылается на orders, то перенос заказов может быть невозможен, пока не решён вопрос с дочерними строками.

Варианты:

• сначала перенести дочерние строки;
• использовать ON DELETE CASCADE, если это соответствует модели;
• делать soft delete вместо физического удаления;
• архивировать целое дерево связанных данных;
• пересмотреть стратегию хранения.

Важно:

DELETE ... RETURNING не обходит ограничения базы. Он просто позволяет использовать удалённые строки дальше в том же операторе.

Soft delete как альтернатива

Иногда физически удалять строки не нужно.

Вместо переноса в архив можно проставить archived_at или deleted_at.

Например:

UPDATE orders
SET archived_at = now()
WHERE status = 'cancelled'
RETURNING id, archived_at;

Это не то же самое, что архивная таблица.

Soft delete подходит, если:

• нужно легко восстановить строку;
• важно не ломать внешние ключи;
• таблица не слишком разрастается;
• отчёты умеют фильтровать архивные строки;
• бизнесу нужна история в той же таблице.

Архивная таблица лучше, если:

• рабочая таблица должна оставаться маленькой;
• старые данные редко читаются;
• нужны другие индексы;
• нужна отдельная политика хранения;
• нужно физически разделить горячие и холодные данные.

MySQL

В MySQL нет такого же удобного паттерна:

WITH moved AS (
  DELETE ...
  RETURNING ...
)
INSERT ...

MySQL обычно не поддерживает изменяющие CTE в таком виде и не имеет PostgreSQL-семантики DELETE ... RETURNING.

Поэтому перенос чаще делают через транзакцию:

START TRANSACTION;

INSERT INTO orders_archive (id, user_id, amount, status, created_at)
SELECT id, user_id, amount, status, created_at
FROM orders
WHERE status = 'cancelled';

DELETE FROM orders
WHERE status = 'cancelled';

COMMIT;

Если нужна защита от конкурентных изменений, используют блокировки, например SELECT ... FOR UPDATE, или проектируют процесс так, чтобы перенос был безопасен.

Но это уже не один оператор, как в PostgreSQL.

ClickHouse

В ClickHouse подход другой.

ClickHouse — аналитическая колоночная СУБД, где удаления обычно являются тяжёлыми мутациями.

Паттерн PostgreSQL:

DELETE ... RETURNING

обычно напрямую не переносится.

В ClickHouse чаще используют:

• INSERT INTO archive SELECT ...;
• отдельную мутацию удаления;
• TTL;
• партиционирование;
• перенос или удаление целых партиций;
• пересборку таблицы;
• хранение версий вместо физического удаления.

Например, для старых данных часто лучше работать на уровне партиций, а не удалять строки по одной.

При переносе логики из PostgreSQL в ClickHouse нужно менять не только синтаксис, но и стратегию хранения данных.

Практические шаблоны

Перенести отменённые заказы в архив

WITH moved AS (
  DELETE FROM orders
  WHERE status = 'cancelled'
  RETURNING
    id,
    user_id,
    amount,
    status,
    created_at
)
INSERT INTO orders_archive (
  id,
  user_id,
  amount,
  status,
  created_at,
  archived_at
)
SELECT
  id,
  user_id,
  amount,
  status,
  created_at,
  now()
FROM moved;

Перенести старые события

WITH moved AS (
  DELETE FROM events
  WHERE created_at < now() - interval '90 days'
  RETURNING
    id,
    user_id,
    event_type,
    payload,
    created_at
)
INSERT INTO events_archive (
  id,
  user_id,
  event_type,
  payload,
  created_at,
  archived_at
)
SELECT
  id,
  user_id,
  event_type,
  payload,
  created_at,
  now()
FROM moved;

Перенести и вернуть количество строк

WITH moved AS (
  DELETE FROM orders
  WHERE status = 'cancelled'
  RETURNING
    id,
    user_id,
    amount,
    status,
    created_at
),
archived AS (
  INSERT INTO orders_archive (
    id,
    user_id,
    amount,
    status,
    created_at,
    archived_at
  )
  SELECT
    id,
    user_id,
    amount,
    status,
    created_at,
    now()
  FROM moved
  RETURNING id
)
SELECT count(*) AS archived_count
FROM archived;

Перенести и посчитать по статусам

WITH moved AS (
  DELETE FROM orders
  WHERE status IN ('cancelled', 'refunded')
  RETURNING
    id,
    user_id,
    amount,
    status,
    created_at
),
archived AS (
  INSERT INTO orders_archive (
    id,
    user_id,
    amount,
    status,
    created_at,
    archived_at
  )
  SELECT
    id,
    user_id,
    amount,
    status,
    created_at,
    now()
  FROM moved
  RETURNING status
)
SELECT
  status,
  count(*) AS archived_count
FROM archived
GROUP BY status
ORDER BY status;

Перенести батчом

WITH batch AS (
  SELECT id
  FROM orders
  WHERE status = 'cancelled'
  ORDER BY id
  LIMIT 10000
),
moved AS (
  DELETE FROM orders o
  USING batch b
  WHERE o.id = b.id
  RETURNING
    o.id,
    o.user_id,
    o.amount,
    o.status,
    o.created_at
)
INSERT INTO orders_archive (
  id,
  user_id,
  amount,
  status,
  created_at,
  archived_at
)
SELECT
  id,
  user_id,
  amount,
  status,
  created_at,
  now()
FROM moved;

Перенести по связанной таблице

WITH moved AS (
  DELETE FROM orders o
  USING users u
  WHERE o.user_id = u.id
    AND u.country = 'US'
  RETURNING
    o.id,
    o.user_id,
    o.amount,
    o.status,
    o.created_at,
    u.country
)
INSERT INTO orders_archive (
  id,
  user_id,
  amount,
  status,
  created_at,
  country,
  archived_at
)
SELECT
  id,
  user_id,
  amount,
  status,
  created_at,
  country,
  now()
FROM moved;

Архивировать удалённые строки и вернуть id

WITH moved AS (
  DELETE FROM orders
  WHERE status = 'cancelled'
  RETURNING
    id,
    user_id,
    amount,
    status,
    created_at
)
INSERT INTO orders_archive (
  id,
  user_id,
  amount,
  status,
  created_at,
  archived_at
)
SELECT
  id,
  user_id,
  amount,
  status,
  created_at,
  now()
FROM moved
RETURNING id;

Частые ошибки

Используют RETURNING *

Учебный пример:

WITH moved AS (
  DELETE FROM orders
  WHERE status = 'cancelled'
  RETURNING *
)
INSERT INTO orders_archive
SELECT *
FROM moved;

В продакшене лучше явно перечислять колонки.

Так безопаснее при изменении схемы.

Думают, что другой CTE увидит результат DELETE через SELECT

Надёжный способ передать удалённые строки — RETURNING.

Не стройте логику на повторном чтении исходной таблицы внутри того же оператора.

Меняют одну и ту же строку несколько раз

Не стоит в одном операторе делать несколько изменяющих CTE, которые могут затронуть одну и ту же строку.

Результат может быть неочевидным.

Делают огромный перенос одной транзакцией

Для миллионов строк лучше использовать батчи, партиции или отдельную стратегию архивации.

Один большой DELETE может создать много нагрузки и долго откатываться.

Игнорируют ограничения и триггеры

DELETE внутри CTE — это обычный DELETE.

Он не обходит foreign keys, triggers и constraints.

Если удаление запрещено ограничением, весь оператор упадёт.

Что важно запомнить

DELETE ... RETURNING внутри WITH позволяет удалить строки и сразу передать их следующему шагу.

Пример:

WITH moved AS (
  DELETE FROM orders
  WHERE status = 'cancelled'
  RETURNING
    id,
    user_id,
    amount,
    status,
    created_at
)
INSERT INTO orders_archive (
  id,
  user_id,
  amount,
  status,
  created_at,
  archived_at
)
SELECT
  id,
  user_id,
  amount,
  status,
  created_at,
  now()
FROM moved;

Главные правила:

• DELETE ... RETURNING возвращает удалённые строки;
• CTE может содержать изменяющую команду;
• внешний INSERT может использовать результат RETURNING;
• перенос выполняется одним SQL-оператором;
• нет промежутка между удалением и вставкой в архив;
• лучше явно перечислять колонки, а не писать RETURNING *;
• другие части оператора не должны читать удалённые строки из исходной таблицы — передавайте их через RETURNING;
• не изменяйте одну и ту же строку несколько раз в одном операторе;
• для больших переносов используйте батчи;
• DELETE всё равно подчиняется foreign keys, triggers и constraints;
• в MySQL и ClickHouse такого же паттерна обычно нет.

Короткий вывод

CTE + DELETE ... RETURNING — это идиоматичный PostgreSQL-паттерн для переноса строк.

Он позволяет выразить действие:

удали строки из рабочей таблицы
и сразу вставь именно эти строки в архив

одним оператором:

WITH moved AS (
  DELETE FROM orders
  WHERE status = 'cancelled'
  RETURNING
    id,
    user_id,
    amount,
    status,
    created_at
)
INSERT INTO orders_archive (
  id,
  user_id,
  amount,
  status,
  created_at,
  archived_at
)
SELECT
  id,
  user_id,
  amount,
  status,
  created_at,
  now()
FROM moved;

Главная мысль:

DELETE ... RETURNING делает удалённые строки доступными как таблицу, а WITH позволяет тут же использовать эту таблицу для следующего действия.

Поэтому этот паттерн отлично подходит для архивирования, аудита, переноса старых данных и фоновых чисток, где важно не потерять строки между удалением и вставкой.