CTE DELETE with RETURNING

CTE DELETE with RETURNING is a SQL statement in the CTE (WITH) category. PostgreSQL: delete rows and return the deleted data in a single operation using a CTE. The syntax is WITH deleted AS (DELETE FROM table WHERE condition RETURNING *) SELECT * FROM deleted;. It returns deleted rows from CTE. A typical example: WITH archived AS ( DELETE FROM orders WHERE created_at < DATE_SUB(NOW(), INTERVAL 1 YEAR) RETURNING * ) INSERT INTO orders_archive SELECT * FROM archived; -- Delete old orders and immediately archive them -- Atomic operation: no data loss if the INSERT fails A close relative is WITH (CTE), which common Table Expression — define a named temporary result set that can be referenced in the main query. A close relative is Recursive CTE, which a CTE that references itself, allowing hierarchical or recursive queries (org charts, tree structures). A close relative is Multiple CTEs, which define multiple CTEs in one WITH clause, comma-separated. Each can reference previous CTEs.