How to Use generate_series in PostgreSQL: Syntax and Examples

In PostgreSQL, generate_series is a set-returning function that produces a series of values, such as numbers or timestamps, between a start and stop value with a specified step. You use it in a SELECT statement to generate rows dynamically without needing a table.

Syntax

The generate_series function has two main forms: one for numbers and one for timestamps.

• Numeric series: generate_series(start, stop, step) generates numbers from start to stop incremented by step.
• Timestamp series: generate_series(start_timestamp, stop_timestamp, interval) generates timestamps from start_timestamp to stop_timestamp incremented by the interval.

The step or interval is optional and defaults to 1 for numbers.

sql

SELECT generate_series(start, stop, step);

-- Examples:
SELECT generate_series(1, 5, 1);
SELECT generate_series('2024-01-01'::timestamp, '2024-01-03'::timestamp, '1 day'::interval);

Example

This example shows how to generate a series of numbers from 1 to 5 and a series of dates from January 1 to January 3, 2024.

sql

SELECT generate_series(1, 5, 1) AS number;

SELECT generate_series('2024-01-01'::timestamp, '2024-01-03'::timestamp, '1 day'::interval) AS day;

Output

 number 
--------
      1
      2
      3
      4
      5
(5 rows)

        day         
---------------------
 2024-01-01 00:00:00
 2024-01-02 00:00:00
 2024-01-03 00:00:00
(3 rows)

Common Pitfalls

Common mistakes when using generate_series include:

• Omitting the step argument in numeric series when you want a step other than 1.
• Using incompatible data types, like mixing dates and numbers.
• Forgetting to cast string literals to timestamp or date when generating time series.
• Expecting generate_series to return a single value instead of a set of rows.

sql

/* Wrong: Missing step for numeric series with step 2 */
SELECT generate_series(1, 10, 2);

/* Correct: Include step explicitly */
SELECT generate_series(1, 10, 2);

/* Wrong: Using string without cast for timestamp series */
SELECT generate_series('2024-01-01', '2024-01-03', '1 day');

/* Correct: Cast strings to timestamp and interval */
SELECT generate_series('2024-01-01'::timestamp, '2024-01-03'::timestamp, '1 day'::interval);

Quick Reference

Usage	Description	Example
Numeric series	Generates numbers from start to stop with step	generate_series(1, 5, 1)
Timestamp series	Generates timestamps from start to stop with interval	generate_series('2024-01-01'::timestamp, '2024-01-03'::timestamp, '1 day'::interval)
Default step	Step defaults to 1 for numeric series	generate_series(1, 5)
Returns	Set of rows, one per generated value	SELECT generate_series(1,3)

Key Takeaways

Use generate_series(start, stop, step) to create sequences of numbers or timestamps in PostgreSQL.

Always cast string literals to timestamp or date when generating time series.

The function returns multiple rows, one for each value in the series.

The step or interval argument controls the increment and defaults to 1 for numbers.

Common errors include missing casts and incorrect step values.