GENERATE_SERIES
MySQL equivalent:
Generates a series of values from start to stop with the given step, one row per value.
PostgreSQL 17.5
GENERATE_SERIES(start, stop [, step])Parameters
- startinteger | numeric | timestamp
- First value of the series
- stopinteger | numeric | timestamp
- Bound of the series, included if the step lands on it
- stepinteger | numeric | intervaloptional
- Step of the series, 1 by default; required for dates and given as an interval
Return value
A set of rows of the same type as start (timestamp with time zone for DATE); an empty set if start is greater than stop with a positive step, or less with a negative one
Examples
Numbers with step 3
PostgreSQL 17.5
SELECT GENERATE_SERIES(1, 10, 3);Dates day by day
PostgreSQL 17.5
SELECT GENERATE_SERIES(
DATE '2024-03-01',
DATE '2024-03-05',
INTERVAL '1 day'
)::DATE AS DAY;Series in FROM like a table
PostgreSQL 17.5
SELECT n,
n * n AS square
FROM GENERATE_SERIES(1, 5) AS n;Payments by month, including empty ones
PostgreSQL 17.5
SELECT TO_CHAR(m, 'YYYY-MM') AS MONTH,
COUNT(p.payment_id) AS payments
FROM GENERATE_SERIES(
DATE '2005-01-01',
DATE '2005-12-01',
INTERVAL '1 month'
) AS m
LEFT JOIN Payments AS p ON DATE_TRUNC('month', p.date) = m
GROUP BY m
ORDER BY m;Countdown with a negative step
PostgreSQL 17.5
SELECT GENERATE_SERIES(10, 1, -3) AS countdown;Details
The function is most often used in FROM like a table: FROM GENERATE_SERIES(1, 5) AS n. This way you can, for example, get every month of a year and join data to it with LEFT JOIN to see the months without records.
A step of 0 causes an error. With a negative step the series goes down, so start must be greater than stop.