How to Create Pivot Tables in PostgreSQL

What is a PostgreSQL pivot table?

Pivot tables reorganize row-based data into a summarized cross-tabulation format, thus transforming values that appear as rows into columns. This option makes it easier to compare and analyze data across two dimensions simultaneously.

• One column's values become the row labels.
• Another column's values become the column headers.
• A numeric or aggregate value (such as SUM, COUNT, or AVG) fills the intersecting cells.

For example, let's take data like this.

It can be transformed into a pivot-style result.

This cross-tab layout makes it easier to compare values across categories.

Ways to create a pivot table in PostgreSQL

Unlike Excel or SQL Server, PostgreSQL does not have a built-in PIVOT operator. Users typically create pivot-style results using one of these approaches:

• crosstab() from the tablefunc extension, which is designed specifically for creating cross-tab reports.
• Conditional aggregation, using expressions such as SUM(CASE WHEN ...) or COUNT(...) FILTER (WHERE ...), which is often the simplest and most portable solution.
• Dynamic SQL that relies on PL/pgSQL to create pivot tables for data where some categories can't be specified in advance in the query.
• GUI tools, such as database clients or reporting applications, that can display PostgreSQL query results as pivot tables without requiring complex SQL.

The table below explains the differences between these options.

How to create a PostgreSQL pivot table with crosstab()

crosstab() is part of the optional tablefunc module. To start using this function, you need to install the tablefunc module for the required database first. In PostgreSQL 9.1 and later, you can do it by running a simple command.

CREATE EXTENSION IF NOT EXISTS tablefunc;

After installing the module, you can use crosstab to get a pivoted result set. In the case below, we are working with the sakila database. The goal is to reorganize the data from the payment table to view payments by date.

SELECT *
FROM crosstab(
  $$
  SELECT 
    payment_date::date,  -- row identifier (date)
    customer_id::text,   -- pivot column (customer_id as text)
    SUM(amount)::numeric  -- aggregated value
  FROM public.payment
  GROUP BY payment_date::date, customer_id
  ORDER BY payment_date::date, customer_id
  $$
) AS ct(
  payment_date date,
  customer_1 numeric,
  customer_2 numeric,
  customer_3 numeric,
  customer_4 numeric,
  customer_5 numeric
);

How to pivot rows to columns in PostgreSQL without crosstab

In PostgreSQL, rows can be pivoted to columns without using crosstab(). This approach is called conditional aggregation, a portable SQL option that doesn't require the tablefunc extension.

Conditional aggregation works in the following way:

• Group values by the row identifier.
• Use CASE expressions to place values into the desired columns.
• Aggregate the results with functions like SUM(), MAX(), or COUNT().

Assume we need to know how many rentals each customer made per month. The necessary data is in the rental table, and we apply conditional aggregation to create a pivoted table.

SELECT
  c.customer_id,
  c.first_name,
  c.last_name,

  COUNT(CASE
  WHEN EXTRACT(MONTH FROM r.rental_date) = 5
  THEN 1
  END) AS may,

  COUNT(CASE
  WHEN EXTRACT(MONTH FROM r.rental_date) = 6
  THEN 1
  END) AS june,

  COUNT(CASE
  WHEN EXTRACT(MONTH FROM r.rental_date) = 7
  THEN 1
  END) AS july,

  COUNT(CASE
  WHEN EXTRACT(MONTH FROM r.rental_date) = 8
  THEN 1
  END) AS august

FROM customer c
  LEFT JOIN rental r
    ON c.customer_id = r.customer_id
GROUP BY c.customer_id,
         c.first_name,
         c.last_name
ORDER BY c.customer_id;

Static vs dynamic pivot tables in PostgreSQL

A static pivot works well when you know the values that will become columns. It is simple, efficient, and easy to read, a preferred solution for such scenarios as months or quarters in a reporting period, ratings, categories, etc.

However, there are scenarios where values that become columns are not known ahead of time or may change. For example, when new products or categories are added, or when survey options change, and so on. PostgreSQL cannot automatically create new columns as new categories appear.

As a result, you need to query the database to discover the distinct category values and then write a SQL statement with those values as columns, building the query as a string at runtime. This approach suggests an extra step, which takes time and resources; it also requires knowledge of PL/pgSQL to construct a string for each new column.

