How to Use CTE in PostgreSQL: Syntax and Examples

In PostgreSQL, you use a WITH clause to define a Common Table Expression (CTE), which is a temporary named result set you can reference in your main query. CTEs help organize complex queries by breaking them into simpler parts and can be recursive or non-recursive.

Syntax

The basic syntax of a CTE in PostgreSQL starts with the WITH keyword, followed by one or more named subqueries enclosed in parentheses. Each subquery acts like a temporary table that you can use in the main query that follows.

• WITH cte_name AS (subquery): Defines the CTE with a name and the query it represents.
• Main query: Uses the CTE name as if it were a table.

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 CTE to find employees with salaries above the average salary in the company. The CTE calculates the average salary first, then the main query selects employees earning more than that.

sql

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

Output

 name  | salary 
-------+--------
 Alice | 70000
 Bob   | 80000
(2 rows)

Common Pitfalls

Common mistakes when using CTEs include:

• Forgetting to reference the CTE name in the main query.
• Using CTEs for very large datasets without indexing, which can slow down queries.
• Assuming CTEs always improve performance; sometimes subqueries or joins are faster.
• Not ending the CTE with a semicolon if it is the last statement.

Here is an example of a wrong and right usage:

sql

/* Wrong: Missing CTE reference in main query */
WITH cte_example AS (
  SELECT id FROM products WHERE price > 100
)
SELECT * FROM products;

/* Right: Using the CTE name in the main query */
WITH cte_example AS (
  SELECT id FROM products WHERE price > 100
)
SELECT * FROM cte_example;

Quick Reference

Feature	Description
WITH	Keyword to start a CTE
cte_name	Name you assign to the temporary result set
AS	Keyword to assign the subquery to the CTE name
Subquery	The SELECT statement defining the CTE content
Main Query	Query that uses the CTE as a table

Key Takeaways

Use the WITH clause to define a CTE as a named temporary result set.

CTEs help simplify complex queries by breaking them into readable parts.

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

CTEs can be recursive or non-recursive depending on your needs.

CTEs do not always improve performance; test your queries for efficiency.