In PostgreSQL, we can use the generate_series() function to return a series of values between a given start and stop point. This can be a series of numbers or a series of timestamps.

The function returns a set containing the series.

Syntax

When using numbers, we can use any of the following:

generate_series ( start integer, stop integer [, step integer ] )
generate_series ( start bigint, stop bigint [, step bigint ] )
generate_series ( start numeric, stop numeric [, step numeric ] )

When using timestamps, we can use the following:

generate_series ( start timestamp, stop timestamp, step interval )
generate_series ( start timestamp with time zone, stop timestamp with time zone, step interval ) 

From PostgreSQL 16 and later, we can use the following:

generate_series ( start timestamp with time zone, stop timestamp with time zone, step interval [, timezone text ] )

This variant computes times of day and daylight-savings for the timestamp series in the specified time zone.

Example

Here’s an example of using generate_series() to create a series of numbers:

SELECT * FROM generate_series( 1, 5 );

Result:

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

We can see that the series of numbers started at the first argument and ended at the second argument.

Specify a Step

We can use a third argument to specify the step between each value in the series:

SELECT * FROM generate_series( 1, 5, 2 );

Result:

 generate_series 
-----------------
               1
               3
               5
(3 rows)

This time we only get three values. This is because we specified a step of 2.

Create a Decrementing Series

We can use a negative step to create a series that decrements:

SELECT * FROM generate_series( 5, 1, -1 );

Result:

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

Note that this requires that the first argument is greater than the second. Otherwise nothing would be returned.

The inverse applies to incrementing series: the first argument must be lower than the second argument, otherwise nothing is returned.

Fractions

The generate_series() function accepts the numeric type, so we can use fractions:

SELECT * FROM generate_series( 1.5, 7.5, 1.5 );

Result:

 generate_series 
-----------------
             1.5
             3.0
             4.5
             6.0
             7.5
(5 rows)

Date & Time Values

As mentioned, we can create a series of date/time values:

SELECT * FROM generate_series(
    '2035-01-01 00:00'::timestamp,
    '2035-01-03 12:00', '8 hours'
    );

Result:

   generate_series   
---------------------
 2035-01-01 00:00:00
 2035-01-01 08:00:00
 2035-01-01 16:00:00
 2035-01-02 00:00:00
 2035-01-02 08:00:00
 2035-01-02 16:00:00
 2035-01-03 00:00:00
 2035-01-03 08:00:00
(8 rows)

Zero Step

Passing zero for the step (third argument) results in an error:

SELECT * FROM generate_series( 1, 5, 0 );

Result:

ERROR:  step size cannot equal zero

NULL Arguments

No rows are returned if any of the arguments are NULL:

SELECT * FROM generate_series( 1, NULL );

Result:

 generate_series 
-----------------
(0 rows)