PostgreSQL generate_series() Function

The PostgreSQL generate_series() function returns a series-based set based on the specified start value, end value, and step size.

For example, you can use the generate_series() function to generate a set containing integers from 1 to 5.

generate_series() Syntax

Here is the syntax of the PostgreSQL generate_series() function:

generate_series(start, stop[, step]) -> setof

Parameters

start

Required. The starting value. It can be a value of type integer, bigint, numeric, timestamp, timestamp with time zone

stop

Required. The end value. It can be a value of type integer, bigint, numeric, timestamp, timestamp with time zone. Its data type should be the same as the start parameter.

step

Optional. The step size. If start and stop are numeric types, it should be the same data type as theirs, and is optional, its default value is 1. If start and stop are of type timestamp, it should be of interval type and is required.

Return value

The PostgreSQL generate_series() function returns a set containing multiple rows.

generate_series() Examples

There are some examples of the generate_series() function here.

To generate a set containing all integers from 1 to 5, use the following statement:

SELECT generate_series(1, 5);

 generate_series
-----------------
               1
               2
               3
               4
               5

You can specify an alias for generate_series, as follows:

SELECT generate_series(1, 5) AS i;

 i
---
 1
 2
 3
 4
 5

To generate a set containing all integers from 5 to 1, use the following statement:

SELECT generate_series(5, 1, -1);

 generate_series
-----------------
               5
               4
               3
               2
               1

To generate a set containing all numbers from 1 to 5 with an step of 1.1, use the following statement:

SELECT generate_series(1, 5, 1.1);

 generate_series
-----------------
               1
             2.1
             3.2
             4.3

To generate a set containing all times between 2022-06-06 and 2022-06-10 with an interval 12 hours, use the following statement:

SELECT generate_series(
    '2022-06-06'::timestamp,
    '2022-06-10',
    '12 hours');

   generate_series
---------------------
 2022-06-06 00:00:00
 2022-06-06 12:00:00
 2022-06-07 00:00:00
 2022-06-07 12:00:00
 2022-06-08 00:00:00
 2022-06-08 12:00:00
 2022-06-09 00:00:00
 2022-06-09 12:00:00
 2022-06-10 00:00:00