Introduction to the PostgreSQL CUBE

PostgreSQLCUBE is a subclause of theGROUP BY clause. TheCUBE allows you to generate multiple grouping sets.

A grouping set is a set of columns to which you want to group. For more information on the grouping sets, check it out theGROUPING SETS tutorial.

The following illustrates the syntax of theCUBE subclause:

SELECT    c1,    c2,    c3,    aggregate (c4)FROM    table_nameGROUP BY    CUBE (c1, c2, c3);

In this syntax:

• First, specify theCUBE subclause in theGROUP BY clause of the SELECT statement.
• Second, in the select list, specify the columns (dimensions or dimension columns) which you want to analyze andaggregation function expressions.
• Third, in theGROUP BY clause, specify the dimension columns within the parentheses of theCUBE subclause.

The query generates all possible grouping sets based on the dimension columns specified inCUBE. TheCUBE subclause is a short way to define multiple grouping sets so the following are equivalent:

CUBE(c1,c2,c3)GROUPINGSETS (    (c1,c2,c3),    (c1,c2),    (c1,c3),    (c2,c3),    (c1),    (c2),    (c3),    () )

In general, if the number of columns specified in theCUBE isn, then you will have $2^n$ combinations.

PostgreSQL allows you to perform a partial cube to reduce the number of aggregates calculated. The following shows the syntax:

SELECT    c1,    c2,    c3,    aggregate (c4)FROM    table_nameGROUP BY    c1,    CUBE (c1, c2);

PostgreSQL CUBE examples

We will use thesales table created in theGROUPING SETS tutorial for the demonstration.

The following query uses theCUBE subclause to generate multiple grouping sets:

SELECT    brand,    segment,    SUM (quantity)FROM    salesGROUP BY    CUBE (brand, segment)ORDER BY    brand,    segment;

Here is the output:

The following query performs a partial cube:

SELECT    brand,    segment,    SUM (quantity)FROM    salesGROUP BY    brand,    CUBE (segment)ORDER BY    brand,    segment;

In this tutorial, you have learned how to use the PostgreSQLCUBE to generate multiple grouping sets.

Last updated onJuly 1, 2024

On this page

• Introduction to the PostgreSQL CUBE
• PostgreSQL CUBE examples