IntermediateAdvanced PostgreSQL

GENERATE_SERIES: number generator utility

The query

SQL

-- Number series
SELECT * FROM GENERATE_SERIES(1, 10) AS n;

-- Date series
SELECT GENERATE_SERIES(
  '2024-01-01'::DATE,
  '2024-12-31'::DATE,
  '1 month'::INTERVAL
)::DATE AS month_start;

-- Use to create test data
INSERT INTO products (name, price, stock)
SELECT
  'Product ' || n,
  ROUND((RANDOM() * 990 + 10)::NUMERIC, 2),
  (RANDOM() * 100)::INT
FROM GENERATE_SERIES(1, 100) n;

Tested against PostgreSQL 16

Note

“GENERATE_SERIES is a set-returning function. Invaluable for test data, calendar fills, and batch processing.

Tables referenced

products6 cols