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.

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;

postgresql.org

Arne Hartherz

makandra.com

Say thanks6

Last edit

About 1 year ago

Michael Leimstädtner

License

Source code in this card is licensed under the MIT License.

Rails: namespacing models with table_name_prefix instead of table_name

When you want to group rails models of a logical context, namespaces are your friend. However, if you have a lot of classes in the same namespace it might be tedious to specify the table name for each class seperately:

class Accounting::I...

Sticky table header with jQuery

When you want the table headers to always stay around (e.g. because that table is huuuge), use the code below.

Style

table.fixed_table_header{
  position: fixed;
  top: 0;
  width: auto;
  display: none;
  border: none;
  margin: 0;
}
`...

SQL: Find out number of rows of all tables within a MySQL database

Here you are:

SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'your_database' order by table_rows;

PostgreSQL: How to show table sizes

When you have a large PG database, you may want to find out which tables are consuming the most disk space.
You can easily check this using the following SQL statement from the PostgreSQL wiki.

SELECT nspname || '.' || relname AS "relation",

...

Cucumber step to match table rows with Capybara

These steps are now part of Spreewald.

This note describes a Cucumber step that lets you write this:

Then I should see a table with the following rows:
  | Br...

Posted by Arne Hartherz to makandra dev (2017-10-30 17:49)

PostgreSQL: WITH Queries (Common Table Expressions)

Related cards:

Option 1: Drop the entire schema

Style

Proven configuration for sorting table rows