Quick summary
Summarize this blog with AI
Use crosstab with a fixed category map
A PostgreSQL pivot can be written with conditional aggregation or with crosstab from the tablefunc extension. This tutorial demonstrates crosstab’s two-query form: one query supplies product-month totals, and another supplies the exact category order. It includes missing months, repeated product-month rows, a product with no sales, and an unexpected month outside the report.
Name the output grain and period
The report has one row per product and January, February, and March columns for one reporting year. Values are numeric totals, not dates. Two January sale rows of 10 and 5 must become 15. Missing February for product 1 must stay in the February position rather than shifting March’s value left. Product 3 remains present because it belongs to the product dimension.
The category query is a fixed ordered list: 1, 2, 3. The declared output types and column order must match that list. Do not discover only observed months if the report requires a stable quarter: a month with no sales anywhere would vanish from the discovered category list. Do not use an ellipsis in a result type declaration; every output column must be specified.
Run the full disposable fixture
This script targets PostgreSQL 16 with the tablefunc extension files available. Run it in a disposable practice database where you are authorized to create the extension. It expects the extension in the public schema and fails explicitly if an existing installation uses another schema. In a managed application database, have its owner arrange prerequisites rather than installing extensions as an incidental query step. The transaction ends with ROLLBACK, including an extension created by this script.
BEGIN;
CREATE EXTENSION IF NOT EXISTS tablefunc WITH SCHEMA public;
DO $$ BEGIN
IF (SELECT extnamespace::regnamespace::text FROM pg_extension
WHERE extname='tablefunc')<>'public' THEN
RAISE EXCEPTION 'This fixture expects tablefunc installed in public';
END IF;
END $$;
CREATE TEMP TABLE pivot_sales(product_id integer,month_no integer,
amount numeric) ON COMMIT DROP;
INSERT INTO pivot_sales VALUES
(1,1,10),(1,1,5),(1,3,9),(2,2,7),(2,4,99);
CREATE TEMP TABLE pivot_products(product_id integer PRIMARY KEY) ON COMMIT DROP;
INSERT INTO pivot_products VALUES (1),(2),(3);
SELECT p.product_id,c.january,c.february,c.march
FROM pg_temp.pivot_products p
LEFT JOIN public.crosstab(
$$SELECT product_id,month_no,SUM(amount)
FROM pg_temp.pivot_sales WHERE month_no BETWEEN 1 AND 3
GROUP BY product_id,month_no ORDER BY product_id,month_no$$,
$$SELECT month_no FROM (VALUES (1),(2),(3)) m(month_no) ORDER BY month_no$$
) AS c(product_id integer,january numeric,february numeric,march numeric)
USING(product_id)
ORDER BY p.product_id;
SELECT COUNT(*) AS excluded_rows,SUM(amount) AS excluded_amount
FROM pg_temp.pivot_sales WHERE month_no NOT BETWEEN 1 AND 3;
ROLLBACK;product_id | january | february | march
1 | 15 | NULL | 9
2 | NULL | 7 | NULL
3 | NULL | NULL | NULL
excluded_rows | excluded_amount
1 | 99The aggregate produces at most one value per product-month before crosstab runs. The category query maps month 3 to march even though product 1 has no month 2. The outer product join retains product 3, which has no source row at all. April’s 99 is explicitly excluded and reported. The known quarter totals are 15 + 9 + 7 = 31.
Why the two-query form matters
The single-query crosstab form fills available value columns from successive input rows; its second input column does not provide the explicit category map used here. With sparse categories, that can put March into the second value column. The two-query form is designed to align named categories with fixed output positions. See the official tablefunc reference for both contracts.
Duplicate product-month records are not automatically invalid: they may be separate sales. SUM is the chosen rule here. If they are duplicate copies of one source transaction, repair that event-level duplication first. A MAX aggregate is not a substitute for defining which fact each row represents. Keep the original event key available for reconciliation.
Missing values are not always zero
crosstab leaves missing cells NULL. Converting them to zero can be appropriate for a complete sales feed where absence means no sales, but it can also conceal missing ingestion. If existing amount rows can themselves be NULL, a sum may represent only known amounts; add completeness measures before calling the result a full revenue total. The conditional aggregation guide demonstrates presence and known-value counts.
Tune the input query without changing its meaning
Restrict the intended reporting period, aggregate at product-month grain, and select only required columns. Then inspect the input query’s plan on representative data. An outer LIMIT does not guarantee that the underlying aggregate processes only that many source rows. Index usefulness depends on selectivity and ordering; do not promise that one index makes every pivot fast.
Dynamic headers require another interface
The column definition following AS is fixed when the query is parsed. A function declared to return three fixed fields cannot become an arbitrary number of month columns merely by executing dynamic SQL internally. A dynamic export needs generated statement text plus a client-visible execution step, or a stable alternative such as long-form rows. Use the complete dynamic pivot export when that changing shape is intentional.
Check category positions, not just totals
A pivot can preserve a grand total while putting values under the wrong month headers. Assert both: product 1 has January 15 and March 9, product 2 has February 7, and product 3 remains present. Add a missing-category and an out-of-scope-category case to every fixed-column report test.
FAQ
Is crosstab required? No; conditional aggregation works without tablefunc. Why are all three value columns numeric? They contain the third source column’s aggregate values. Month labels are the category map, not alternating date/value output fields. Does crosstab resolve conflicting records? No; establish the aggregate or winner rule before reshaping.
