GENERATE_SERIES
MySQL equivalent:
Generates a series of values from start to stop with the given step, one row per value.
PostgreSQL 17.9
GENERATE_SERIES(start, stop [, step])- [ ]optional part
- start
- First value of the seriesinteger | numeric | timestamp
- stop
- Bound of the series, included if the step lands on itinteger | numeric | timestamp
- step
- Step of the series, 1 by default; required for dates and given as an intervalinteger | numeric | intervalOptional
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.9
SELECT GENERATE_SERIES(1, 10, 3);Dates day by day
PostgreSQL 17.9
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.9
SELECT n,
n * n AS square
FROM GENERATE_SERIES(1, 5) AS n;Payments by month, including empty ones
PostgreSQL 17.9
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.9
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.