PostgreSQL Crosstab: Pivot Queries

Tip: Enable tablefunc

CREATE EXTENSION tablefunc;

Gotcha: Crosstab Function

SELECT * FROM crosstab(
    'SELECT category, month, revenue FROM sales ORDER BY 1, 2',
    'SELECT DISTINCT month FROM sales ORDER BY 1'
) AS ct (category text, jan int, feb int, mar int);

Tip: Pivot Rows to Columns

Transforms row data into a column-based report format.

Gotcha: Column Definition Required

You must define all output columns explicitly. Dynamic columns aren't supported.

Tip: Alternative with FILTER

SELECT category,
    sum(revenue) FILTER (WHERE month = 'jan') as jan,
    sum(revenue) FILTER (WHERE month = 'feb') as feb
FROM sales GROUP BY category;

Often simpler than crosstab.

Gotcha: NULL Handling

Missing values become NULL. Use COALESCE to replace with 0.

Tip: EXPLAIN (ANALYZE, BUFFERS) Is Your Best Friend

For query debugging, always use EXPLAIN (ANALYZE, BUFFERS) instead of plain EXPLAIN. The BUFFERS option shows hit/miss rates for every node, revealing whether your indexes are actually in memory.

Tip: Partial Indexes Are Underutilized

CREATE INDEX ON orders (status) WHERE status = 'pending' creates a tiny index that covers only the rows your query needs. It's faster to scan and cheaper to maintain than a full-column index.

Gotcha: NULL Sorting Is Non-Obvious

By default, NULLs sort AFTER non-null values in ascending order. ORDER BY col DESC puts NULLs FIRST. Use NULLS LAST or NULLS FIRST to be explicit.

Senior Insight

The tablefunc extension's crosstab() function turns rows into columns — essentially a PIVOT operation. I've used it for reporting dashboards that need month-over-month comparisons in a single result set. The function is powerful but finicky: it requires exactly three columns (row_name, category, value) and the category values must be specified in a second query. I write a wrapper function for each crosstab query to make it reusable.

Source: pganalyze Blog (https://pganalyze.com/blog), PostgreSQL Docs (https://www.postgresql.org/docs/current/), Crunchy Data Blog (https://www.crunchydata.com/blog)