What are CTEs (Common Table Expressions) in PostgreSQL?

A CTE (Common Table Expression), defined with the WITH clause, is a named temporary result set that exists only within the query. CTEs improve readability by naming intermediate results. Syntax: WITH active_users AS (SELECT * FROM users WHERE is_active = true) SELECT * FROM active_users WHERE age > 18;. Multiple CTEs: WITH cte1 AS (...), cte2 AS (...) SELECT .... Recursive CTEs: use WITH RECURSIVE to traverse hierarchical data (e.g., org charts, category trees). Data-modifying CTEs: WITH deleted AS (DELETE FROM sessions WHERE expired RETURNING id) INSERT INTO audit_log SELECT id, NOW() FROM deleted;. In PostgreSQL 12+, the planner can inline CTEs as subqueries (optimization fence was removed).