On the other hand, there is an alternative: working with changing datasets visually using the built-in Pivot Tables tool in dbForge Studio for PostgreSQL.

How to create a pivot table in dbForge Studio for PostgreSQL

dbForge Studio for PostgreSQL is an AI-powered, robust IDE for PostgreSQL, Amazon Redshift, and related databases and cloud platforms. It provides a full set of tools to perform all standard database development, management, and data analysis tasks. Visual pivot tables are just one of many useful tools provided by this solution.

Creating pivot tables and charts with dbForge Studio

To create a pivot table, launch dbForge Studio for PostgreSQL and execute a query to select the data you want to analyze. Right-click the data grid of the query output and select Send > Pivot Table.

To add fields to the pivot table, simply drag them from the Data Source to the required pivot table area.

As a result, we get a pivot table very similar to the table created with the help of the crosstab() function. Note that dbForge Studio for PostgreSQL automatically generates the Grand Total columns. By default, the Grand Total cells contain the sum of the values of the rows (columns).

You can also visualize data dependencies and compare them using charts associated with the pivot data: click Show Chart on the pivot table document toolbar and select the chart type.

Reordering fields in pivot tables

Changing the layout of a pivot table is key to making data easier to read and scan for details. Simply drag a field from one area of a pivot table to another to redesign its layout and organize data differently.

To push the functionality further, you can transpose rows to columns to display row field values in columns and column field values in rows. This makes extracting insights from large datasets straightforward.

Sorting, grouping, and filtering fields

Data in a pivot table can be sorted and grouped to reveal relationships, patterns, and trends.

• To sort data in ascending or descending order, click the sorting icon on the field header.
• To group values in a pivot table, right-click it in the Data Source area and select a required group mode from the menu.

You can also use filters to display only the required data in your pivot table. Click the filtering icon on the field header and select the values to be displayed. It is also possible to create custom filters.

Using summary functions to calculate custom totals

The pivot table offers a number of summary functions that you can apply to the required column or row fields to calculate custom totals for their values. Custom totals are displayed in additional rows or columns, highlighted in color.

The sum values are calculated by default, and you can see them immediately after adding row and column fields in a pivot table. You can also apply a different summary function if required:

• Average of values
• Count of values
• Maximum value
• Minimum value

PostgreSQL crosstab vs pivot tables in dbForge Studio

When working with data, pivot tables are a powerful way to summarize information and uncover insights quickly. In PostgreSQL, however, creating pivot tables is not always straightforward. While the built-in crosstab() function provides pivoting capabilities, it requires additional setup and has several limitations compared to the intuitive drag-and-drop experience offered by GUI tools. The table below highlights the key differences between using crosstab() in PostgreSQL and creating pivot tables in dbForge Studio.

Common PostgreSQL pivot table problems

PostgreSQL can produce pivot tables, but there are a few recurring pain points. Here are the most common pivot table problems in PostgreSQL:

crosstab() requires a predefined output structure

The tablefunc extension helps but introduces its own constraint: users must explicitly declare output columns. So even though it pivots rows, it is still not truly dynamic.

Dynamic pivots require SQL generation.

When categories are unknown or changing, PostgreSQL cannot automatically generate columns. Instead, it requires you to query distinct values first, then build a query that relies on those values, and execute it. This aspect adds complexity and makes debugging harder.

Handling NULLs and missing values

Pivot results often contain NULLs when no matching rows exist. This leads to an extra cleanup step to avoid incomplete or misleading data in reports.

Complex aggregation queries

Pivot tables require aggregation functions (COUNT, SUM, etc.), and mistakes are easy to make—especially when working with large datasets that demand solid SQL knowledge.

Readability and performance considerations

Poorly written pivot queries can be expensive because they scan every row and evaluate it against multiple CASE conditions and perform multiple aggregations in a single pass.

This is why SQL pivots are often paired with GUI tools rather than used alone.

Conclusion

Pivot tables provide interactive data visualization that is vastly superior to the crosstab() capabilities in PostgreSQL. This feature offers flexibility and functionality for summarizing large data sets, which allows, among other things, creating charts for both all data and a selected data range.

You can try the dbForge Studio for PostgreSQL tool in practice: just download a fully functional free trial, install it, and see for yourself that extracting significance from a large, detailed data set has never been easier.

FAQ