CTE with INSERT ... RETURNING (PG)

CTE with INSERT ... RETURNING (PG) is a SQL statement in the CTE (WITH) category. PostgreSQL: a data-modifying CTE that uses RETURNING to pass inserted/updated data to the main query. The syntax is WITH ins AS (INSERT INTO table VALUES (...) RETURNING id) SELECT * FROM ins;. It returns data-modifying CTE result. A typical example: WITH new_employee AS ( INSERT INTO employees (name, salary) VALUES ('Alice', 75000) RETURNING id, name ) SELECT id, name, 'Welcome!' AS message FROM new_employee; -- Insert and retrieve the new row in one statement -- Update then read: WITH updated AS ( UPDATE employees SET salary = salary * 1.1 WHERE department = 'IT' RETURNING id, name, salary ) SELECT * FROM updated; 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).