How to Use the PostrgreSQL Crosstab Module

When you run a SELECT statement to retrieve the records from a PostgreSQL table, the data is returned in a tabular form. The tabular form may not always be the best for reading the data, especially when you want to compare the values. A better approach is to return the records as a crosstab. For that, PostgreSQL offers the tablefunc to allow the users to utilize the crosstab function to represent the target data as a pivot table. This post elaborates more on how to use the PostgreSQL crosstab function. Read on!

Working with the PostgreSQL Crosstab Module

Having a room to specify how you want your data to be represented is handy. With PostgreSQL, although the default way is to have the data in a tabular form, the crosstab module gives you another option. You can create the pivot tables using the “select” columns from a PostgreSQL table for a better presentation.

The amazing feature comes as an extension for PostgreSQL version 8.3. The “tablefunc” extension offers the crosstab function, making it possible to have the pivot tables as your result set. Let’s give an example.

First, we create the “results” table with three columns using the following command:

The created table is empty. Thus, go ahead and use the INSERT query to add the values to it. For this case, we insert a few entries as shown in the following:

If we run the “select” statement, we get the following results. Note how this default way of retrieving the records presents them in a tabular form. The columns are used as the headers and their respective values appear in the rows:

Although the previous approach works for most cases, there is another option to retrieve the same records. For this case, we want to create a pivot table. Therefore, we use the “tablefunc” extension that comes with PostgreSQL version 8.3+.

So, let’s create a “tablefunc” extension to enable it when we call it in the query. Execute the following command:

You will know that the extension has been successfully enabled once you get an output like the one that we have in the previous image.

For our crosstab function, let’s make it such that the first column is the faculty. Below it are the various faculties that we have in our table. Next to the faculty column, we have other columns that represent the different values in the category column of our table. Lastly, for each category, we have the number of students who attained the different categories.

To achieve such a pivot table, use the following syntax:

Using the example table that we created, our command looks as follows:

Before we see the results, let’s have a breakdown on how the crosstab module command works. First, the SELECT statement returns three columns. The first column is treated as the row identifier. In our case, it is the “faculty” column. The second column, which is the “category” column, represents the categories for the pivot table. Lastly, the third column contains the values for the categories. In our case, it is the number of students in each category.

Having that understanding, once we press the “Enter” key, we get the pivot table as follows:

Based on the returned pivot table, we can see that it works like a 2-D array where the first column in the SELECT statement is the first array and the second column is the second array in the dimension. The third column contains the values for the second array elements.

Conclusion

The PostgreSQL crosstab is good for representing the table values as a pivot table. You must return three columns in your SELECT statement to use the crosstab module, and this post has shared a practical example on how to use it. Besides, we explained how the crosstab module works in creating the pivot table that uses the same logic as a 2-D array.