Common Table Expressions (CTEs) in PostgreSQL

Common Table Expressions (CTEs) in PostgreSQL: The Power of SELECT WITH


You know that feeling when your SQL query starts to resemble spaghetti code? Juggling multiple sub-queries can get messy. Enter Common Table Expressions (CTEs), a feature in PostgreSQL that allows you to write cleaner, more organized SQL queries. In this post, we’ll delve into the non-recursive variant of CTEs using the SELECT WITH clause.

What Are Common Table Expressions?

A Common Table Expression, or CTE, is a named temporary result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. The CTE is defined using the WITH clause and can be followed by a SQL query that refers to it.


Here’s the basic syntax for a CTE:

WITH cte_name (column_name1, column_name2, ...) AS (
    -- SQL query to generate the CTE data
-- Main SQL query that can refer to the CTE

Example Using the tv_series Table

Suppose you want to find the average number of seasons for TV series in each genre. First, let’s use a CTE to calculate the average for each genre.

WITH genre_avg (genre, avg_seasons) AS (
  SELECT genre, AVG(seasons)
  FROM public.tv_series
  GROUP BY genre
SELECT * FROM genre_avg;

The CTE genre_avg calculates the average number of seasons for each genre, and the main SQL query then selects all rows from this CTE.

genre avg_seasons
Drama 4.7368421052631579
Reality 5.0000000000000000
Thriller 4.0000000000000000
Fantasy 5.0000000000000000
Comedy 5.6363636363636364
Adventure 1.00000000000000000000
Science Fiction 3.6363636363636364
Crime 4.0000000000000000
Animation 14.3333333333333333

Common Errors and Solutions

Error Message:

ERROR: syntax error at or near "WITH"


Usually, this error occurs if you misspell or misuse the WITH keyword.


  • Make sure that the WITH keyword is placed correctly and that the syntax surrounding it is correct.

Best Practices and Tips

  • Use CTEs to break down complex queries into simpler parts. This makes the query easier to understand and maintain.
  • Though CTEs improve readability, they can have performance implications. Always check the query performance.


Common Table Expressions are not just a neat feature but a powerful part of the SQL language, helping you structure your queries in a more readable and maintainable way. In this guide, you’ve learned how to use the non-recursive variant of the CTE with the SELECT WITH clause, including syntax, examples based on the tv_series table, and how to troubleshoot common errors. Armed with this knowledge, you’re well on your way to writing cleaner, more organized SQL queries.

Leave a Reply