How to Use tablefunc Extension in PostgreSQL: Syntax and Examples

To use the tablefunc extension in PostgreSQL, first enable it with CREATE EXTENSION IF NOT EXISTS tablefunc;. This extension provides functions like crosstab() to pivot query results, turning rows into columns for easier data analysis.

Syntax

The tablefunc extension provides several functions, the most common being crosstab(). Its basic syntax is:

crosstab(text source_sql) RETURNS setof record

Here, source_sql is a SQL query returning three columns: row identifier, category, and value. The function pivots categories into columns.

sql

CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT * FROM crosstab(
  'SELECT rowid, category, value FROM your_table ORDER BY 1,2'
) AS ct(rowid text, category1 int, category2 int, category3 int);

Example

This example shows how to pivot sales data by product categories for each month using crosstab().

sql

CREATE EXTENSION IF NOT EXISTS tablefunc;

CREATE TABLE sales(
  month text,
  product text,
  amount int
);

INSERT INTO sales VALUES
('Jan', 'Apples', 10),
('Jan', 'Oranges', 20),
('Feb', 'Apples', 15),
('Feb', 'Oranges', 25),
('Mar', 'Apples', 10),
('Mar', 'Oranges', 30);

SELECT * FROM crosstab(
  'SELECT month, product, amount FROM sales ORDER BY 1,2'
) AS ct(month text, Apples int, Oranges int);

Output

 month | Apples | Oranges 
-------+--------+---------
 Jan   |     10 |      20
 Feb   |     15 |      25
 Mar   |     10 |      30
(3 rows)

Common Pitfalls

• Not enabling the extension first with CREATE EXTENSION IF NOT EXISTS tablefunc;.
• Providing a source query to crosstab() that does not return exactly three columns: row id, category, and value.
• Not specifying the output column names and types in the AS clause after crosstab().
• Ordering the source query incorrectly; it must be ordered by row identifier and category.

sql

/* Wrong: missing extension */
-- SELECT * FROM crosstab('SELECT 1,2,3');

/* Wrong: source query returns wrong columns */
-- SELECT * FROM crosstab('SELECT month, amount FROM sales');

/* Correct usage */
CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT * FROM crosstab(
  'SELECT month, product, amount FROM sales ORDER BY 1,2'
) AS ct(month text, Apples int, Oranges int);

Quick Reference

Summary tips for using tablefunc:

• Enable with CREATE EXTENSION IF NOT EXISTS tablefunc;.
• Use crosstab() to pivot rows to columns.
• Source query must return three columns: row id, category, value.
• Always specify output columns and types after crosstab().
• Order source query by row id and category.

Key Takeaways

Enable the tablefunc extension with CREATE EXTENSION IF NOT EXISTS before using its functions.

Use crosstab() to pivot data by providing a source query with row id, category, and value columns.

Always specify the output column names and types after crosstab() for correct results.

Ensure the source query is ordered by row identifier and category for proper pivoting.

Common errors include missing extension, wrong source query columns, and missing output column definitions.