Mastering Common Table Expressions (CTEs) in PostgreSQL for Complex Queries

Mastering Common Table Expressions (CTEs) in PostgreSQL for Complex Queries Common Table Expressions (CTEs) are one of PostgreSQL's most powerful features for writing modular, readable, and maintainable SQL. In this post, we’ll go deep into how CTEs work, when to use them, and some advanced techniques to leverage their full potential. What Is a CTE? A Common Table Expression is a temporary result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. Think of it like a subquery with a name. WITH active_users AS ( SELECT id, name FROM users WHERE active = true ) SELECT * FROM active_users WHERE name ILIKE '%john%'; This improves readability and allows reuse of the same logic in multiple places within a single query. Chaining Multiple CTEs You can define multiple CTEs by separating them with commas: WITH recent_orders AS ( SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '7 days' ), top_customers AS ( SELECT user_id, COUNT() AS order_count FROM recent_orders GROUP BY user_id ) SELECT u.name, t.order_count FROM top_customers t JOIN users u ON u.id = t.user_id ORDER BY t.order_count DESC; Recursive CTEs Recursive CTEs are useful for traversing hierarchical data, like trees or organizational charts. WITH RECURSIVE subordinates AS ( SELECT id, name, manager_id FROM employees WHERE id = 1 UNION ALL SELECT e.id, e.name, e.manager_id FROM employees e INNER JOIN subordinates s ON s.id = e.manager_id ) SELECT FROM subordinates; This example retrieves all employees under a specific manager, no matter how deep the hierarchy. Using CTEs With Modifications CTEs can be used in UPDATE and DELETE statements for complex data manipulation. WITH flagged_orders AS ( SELECT id FROM orders WHERE total < 0 ) DELETE FROM orders WHERE id IN (SELECT id FROM flagged_orders); Materialization Behavior PostgreSQL 12+ added support for inlining CTEs. Previously, they were always materialized (evaluated once and stored), which could negatively impact performance. If needed, you can force materialization manually: WITH active_users AS MATERIALIZED ( SELECT * FROM users WHERE active = true ) SELECT * FROM active_users; CTEs vs. Subqueries While CTEs improve readability, they may sometimes be less performant than inline subqueries. Always check execution plans using EXPLAIN ANALYZE: EXPLAIN ANALYZE WITH ... SELECT ...; Conclusion CTEs can simplify the structure of complex queries, enable recursive operations, and provide a more readable alternative to nested subqueries. Used correctly, they’re an essential tool in any PostgreSQL developer’s toolbox. If this post helped you, consider supporting me: buymeacoffee.com/hexshift

Apr 15, 2025 - 17:12

Mastering Common Table Expressions (CTEs) in PostgreSQL for Complex Queries

Common Table Expressions (CTEs) are one of PostgreSQL's most powerful features for writing modular, readable, and maintainable SQL. In this post, we’ll go deep into how CTEs work, when to use them, and some advanced techniques to leverage their full potential.

What Is a CTE?

A Common Table Expression is a temporary result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. Think of it like a subquery with a name.

WITH active_users AS (
  SELECT id, name FROM users WHERE active = true
)
SELECT * FROM active_users WHERE name ILIKE '%john%';

This improves readability and allows reuse of the same logic in multiple places within a single query.

Chaining Multiple CTEs

You can define multiple CTEs by separating them with commas:

WITH recent_orders AS (
  SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '7 days'
),
top_customers AS (
  SELECT user_id, COUNT(*) AS order_count
  FROM recent_orders
  GROUP BY user_id
)
SELECT u.name, t.order_count
FROM top_customers t
JOIN users u ON u.id = t.user_id
ORDER BY t.order_count DESC;

Recursive CTEs

Recursive CTEs are useful for traversing hierarchical data, like trees or organizational charts.

WITH RECURSIVE subordinates AS (
  SELECT id, name, manager_id
  FROM employees
  WHERE id = 1

  UNION ALL

  SELECT e.id, e.name, e.manager_id
  FROM employees e
  INNER JOIN subordinates s ON s.id = e.manager_id
)
SELECT * FROM subordinates;

This example retrieves all employees under a specific manager, no matter how deep the hierarchy.

Using CTEs With Modifications

CTEs can be used in UPDATE and DELETE statements for complex data manipulation.

WITH flagged_orders AS (
  SELECT id FROM orders WHERE total < 0
)
DELETE FROM orders
WHERE id IN (SELECT id FROM flagged_orders);

Materialization Behavior

PostgreSQL 12+ added support for inlining CTEs. Previously, they were always materialized (evaluated once and stored), which could negatively impact performance. If needed, you can force materialization manually:

WITH active_users AS MATERIALIZED (
  SELECT * FROM users WHERE active = true
)
SELECT * FROM active_users;

CTEs vs. Subqueries

While CTEs improve readability, they may sometimes be less performant than inline subqueries. Always check execution plans using EXPLAIN ANALYZE:

EXPLAIN ANALYZE
WITH ...
SELECT ...;

Conclusion

CTEs can simplify the structure of complex queries, enable recursive operations, and provide a more readable alternative to nested subqueries. Used correctly, they’re an essential tool in any PostgreSQL developer’s toolbox.

If this post helped you, consider supporting me: buymeacoffee.com/hexshift