Bulk inserts & data-modifying CTEs

Insert from a query, and chain writes inside WITH.

Write from a query

INSERT ... SELECT copies rows from one query straight into a table — the backbone of archiving, snapshots, and ETL:

INSERT INTO archived (id, customer_id)
SELECT id, customer_id FROM orders WHERE status = 'cancelled';

And because INSERT/UPDATE/DELETE all support RETURNING, you can wrap one in a CTE and feed its output into the next step — a data-modifying CTE:

WITH gone AS (DELETE FROM reviews WHERE stars < 3 RETURNING *)
SELECT count(*) AS removed FROM gone;

(generate_series(1, 100) is the easy way to manufacture bulk rows for testing.)

RETURNING turns a write into a row source. The DELETE runs once; its returned rows live in the CTE named gone, and the outer SELECT counts them — one statement, one pass, no second query to find out what was removed.