The tablefunc extension provides a set of functions for manipulating tables, including cross tabulation, pivoting, and connecting tables. Your Nile database arrives with the tablefunc extension already enabled.

​
Overview

The tablefunc extension provides several useful functions:

  • crosstab: Creates pivot tables and cross tabulations
  • normal_rand: Generates normally distributed random numbers
  • connectby: Implements hierarchical queries

​
Cross Tabulation Functions

​
Basic Crosstab

The crosstab function transforms row-oriented data into a cross-tabulation format (pivot table).

-- Basic crosstab syntax
SELECT * FROM crosstab(
    source_sql text,   -- SQL query returning (row_name, category, value)
    category_sql text  -- SQL query returning distinct categories
) AS ct (
    row_name text,     -- Name of the row
    category1 text,    -- First category column
    category2 text,    -- Second category column
    ...
);

​
Example: Sales by Quarter

-- Create sample data
CREATE TABLE quarterly_sales (
    tenant_id uuid,
    year int,
    quarter text,
    sales numeric
);

INSERT INTO quarterly_sales VALUES
    ('11111111-1111-1111-1111-111111111111', 2023, 'Q1', 100),
    ('11111111-1111-1111-1111-111111111111', 2023, 'Q2', 150),
    ('11111111-1111-1111-1111-111111111111', 2023, 'Q3', 130),
    ('11111111-1111-1111-1111-111111111111', 2023, 'Q4', 180),
    ('11111111-1111-1111-1111-111111111111', 2024, 'Q1', 120),
    ('11111111-1111-1111-1111-111111111111', 2024, 'Q2', 160);

-- Create cross tab of sales by year and quarter
SELECT * FROM crosstab(
    'SELECT year, quarter, sales 
     FROM quarterly_sales 
     ORDER BY 1,2',
    'SELECT DISTINCT quarter 
     FROM quarterly_sales 
     ORDER BY 1'
) AS ct (
    year int,
    "Q1" numeric,
    "Q2" numeric,
    "Q3" numeric,
    "Q4" numeric
);

Result:

 year | Q1  | Q2  | Q3  | Q4
------+-----+-----+-----+-----
 2023 | 100 | 150 | 130 | 180
 2024 | 120 | 160 | null| null

​
Normal Random Numbers

The normal_rand function generates normally distributed random numbers:

-- Generate 5 normal random numbers
SELECT * FROM normal_rand(
    5,    -- number of rows
    15    -- standard deviation
);

​
Hierarchical Queries

The connectby function helps create hierarchical queries. Let’s create a sample employee hierarchy and query it:

-- Create sample employee hierarchy
CREATE TABLE employees (
    tenant_id uuid,
    employee_id int,
    name text,
    manager_id int,
    PRIMARY KEY (tenant_id, employee_id)
);

INSERT INTO employees VALUES
    ('11111111-1111-1111-1111-111111111111', 1, 'CEO', NULL),   
    ('11111111-1111-1111-1111-111111111111', 2, 'VP Sales', 1),
    ('11111111-1111-1111-1111-111111111111', 3, 'VP Engineering', 1),
    ('11111111-1111-1111-1111-111111111111', 4, 'Sales Manager', 2),
    ('11111111-1111-1111-1111-111111111111', 5, 'Engineer', 3);

-- Query hierarchical employee structure
-- Order of results is not guaranteed
SELECT * FROM connectby(
    'employees', 'employee_id', 'manager_id', '1', 0, '>')
 AS t(employee_id int, manager_id int, level int, branch text);

Result:

 employee_id | manager_id | level | branch
-------------+------------+-------+--------
           1 |            |     0 | 1
           2 |          1 |     1 | 1>2
           4 |          2 |     2 | 1>2>4
           3 |          1 |     1 | 1>3
           5 |          3 |     2 | 1>3>5

​
Additional Resources

Tablefunc is a powerful extension for working with tables and hierarchies. We only covered a few basic examples here, but there are many more options available. You can read the rest in PostgreSQL tablefunc Documentation

Was this page helpful?

RandomXicor