What is a data-modifying CTE and how can DELETE ... RETURNING feed an INSERT?
PostgreSQL lets INSERT, UPDATE and DELETE appear inside WITH, and RETURNING hands the affected rows to the next step. This moves rows between tables in one atomic statement, for example archiving deleted rows.
Explain it simply
Think of taking old files out of a cabinet and putting exactly those files into an archive box in one motion, so nothing is lost if you are interrupted. With a data-modifying CTE, the delete step passes the rows it removed to the insert step, and the whole thing succeeds or fails together.
RETURNING is the handoff: it makes a write statement produce rows like a SELECT.
Example
WITH moved AS (
DELETE FROM orders
WHERE created_at < now() - interval '2 years'
RETURNING *
)
INSERT INTO orders_archive
SELECT * FROM moved;Another common use is creating a parent and child in one go:
WITH new_user AS (
INSERT INTO users (name) VALUES ('Asha') RETURNING id
)
INSERT INTO profiles (user_id, bio)
SELECT id, 'Fresher' FROM new_user;All sub-statements see the same snapshot of the data, so they cannot see each other's changes to the tables, only the rows passed through RETURNING. A data-modifying CTE is always executed, even if the main query does not read from it.
What to say in the interview
"WITH can contain INSERT, UPDATE or DELETE with RETURNING, so I can move or chain rows atomically in one statement, like archive-on-delete. Sub-statements share one snapshot and communicate only through RETURNING."
Common mistakes
- Expecting a later step to see table changes made by an earlier step in the same WITH.