PostgreSQL's Common Table Expressions (CTEs) can be used to extract sub-queries from bulky SQL statements into a temporary table to be referenced instead.

Do you need DevOps-experts?

Your development team has a full backlog? No time for infrastructure architecture? Our DevOps team is ready to support you!

• We build reliable cloud solutions with Infrastructure as code
• We are experts in security, Linux and databases
• We support your dev team to perform

Read more Show archive.org snapshot

This is most useful to avoid ugly joins or sub-selects. CTEs can be used for SELECT, INSERT, UPDATE or DELETE.

Example (from the PostgreSQL docs):

WITH regional_sales AS (
        SELECT region, SUM(amount) AS total_sales
        FROM orders
        GROUP BY region
     ), top_regions AS (
        SELECT region
        FROM regional_sales
        WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
     )
SELECT region,
       product,
       SUM(quantity) AS product_units,
       SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;

Note how the SELECT statement at the end is rather short and much easier to read. The CTE SELECTs are also quite readable. Imagine how a JOINed statement would look like...

Using sub-queries, the above statement would look something like this:

SELECT region,
       product,
       SUM(quantity) AS product_units,
       SUM(amount) AS product_sales
FROM orders
WHERE region IN (
  SELECT region FROM (
    SELECT region FROM (
      SELECT region, SUM(amount) AS total_sales
      FROM orders
      GROUP BY region
    )
    WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
  )
)
GROUP BY region, product;