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.

Rails Long Term Support

Rails LTS provides security patches for old versions of Ruby on Rails (2.3, 3.2, 4.2 and 5.2)

• Prevents you from data breaches and liability risks
• Upgrade at your own pace
• Works with modern Rubies

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;