Part 84: Modifying CTEs with RETURNING in PostgreSQL

In PostgreSQL, CTEs can contain INSERT, UPDATE, or DELETE statements with a RETURNING clause:

1. Moving Rows Atomically

Move deleted items into an archive table in a single atomic statement:

WITH DeletedRows AS (
  DELETE FROM active_sessions
  WHERE last_active < NOW() - INTERVAL '30 days'
  RETURNING session_id, user_id, last_active
)
INSERT INTO archived_sessions (session_id, user_id, last_active)
SELECT session_id, user_id, last_active FROM DeletedRows;

Key Takeaways

• Data-modifying CTEs enable atomic migrations between tables in a single query.

In Part 85, we enter the world of Recursive CTEs: Anchor and Recursive Members!