How to Use WITH Clause in PostgreSQL: Syntax and Examples

In PostgreSQL, the WITH clause lets you define temporary named result sets called common table expressions (CTEs) that you can use within a query. It helps organize complex queries by breaking them into simpler parts and can improve readability and maintainability.

Syntax

The WITH clause defines one or more named subqueries called common table expressions (CTEs). Each CTE has a name and a query. After defining CTEs, you write the main query that can reference these names as if they were tables.

• WITH: starts the CTE definition
• cte_name: the name of the temporary result set
• AS (subquery): the query that defines the CTE
• Multiple CTEs can be separated by commas
• The main query follows after the CTEs

sql

WITH cte_name AS (
  SELECT column1, column2
  FROM table_name
  WHERE condition
)
SELECT * FROM cte_name;

Example

This example shows how to use a WITH clause to find employees with salaries above the average salary. The CTE calculates the average salary first, then the main query uses it.

sql

WITH avg_salary AS (
  SELECT AVG(salary) AS average FROM employees
)
SELECT name, salary
FROM employees, avg_salary
WHERE salary > avg_salary.average;

Output

name | salary
-----+--------
John | 75000
Jane | 80000

Common Pitfalls

Common mistakes when using WITH clause include:

• Forgetting to reference the CTE name in the main query
• Using CTEs for very simple queries where a subquery would be clearer
• Not ending the CTE definition with a comma when defining multiple CTEs
• Assuming CTEs improve performance automatically; they mainly improve readability

sql

/* Wrong: missing CTE reference */
WITH cte AS (
  SELECT id FROM users
)
SELECT * FROM users;

/* Right: use the CTE name in main query */
WITH cte AS (
  SELECT id FROM users
)
SELECT * FROM cte;

Quick Reference

Use the WITH clause to:

• Break complex queries into simpler parts
• Reuse the same subquery multiple times
• Improve query readability

Remember:

• CTEs are temporary and only valid for the query they are defined in
• You can define multiple CTEs separated by commas
• CTEs can reference each other in order

Key Takeaways

The WITH clause defines temporary named result sets called CTEs for use in a query.

CTEs improve query readability by breaking complex queries into simpler parts.

Always reference the CTE name in the main query to use its results.

Multiple CTEs can be defined and can reference each other in order.

CTEs do not guarantee performance improvements but help organize queries.